Tom Lane wrote: > Ron Mayer <rm_pg@xxxxxxxxxxxxxxxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
