Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id E288D64FD33
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 18 Aug 2008 11:02:40 -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 66507-01 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 18 Aug 2008 11:02:26 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id 84E6264FD1C
	for <pgsql-performance@postgresql.org>;
	Mon, 18 Aug 2008 11:02:32 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m7IE2T53018371;
	Mon, 18 Aug 2008 10:02:29 -0400 (EDT)
To: "Gauri Kanekar" <meetgaurikanekar@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Cross Join Problem 
In-reply-to: <7e4ba9550808180637v382ce149sf0577849b03d955@mail.gmail.com> 
References: <7e4ba9550808180637v382ce149sf0577849b03d955@mail.gmail.com>
Comments: In-reply-to "Gauri Kanekar" <meetgaurikanekar@gmail.com>
	message dated "Mon, 18 Aug 2008 19:07:33 +0530"
Date: Mon, 18 Aug 2008 10:02:29 -0400
Message-ID: <18370.1219068149@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200808/190
X-Sequence-Number: 31071

"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