Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 51F8E64FD33 for ; Tue, 30 Dec 2008 15:55:19 -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 76056-06 for ; Tue, 30 Dec 2008 15:55:16 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wf-out-1314.google.com (wf-out-1314.google.com [209.85.200.170]) by mail.postgresql.org (Postfix) with ESMTP id 522D064FCE2 for ; Tue, 30 Dec 2008 15:55:16 -0400 (AST) Received: by wf-out-1314.google.com with SMTP id 25so5264961wfc.28 for ; Tue, 30 Dec 2008 11:55:15 -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=Ym7ijXdaV3AG0crSg+3kOSJlIGFA35wbvzB4sv3a3ek=; b=e+L57Vu86le/0xxXnhm3oih7duYaSKHOr5eVr0GesMhw5CLpyffqAK7sSlXEu5V6kp TLSpo7a4HyWmC3he9ROG1YEWBwOR8q5lIWp51vAZ+uq+dAIMTOpgW4hdmFXph5goGWSQ JlU6zL9huoqreMT0igZauYBj5Zq53MqWI3n1A= 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=QY+lG+B+DkZXhGdXV8PYgB7yfBxaNv/nOFJp6rSvghQVTqXhCcrjQPrLLvBR+InsWE zNyO7JwbsEKTD5AgvGw0d3KXpjJfcOCsX8/jBJ1OjH4eU1d7qu2M90LIKGPglZvNjcra dhIyzCnCNly6bb9Qte1X1f56JXSlFssXqo7kY= Received: by 10.142.142.16 with SMTP id p16mr6233794wfd.231.1230666915216; Tue, 30 Dec 2008 11:55:15 -0800 (PST) Received: by 10.142.255.15 with HTTP; Tue, 30 Dec 2008 11:55:15 -0800 (PST) Message-ID: <33b743250812301155q7c5a20b5x9d325ed7121c1f87@mail.gmail.com> Date: Tue, 30 Dec 2008 11:55:15 -0800 From: bricklen To: "Merlin Moncure" Subject: Re: Poor plan choice in prepared statement Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 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/232 X-Sequence-Number: 32268 Hi Merlin, On Tue, Dec 30, 2008 at 11:42 AM, 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. > > what indexes do you have on click? > > merlin > "click" is a partitioned table, but the child tables are all the same. Here is the current partition: dev=# \d click_current Column | Type | Modifiers ----------------+-----------------------------+---------------------------------------------------- id | bigint | not null default nextval('click_id_seq'::regclass) idaffiliate | integer | not null idsite | integer | not null idoffer | integer | not null idcreative | integer | not null idoptimizer | integer | clickgenerated | character varying | not null subid | character varying | datetime | timestamp without time zone | not null date | date | ip | inet | not null xip | inet | referrer | text | countrycode | character varying | timestamp | timestamp without time zone | not null Indexes: "click_current_pk" PRIMARY KEY, btree (id) "click_current_clickgenerated_idx" btree (clickgenerated) "click_current_date_idx" btree (date) "click_current_idoffer_idx" btree (idoffer) "click_current_massive_idx" btree (date, idaffiliate, idsite, idoffer, idcreative, idoptimizer, subid) Check constraints: "click_current_date_chk" CHECK (date > '2008-12-29'::date)