On 6/20/2012 1:01 AM, Eyal Wilde wrote:
Hi, all. this is an obligation from the past: http://archives.postgresql.org/pgsql-performance/2012-05/msg00017.php the same test, that did ~230 results, is now doing ~700 results. that is, BTW even better than mssql. the ultimate solution for that problem was to NOT to do "ON COMMIT DELETE ROWS" for the temporary tables. instead, we just do "DELETE FROM temp_table1". doing "TRUNCATE temp_table1" is defiantly the worst case (~100 results in the same test). this is something we knew for a long time, which is why we did "ON COMMIT DELETE ROWS", but eventually it turned out as far from being the best. another minor issue is that when configuring temp_tablespace='other_tablespace', the sequences of the temporary tables remain on the 'main_tablespace'. i hope that will help making postgres even better :)
Did you ever try re-writing some of the temp table usage to use subselect's/views/cte/etc?
-Andy -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
