X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BE58B44F2D5 for ; Tue, 28 Dec 2004 07:47:47 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 88631-06 for ; Tue, 28 Dec 2004 07:47:34 +0000 (GMT) Received: from sa8.bezeqint.net (sa8.bezeqint.net [192.115.104.22]) by svr1.postgresql.org (Postfix) with ESMTP id 6AC7244EEE8 for ; Tue, 28 Dec 2004 07:31:05 +0000 (GMT) Received: from localhost (unknown [127.0.0.1]) by sa8.bezeqint.net (Bezeq International SMTP out Mail Server) with ESMTP id 7FF2833C10 for ; Tue, 28 Dec 2004 09:28:44 +0200 (IST) Received: from sa8.bezeqint.net ([127.0.0.1]) by localhost (sa8 [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 03504-06 for ; Tue, 28 Dec 2004 09:28:43 +0200 (IST) Received: from mazcal (unknown [62.219.250.146]) by sa8.bezeqint.net (Bezeq International SMTP out Mail Server) with ESMTP for ; Tue, 28 Dec 2004 09:28:42 +0200 (IST) From: "Ishay Pomerantz" To: Subject: Problem with subquery containg GROUP BY Date: Tue, 28 Dec 2004 09:31:15 -0800 Message-ID: <000401c4ed03$08a23730$92fadb3e@mazcal> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0005_01C4ECBF.FA7EF730" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2616 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: amavisd-new at bezeqint.net X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.599 tagged_above=0 required=5 tests=DATE_IN_FUTURE_06_12, HTML_90_100, HTML_MESSAGE, UPPERCASE_25_50 X-Spam-Level: * X-Archive-Number: 200412/163 X-Sequence-Number: 19783 This is a multi-part message in MIME format. ------=_NextPart_000_0005_01C4ECBF.FA7EF730 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_0005_01C4ECBF.FA7EF730 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi,

I’m using postgres = 7.4.

 

I have two queries:

(1)

SELECT  a,b

FROM table1

WHERE a=3D99

 

(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=3D99

 

 

It runs 100 times slower.

I guess it has to do with the postgres unable to pass the a=3D99 inside the subquery

 

Any ideas = ?

 

ishay

------=_NextPart_000_0005_01C4ECBF.FA7EF730--