From: Ron Mayer <rm_pg@cheapcomplexdevices.com>
Subject: Re: TB-sized databases
Date: Thu, 06 Dec 2007 21:11:40 -0800
Organization: Hub.Org Networking Services
Lines: 31
Message-ID: <4758D60C.50703@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>
	<10359.1197000850@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
To: Tom Lane <tgl@sss.pgh.pa.us>, Ron Mayer <rm_pg@cheapcomplexdevices.com>
User-Agent: Icedove 1.5.0.14pre (X11/20071020)
In-Reply-To: <10359.1197000850@sss.pgh.pa.us>
To: pgsql-performance@postgresql.org

Tom Lane wrote:
> Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
>> Tom Lane wrote:
>>> ...given that that plan has a lower cost estimate, it 
>>> should've picked it without any artificialconstraints.
> 
>>I think the reason it's not picking it was discussed back...
>> http://archives.postgresql.org/pgsql-performance/2005-03/msg00675.php
> ...
> 
> 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...
> 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....

Interesting....  I think Simon mentioned last time that this type of
query is quite common for standard star schema data warehouses.
And it seem to me the Cartesian join on the dimension tables is
often pretty harmless since each dimension table would often return
just 1 row; and the size of the fact table is such that it's useful
to touch it as little as possible.

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

No problem.. we've been working around it since that last
thread in early '05 with early 8.0, IIRC.  :-)

Thanks to the excellent postgres hints system ("offset 0" and
"set join_collapse_limit=1") we can get the plans we want
pretty easily. :-)