Skip site navigation (1) Skip section navigation (2)

Re: TB-sized databases

From: Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com>
Subject: Re: TB-sized databases
Date: 2007-12-07 05:11:40
Message-ID: 4758D60C.50703@cheapcomplexdevices.com (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane wrote:
> Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)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. :-)

In response to

pgsql-performance by date

Next:From: Richard HuxtonDate: 2007-12-07 08:12:55
Subject: Re: database tuning
Previous:From: Tom LaneDate: 2007-12-07 04:14:10
Subject: Re: TB-sized databases

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group