Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 7633E64FC71 for ; Wed, 31 Dec 2008 00:23:50 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 66741-08 for ; Wed, 31 Dec 2008 00:23:47 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from yw-out-2324.google.com (yw-out-2324.google.com [74.125.46.29]) by mail.postgresql.org (Postfix) with ESMTP id 37A5864FC32 for ; Wed, 31 Dec 2008 00:23:47 -0400 (AST) Received: by yw-out-2324.google.com with SMTP id 3so1771265ywj.73 for ; Tue, 30 Dec 2008 20:23:46 -0800 (PST) Received: by 10.64.10.2 with SMTP id 2mr11921952qbj.76.1230697426013; Tue, 30 Dec 2008 20:23:46 -0800 (PST) Received: from oxford.xeocode.com.enterprisedb.com (69-196-131-89.dsl.teksavvy.com [69.196.131.89]) by mx.google.com with ESMTPS id p27sm32641182qbp.12.2008.12.30.20.23.43 (version=TLSv1/SSLv3 cipher=RC4-MD5); Tue, 30 Dec 2008 20:23:44 -0800 (PST) To: david@lang.hm Cc: Tom Lane , Scott Carey , bricklen , Scott Marlowe , Merlin Moncure , "pgsql-performance\@postgresql.org" Subject: Re: Poor plan choice in prepared statement In-Reply-To: (david@lang.hm's message of "Tue\, 30 Dec 2008 16\:19\:01 -0800 \(PST\)") User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) X-Draft-From: ("nnimap+imap.gmail.com:PG" 102504) References: <33b743250812301059u31ce82faw3d72a38280b37402@mail.gmail.com> <33b743250812301214k7a0ec4fby614d693ec7915d3@mail.gmail.com> <2796.1230678149@sss.pgh.pa.us> From: Gregory Stark Organization: EnterpriseDB Date: Tue, 30 Dec 2008 23:23:41 -0500 Message-ID: <877i5hotv6.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200812/240 X-Sequence-Number: 32276 david@lang.hm writes: > since there is not a pre-parsed interface for queries, it may make sense to > setup a way to have the query pre-parsed, but not pre-planned for cases like > this. What would be more interesting would be to have plans that take into account the outlier values and have alternative plans for those values. One aspect that hasn't been discussed here is whether it's only certain outlier arguments that cause Postgres to choose the poor plan for you or whether it always chooses it for all the sets of arguments you actually use. If it's the former then it's possible you're only being bitten if the first time you prepare the query happens to have one of these outlier set of parameters. I forget what version this went in but I think it was before 8.2 that Postgres started using the first set of arguments seen to plan the query. This is usually an improvement over just guessing but if that first set is unusual it could lead to strange results. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning