From: Tom Lane <tgl@sss.pgh.pa.us>
To: Gauri Kanekar <meetgaurikanekar@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Cross Join Problem
Date: Mon, 18 Aug 2008 10:02:29 -0400
Message-ID: <18370.1219068149@sss.pgh.pa.us> (raw)
In-Reply-To: <7e4ba9550808180637v382ce149sf0577849b03d955@mail.gmail.com>
References: <7e4ba9550808180637v382ce149sf0577849b03d955@mail.gmail.com>
"Gauri Kanekar" <meetgaurikanekar@gmail.com> writes:
> 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
No PG release since 7.3 would have voluntarily planned that query that
way. Maybe you were using join_collapse_limit = 1 to force the join
order?
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, meetgaurikanekar@gmail.com
Subject: Re: Cross Join Problem
In-Reply-To: <18370.1219068149@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox