To: Ron Mayer <rm_pg@cheapcomplexdevices.com>
cc: pgsql-performance@postgreSQL.org
Subject: Re: TB-sized databases 
In-reply-to: <4758C425.3080105@cheapcomplexdevices.com> 
References: <4544e0330711260904g45d893a8ub1731e245aa8fc84@mail.gmail.com>
	<13851.1196351131@sss.pgh.pa.us>
	<1196352866.4246.1304.camel@ebony.site>
	<200712051507.22745.xzilla@users.sourceforge.net>
	<1196933896.4255.340.camel@ebony.site>
	<20071206154212.GP5294@mathom.us> <15753.1196957598@sss.pgh.pa.us>
	<4758A1B8.20501@cheapcomplexdevices.com>
	<4758C425.3080105@cheapcomplexdevices.com>
Comments: In-reply-to Ron Mayer <rm_pg@cheapcomplexdevices.com>
	message dated "Thu, 06 Dec 2007 19:55:17 -0800"
Date: Thu, 06 Dec 2007 23:14:10 -0500
Message-ID: <10359.1197000850@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
> Tom Lane wrote:
>> There's something fishy about this --- given that that plan has a lower
>> cost estimate, it should've picked it without any artificial
>> constraints.

> I think the reason it's not picking it was discussed back in this thread
> too.
> http://archives.postgresql.org/pgsql-performance/2005-03/msg00675.php
> http://archives.postgresql.org/pgsql-performance/2005-03/msg00684.php
> My offset 0 is forcing the outer join.
> [Edit: Ugh - meant cartesian join - which helps this kind of query.]

Ah; I missed the fact that the two relations you want to join first
don't have any connecting WHERE clause.

The concern I mentioned in the above thread was basically that I don't
want the planner to go off chasing Cartesian join paths in general ---
they're usually useless and would result in an exponential explosion
in the number of join paths considered in many-table queries.

However, in this case the reason that the Cartesian join might be
relevant is that both of them are needed in order to form an inner
indexscan on the big table.  I wonder if we could drive consideration
of the Cartesian join off of noticing that.  It'd take some rejiggering
around best_inner_indexscan(), or somewhere in that general vicinity.

Way too late for 8.3, but something to think about for next time.

			regards, tom lane