"Gauri Kanekar" <meetgaurikanekar@xxxxxxxxx> 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
