From: Scott Marlowe <scott.marlowe@gmail.com>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: bricklen <bricklen@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Poor plan choice in prepared statement
Date: Tue, 30 Dec 2008 13:09:55 -0700
Message-ID: <dcc563d10812301209i59bf612eidc42fa408d4441ab@mail.gmail.com> (raw)
In-Reply-To: <b42b73150812301142w4483978ahfb819b0313150dbb@mail.gmail.com>
References: <33b743250812301059u31ce82faw3d72a38280b37402@mail.gmail.com>
<b42b73150812301142w4483978ahfb819b0313150dbb@mail.gmail.com>
On Tue, Dec 30, 2008 at 12:42 PM, 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.
One of the things you can try here is to build your query then execute
it so it has to be planned each time.
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scott.marlowe@gmail.com, mmoncure@gmail.com, bricklen@gmail.com
Subject: Re: Poor plan choice in prepared statement
In-Reply-To: <dcc563d10812301209i59bf612eidc42fa408d4441ab@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