Re: Cross Join Problem

"Gauri Kanekar" <meetgaurikanekar(at)gmail(dot)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