Re: Poor plan choice in prepared statement

Hi Merlin,

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