Skip site navigation (1) Skip section navigation (2)

Re: Avoiding bad prepared-statement plans.

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Mark Mielke <mark(at)mark(dot)mielke(dot)cc>
Cc: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Jeroen Vermeulen <jtv(at)xs4all(dot)nl>, Greg Stark <gsstark(at)mit(dot)edu>, Bart Samwel <bart(at)samwel(dot)tk>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Avoiding bad prepared-statement plans.
Date: 2010-03-01 02:51:14
Message-ID: 603c8f071002281851u222a66e1pbcc6903a101c39d@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Sun, Feb 28, 2010 at 2:52 AM, Mark Mielke <mark(at)mark(dot)mielke(dot)cc> wrote:
> On 02/27/2010 11:20 PM, Craig Ringer wrote:
>>
>> Essentially, you have:
>>
>> 1) People preparing statements to save on parse+plan time; and
>> 2) People preparing statements to get convenenient param placement.
>>
>> I suspect that most of (1) also want (2), but many of (2) don't care much
>> about (1) and are just preparing statements for sql-injection safety (param
>> placement), because they've been told to by someone, because their library
>> does it for them, etc.
>>
>> So: Would it be easier to handle control of replan vs no-replan at PREPARE
>> time? Or would that have very much the same protocol/pl change issues?
>
> I think if SQL hints were sufficient, that clients would only need to remove
> the prepared statement and re-create it whenever required.
>
> It should do the right thing automatically.
>
> I'm convinced that means generic plans are always wrong, and that some
> combination of performing fixed operations in PREPARE and variable
> operations in EXECUTE, combined with a plan caching against the prepared
> statement with criteria to determine whether or not the parameters match the
> assumptions made when creating one of the cached plans. Tom says extracting
> the fixed part of the planning out to PREPARE would be difficult or less
> valuable than I think. And the multi-plan caching with criteria seems to
> have been brought up and not commented on much by several people. So, it
> doesn't look like I will get this unless I learn how to implement it myself
> - which is probably not feasible at this time. :-)
>
> Not getting this, I think I'd be happy if PREPARE/EXECUTE can *easily*
> detect the worst cases (i.e. not slower in the general case), and generic
> plan plus custom plan plus custom execution is still significantly faster
> than generic plan plus generic execution.
>
> Adding SQL to indicate whether it should be re-planned or not is completely
> unappealing. If I could change the code, today, I'd just turn off or choose
> not to use PREPARE/EXECUTE. Today, PREPARE/EXECUTE seems like it should
> always be considered slower unless one can prove it is actually faster in a
> specific case, which is the exact opposite of what people expect.

I don't really understand most of what you're saying here, but there's
definitely some truth to your last sentence.  This has easily got to
be one of the top ten questions on -performance.

...Robert

In response to

Responses

pgsql-hackers by date

Next:From: Greg SmithDate: 2010-03-01 03:00:09
Subject: Re: Re: Hot Standby query cancellation and Streaming Replication integration
Previous:From: Robert HaasDate: 2010-03-01 02:47:38
Subject: Re: Avoiding bad prepared-statement plans.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group