Re: TB-sized databases

Tom Lane wrote:
> Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com> writes:
>
>> Also shown below it seems that if I use "OFFSET 0" as a "hint"
>> I can force a much (10x) better plan. I wonder if there's room for
>> a pgfoundry project for a patch set that lets us use more hints
>> than OFFSET 0.
>>
> 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.]

> What PG version are you using?

logs=# select version();

version
----------------------------------------------------------------------------------------------------------------
PostgreSQL 8.2.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
(1 row)

> Do you perhaps have a low setting for join_collapse_limit?

logs=# show join_collapse_limit;
join_collapse_limit
---------------------
8
(1 row)

Actually, IIRC back in that other thread, "set join_collapse_limit =1;"
helped
http://archives.postgresql.org/pgsql-performance/2005-03/msg00663.php