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

Re: Avoiding bad prepared-statement plans.

From: Mark Mielke <mark(at)mark(dot)mielke(dot)cc>
To: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas <robertmhaas(at)gmail(dot)com>, 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-02-28 07:52:55
Message-ID: 4B8A20D7.1020902@mark.mielke.cc (view raw or flat)
Thread:
Lists: pgsql-hackers
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.

Cheers,
mark


In response to

Responses

pgsql-hackers by date

Next:From: Dimitri FontaineDate: 2010-02-28 08:18:26
Subject: Re: Avoiding bad prepared-statement plans.
Previous:From: Jaime CasanovaDate: 2010-02-28 06:29:30
Subject: Re: Anyone know if Alvaro is OK?

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