Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 1901C64FD19 for ; Mon, 18 Aug 2008 10:37:43 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 43724-01 for ; Mon, 18 Aug 2008 10:37:30 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.233]) by postgresql.org (Postfix) with ESMTP id C339664FCF3 for ; Mon, 18 Aug 2008 10:37:35 -0300 (ADT) Received: by rv-out-0506.google.com with SMTP id b25so3328685rvf.43 for ; Mon, 18 Aug 2008 06:37:33 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:mime-version:content-type; bh=HUJJ2z6r/XpV1YwTimQ8Rq/U5hLWcHqh5UVkdPtpH50=; b=vOEI1fdxkLd4IWZujuugSTyTltwpNUg/Fd44RIlm4NSUSklK8ORr3IEAjIuRpk3QeI xYgCi09c8H6y4iAnaL7WZ4LXuwyaflKH5Qk2EOIhLGPn3uGe8GLbc3HPoXvoBxJDyLAA R7QuzCujOYg8CikFG6IXFbsYjGkDXvFlfE81s= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=hOo9y1yOqzeGjBUDws4KIlErdlyfa4Ud+GbxD0vcCgbNJomZ35hW4xTGXzfDdLRj3o y8bzJvws5nkLNUikbme4MsQf4mTDOVGzQ1GATCooOK9K0ba7OpqhJfi3S0ycRwW6pcGZ P6vfuSgjgoh1DMBbTYxZq2YIpFGf4L79BuuAs= Received: by 10.141.161.6 with SMTP id n6mr3206017rvo.41.1219066653428; Mon, 18 Aug 2008 06:37:33 -0700 (PDT) Received: by 10.140.127.10 with HTTP; Mon, 18 Aug 2008 06:37:33 -0700 (PDT) Message-ID: <7e4ba9550808180637v382ce149sf0577849b03d955@mail.gmail.com> Date: Mon, 18 Aug 2008 19:07:33 +0530 From: "Gauri Kanekar" To: pgsql-performance@postgresql.org Subject: Cross Join Problem MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_114440_26756011.1219066653406" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200808/189 X-Sequence-Number: 31070 ------=_Part_114440_26756011.1219066653406 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi, Following is the Query : SELECT sum(id), sum(cd), sum(ad) FROM table1 a , table2 b cross join table3 c WHERE a.nkey = b.key AND a.dkey = c.key AND c.date = '2008-02-01' AND b.id = 999 ; We have fired this on our production system which is postgres 8.1.3, and got the following explain analyse of it Aggregate (cost=11045.52..11045.53 rows=1 width=24) (actual time=79.290..79.291 rows=1 loops=1) -> Nested Loop (cost=49.98..11043.42 rows=279 width=24) (actual time=1.729..50.498 rows=10473 loops=1) -> Nested Loop (cost=0.00..6.05 rows=1 width=8) (actual time=0.028..0.043 rows=1 loops=1) -> Index Scan using rnididx on table2 b (cost=0.00..3.02 rows=1 width=4) (actual time=0.011..0.015 rows=1 loops=1) Index Cond: (id = 999) -> Index Scan using rddtidx on table3 c (cost=0.00..3.02 rows=1 width=4) (actual time=0.010..0.016 rows=1 loops=1) Index Cond: (date = '2008-02-01 00:00:00'::timestamp without time zone) -> Bitmap Heap Scan on table1 a (cost=49.98..10954.93 rows=5496 width=32) (actual time=1.694..19.006 rows=10473 loops=1) Recheck Cond: ((a.nkey = "outer"."key") AND (a.dkey = "outer"."key")) -> Bitmap Index Scan on rndateidx (cost=0.00..49.98 rows=5496 width=0) (actual time=1.664..1.664 rows=10473 loops=1) Index Cond: ((a.nkey = "outer"."key") AND (a.dkey = "outer"."key")) Total runtime: 79.397 ms Time: 80.752 ms Same Query when we fire on postgres 8.3.3, following is the explain analyse QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=1171996.35..1171996.36 rows=1 width=24) (actual time=6360.783..6360.785 rows=1 loops=1) -> Nested Loop (cost=0.00..1171994.28 rows=275 width=24) (actual time=3429.309..6330.424 rows=10473 loops=1) Join Filter: (a.nkey = b.key) -> Index Scan using rnididx on table2 b (cost=0.00..4.27 rows=1 width=4) (actual time=0.030..0.033 rows=1 loops=1) Index Cond: (id = 999) -> Nested Loop (cost=0.00..1169411.17 rows=206308 width=28) (actual time=0.098..4818.450 rows=879480 loops=1) -> Index Scan using rddtidx on table1 c (cost=0.00..4.27 rows=1 width=4) (actual time=0.031..0.034 rows=1 loops=1) Index Cond: (date = '2008-02-01 00:00:00'::timestamp without time zone) -> Index Scan using rdnetidx on table1 a (cost=0.00..1156050.51 rows=1068511 width=32) (actual time=0.047..1732.229 rows=879480 loops=1) Index Cond: (a.dkey = c.key) Total runtime: 6360.978 ms The Query on postgres 8.1.3 use to take only 80.752 ms is now taking 6364.950 ms. We have done vacuum analyse on all the tables. Can anybody helpout over here ... was may b wrong... and why the query seems to take time on postgres 8.3.3. Is it 8.3.3 problem or its cross join problem on 8.3.3 Thanx -- Regards Gauri ------=_Part_114440_26756011.1219066653406 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Hi,


Following is the Query :
SELECT sum(id), sum(cd), sum(ad)
       FROM table1 a , table2 b cross join table3 c
       WHERE a.nkey = b.key
             AND a.dkey = c.key
             AND c.date = '2008-02-01'
             AND b.id = 999 ;


We have fired this on our production system which is postgres 8.1.3, and got the following explain analyse of it

 Aggregate  (cost=11045.52..11045.53 rows=1 width=24) (actual time=79.290..79.291 rows=1 loops=1)
   ->  Nested Loop  (cost=49.98..11043.42 rows=279 width=24) (actual time=1.729..50.498 rows=10473 loops=1)
         ->  Nested Loop  (cost=0.00..6.05 rows=1 width=8) (actual time=0.028..0.043 rows=1 loops=1)
               ->  Index Scan using rnididx on table2 b  (cost=0.00..3.02 rows=1 width=4) (actual time=0.011..0.015 rows=1 loops=1)
                     Index Cond: (id = 999)
               ->  Index Scan using rddtidx on table3 c  (cost=0.00..3.02 rows=1 width=4) (actual time=0.010..0.016 rows=1 loops=1)
                     Index Cond: (date = '2008-02-01 00:00:00'::timestamp without time zone)
         ->  Bitmap Heap Scan on table1 a  (cost=49.98..10954.93 rows=5496 width=32) (actual time=1.694..19.006 rows=10473 loops=1)
               Recheck Cond: ((a.nkey = "outer"."key") AND (a.dkey = "outer"."key"))
               ->  Bitmap Index Scan on rndateidx  (cost=0.00..49.98 rows=5496 width=0) (actual time=1.664..1.664 rows=10473 loops=1)
                     Index Cond: ((a.nkey = "outer"."key") AND (a.dkey = "outer"."key"))
 Total runtime: 79.397 ms

Time: 80.752 ms



Same Query when we fire on postgres 8.3.3, following is the explain analyse
                                                                                    QUERY PLAN                              
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1171996.35..1171996.36 rows=1 width=24) (actual time=6360.783..6360.785 rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..1171994.28 rows=275 width=24) (actual time=3429.309..6330.424 rows=10473 loops=1)
         Join Filter: (a.nkey = b.key)
         ->  Index Scan using rnididx on table2 b  (cost=0.00..4.27 rows=1 width=4) (actual time=0.030..0.033 rows=1 loops=1)
               Index Cond: (id = 999)
         ->  Nested Loop  (cost=0.00..1169411.17 rows=206308 width=28) (actual time=0.098..4818.450 rows=879480 loops=1)
               ->  Index Scan using rddtidx on table1 c  (cost=0.00..4.27 rows=1 width=4) (actual time=0.031..0.034 rows=1 loops=1)
                     Index Cond: (date = '2008-02-01 00:00:00'::timestamp without time zone)
               ->  Index Scan using rdnetidx on table1 a  (cost=0.00..1156050.51 rows=1068511 width=32) (actual time=0.047..1732.229 rows=879480 loops=1)
                     Index Cond: (a.dkey = c.key)
 Total runtime: 6360.978 ms


The Query on postgres 8.1.3 use to take only 80.752 ms is now taking 6364.950 ms.

We have done vacuum analyse on all the tables.

Can anybody helpout over here ... was may b wrong... and why the query seems to take time on postgres 8.3.3.

Is it 8.3.3 problem or its cross join problem on 8.3.3

Thanx

--
Regards
Gauri
------=_Part_114440_26756011.1219066653406--