Problem with subquery containg GROUP BY

From: "Ishay Pomerantz" <ishay(at)ovdimnet(dot)co(dot)il>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Problem with subquery containg GROUP BY
Date: 2004-12-28 17:31:15
Message-ID: 000401c4ed03$08a23730$92fadb3e@mazcal
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi,
I'm using postgres 7.4.

I have two queries:
(1)
SELECT a,b
FROM table1
WHERE a=99

(2)
SELECT a,b,sum (o) as sum_o
FROM table2
GROUP BY a,b

Both Runs very fast.

But when I try to make (2) as a subquery of (1):

SELECT a,b,sum_o
FROM table1
LEFT JOIN (
SELECT a,b,sum (o) as sum_o
FROM table2
GROUP BY a,b
) sub_query
USING (a,b)
WHERE a=99


It runs 100 times slower.
I guess it has to do with the postgres unable to pass the a=99 inside
the subquery

Any ideas ?

ishay

Browse pgsql-sql by date

  From Date Subject
Next Message Robert Treat 2004-12-29 03:35:06 Re: diff databases
Previous Message Nathan Pickett 2004-12-28 17:12:03 Making a prepared statement in a stored procedure