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

Re: Avoiding bad prepared-statement plans.

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: 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 04:20:38
Message-ID: 4B89EF16.3080606@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-hackers
On 26/02/2010 11:40 AM, Tom Lane wrote:

> But putting support for a per-query level
> of control into the protocol (and then every client library) as well as
> every PL is going to be painful to implement, and even more painful to
> use.

You mean something like 'EXECUTE REPLAN' and protocol/PL-level equivalents?

That's what people on -GENERAL often seem to need. A way, for a 
particular query, to say "replan this every time, because the stats are 
very signifcant here".

> I still like the idea of automatically replanning with the known
> parameter values, and noting whether the result plan was estimated to be
> noticeably cheaper than the generic plan, and giving up on generating
> custom plans if we didn't observe any such win over N tries.

That risks making prepared statements less efficient when re-used 
between 2 and N times. People would end up asking for a 'no replan' 
knob, which I'm not sure is any improvement over a 'force replan' knob.

OTOH, a GUC to turn that off would be (IMO) fairly harmless - if you 
know you don't have any problematic prepared queries, turn it off to 
save some cycles.



Another thought: I wonder if this should be viewed from one step back. 
Many of these issues come from people who don't actually want prepared 
statements for performance, they're just using them to get convienient 
and secure parameter placement and server-side caching of the query text.

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?

--
Craig Ringer

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2010-02-28 04:22:00
Subject: Re: Avoiding bad prepared-statement plans.
Previous:From: Marc G. FournierDate: 2010-02-28 04:02:32
Subject: Re: Anyone know if Alvaro is OK?

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