Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id D872464FD33 for ; Tue, 30 Dec 2008 16:09:59 -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 79439-01 for ; Tue, 30 Dec 2008 16:09:57 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.180]) by mail.postgresql.org (Postfix) with ESMTP id D892D64FCE2 for ; Tue, 30 Dec 2008 16:09:56 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id j4so2999350wah.19 for ; Tue, 30 Dec 2008 12:09:55 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=TF78QQ3lDq5+GB+CMpJVdIKdFiygjuSMe9xFcTid7+s=; b=wPQdQYhmeDsG8jZrhJZPJLiiUbSmQJ5sCLMZZAIUUPMaAmZHbIhRUeAEel9rAODahm 4e57gIwAEzeT88r7IxADT3/T0YddYNzqUpCzKOQ4xUuv9H1K3jgxPFAMMm63XVyH27Nw A3GjqoiRO6xk41q/9hnmszNg0CZ1EXYYIuo0g= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=rfrbiHc8fI3J5B7a88hkUMYLR4XuLfaaw97vCBHnyytjiULktzR3SmNNclmt6B7zDX NHIyK5G3/Yyf0IRYlRuE5aRxUgU5CRswJG3TqAmwH7ecY8WSg5GK1VKGeDXBLEiM35wn UDqc09vzFUZnS+nWGaXIs5xvvNNw6hLwspoUo= Received: by 10.114.196.13 with SMTP id t13mr9983406waf.82.1230667795805; Tue, 30 Dec 2008 12:09:55 -0800 (PST) Received: by 10.114.132.17 with HTTP; Tue, 30 Dec 2008 12:09:55 -0800 (PST) Message-ID: Date: Tue, 30 Dec 2008 13:09:55 -0700 From: "Scott Marlowe" To: "Merlin Moncure" Subject: Re: Poor plan choice in prepared statement Cc: bricklen , pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <33b743250812301059u31ce82faw3d72a38280b37402@mail.gmail.com> 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/233 X-Sequence-Number: 32269 On Tue, Dec 30, 2008 at 12:42 PM, Merlin Moncure wrote: > On Tue, Dec 30, 2008 at 1:59 PM, bricklen wrote: >> Hi, I am re-posting my question here after trying to find a solution >> in the PHP pgsql list with no luck. >> >> I am experiencing some performance issues that I think are stemming >> from prepared statements. I have a pretty simple query: >> -- bad plan, from prepared statement >> -- >> dev=# prepare fooplan (date,date,int,int) as >> dev-# SELECT cl.idOffer AS campaign, cl.idAffiliate AS affiliate, >> cl.idCreative AS creative, cl.subid, cl.datetime >> dev-# FROM click AS cl LEFT JOIN conversion AS co ON cl.clickGenerated >> = co.clickGenerated >> dev-# WHERE cl."date" >= $1 >> dev-# AND cl."date" <= $2 >> dev-# AND cl.idAffiliate = $3 >> dev-# LIMIT $4; > > Your problem is that the query as written is hard to plan. The > database has no idea what you pass in, it has to guess. (IMO, It > almost always guesses wrong...I think it should assume 1 row > returned). Also, the db has no idea what you want to pass in at plan > time for date. One of the things you can try here is to build your query then execute it so it has to be planned each time.