Using subquery or creating temp table

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: pgsql-general(at)postgresql(dot)org
Subject: Using subquery or creating temp table
Date: 2008-10-05 19:32:14
Message-ID: gcb4r5$6us$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kieran Seal 2008-10-05 22:32:36 IP4r on windows
Previous Message Alvaro Herrera 2008-10-05 17:37:50 Re: db_user_namespace, md5 and changing passwords