Thread: Using subquery or creating temp table
Test table: CREATE TABLE t1 ( col1 int, col2 int, ... ); Subquery SELECT * FROM t1 WHERE col1=2 Is it OK to use this subquery two times in same statement or should temp table created to prevent subquery executing twice? Which is better SELECT * ( SELECT * FROM (SELECT * FROM t1 WHERE col1=2) p1 WHERE col2=3 UNION ALL SELECT * FROM (SELECT * FROM t1 WHERE col1=2) p2 WHERE col2=4 ) p3 GROUP BY 1; or CREATE TEMP TABLE temp ON COMMIT DROP AS SELECT * FROM t1 WHERE col1=2; SELECT * ( SELECT * FROM temp p1 WHERE col2=3 UNION ALL SELECT * FROM temp p2 WHERE col2=4 ) p3 GROUP BY 1 ? In real query select statements above contain several tables and have more sophisticated where clauses. Using PostgreSQL 8.0+ Andrus.
temporary tables make sens, if you want to operate on multiple queries in the same connection.
Also, temporary tables are visible only to the connection, if multiple connections will create temp table by the same name - they all will see their own content, ie - it is not shared between connections.
What you probably need, is either wait for 8.4, and use WITH() , or use subquery as 'temporary' data.
or fire two queries, one creating temporary table, second one using it.
Try all, and see which one will apply best to your problem, and which one will be fastest.
I am using temporary tables, to keep large amounts of data, that's used for transactions within connection. That saves me bandwith, all in all - time, and makes life much easier.