pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: bricklen <bricklen@gmail.com>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Poor plan choice in prepared statement
Date: Tue, 30 Dec 2008 11:55:15 -0800
Message-ID: <33b743250812301155q7c5a20b5x9d325ed7121c1f87@mail.gmail.com> (raw)
In-Reply-To: <b42b73150812301142w4483978ahfb819b0313150dbb@mail.gmail.com>
References: <33b743250812301059u31ce82faw3d72a38280b37402@mail.gmail.com>
	<b42b73150812301142w4483978ahfb819b0313150dbb@mail.gmail.com>

Hi Merlin,

On Tue, Dec 30, 2008 at 11:42 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Tue, Dec 30, 2008 at 1:59 PM, bricklen <bricklen@gmail.com> 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)



view thread (20+ messages)  latest in thread

Message-ID: <33b743250812301155q7c5a20b5x9d325ed7121c1f87@mail.gmail.com>
Permalink:  ../33b743250812301155q7c5a20b5x9d325ed7121c1f87@mail.gmail.com/
Also on:    postgresql.org/message-id/33b743250812301155q7c5a20b5x9d325ed7121c1f87@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: bricklen@gmail.com, mmoncure@gmail.com
  Subject: Re: Poor plan choice in prepared statement
  In-Reply-To: <33b743250812301155q7c5a20b5x9d325ed7121c1f87@mail.gmail.com>
  References: <33b743250812301059u31ce82faw3d72a38280b37402@mail.gmail.com> <b42b73150812301142w4483978ahfb819b0313150dbb@mail.gmail.com> <33b743250812301155q7c5a20b5x9d325ed7121c1f87@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox