Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 3909B2E026D for ; Fri, 7 Dec 2007 00:20:06 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 67439-05 for ; Fri, 7 Dec 2007 00:20:05 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by postgresql.org (Postfix) with ESMTP id 59F3B2E0054 for ; Fri, 7 Dec 2007 00:20:05 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.1/8.14.1) with ESMTP id lB74Jx1j090246 for ; Fri, 7 Dec 2007 00:19:59 -0400 (AST) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.1/8.14.1/Submit) id lB73tAqU061342 for pgsql-performance@postgresql.org; Thu, 6 Dec 2007 23:55:10 -0400 (AST) (envelope-from news) From: Ron Mayer X-Newsgroups: pgsql.performance Subject: Re: TB-sized databases Date: Thu, 06 Dec 2007 19:55:17 -0800 Organization: Hub.Org Networking Services Lines: 42 Message-ID: <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> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org Cc: Tom Lane To: Ron Mayer User-Agent: Icedove 1.5.0.14pre (X11/20071020) In-Reply-To: <4758A1B8.20501@cheapcomplexdevices.com> To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/92 X-Sequence-Number: 27908 Tom Lane wrote: > Ron Mayer 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