Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-sql by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group