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

Re: Avoiding bad prepared-statement plans.

From: Jeroen Vermeulen <jtv(at)xs4all(dot)nl>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Avoiding bad prepared-statement plans.
Date: 2010-02-09 14:25:19
Message-ID: 4B71704F.5070708@xs4all.nl (view raw or flat)
Thread:
Lists: pgsql-hackers
Richard Huxton wrote:

>> = Actual-cost threshold =
>>
>> Also stop using the generic plan if the statement takes a long time to
>> run in practice.
> 
> Do you mean:
> 1. Rollback the current query and start again
> 2. Mark the plan as a bad one and plan again next execute
> 
> If you can figure out how to do #1 then you could probably do it for all 
> queries, but I'm guessing it's far from simple to implement.

I'm talking about #2.  As a matter of fact #1 did come up in one of 
those discussions, but how do you know you're not killing the query 
juuust before it'd done, and then maybe executing a different plan 
that's no better?


>> = Plan refresh =
>>
>> Periodically re-plan prepared statements on EXECUTE. This is also a
>> chance for queries that were being re-planned every time to go back to a
>> generic plan.
> 
> Presumably some score based on update stats and vacuum activity etc.

I was thinking of something very simple: re-do whatever we'd do if the 
statement were only being prepared at that point.


> The good side of all these ideas is good indeed. The bad side is plan 
> instability. Someone somewhere will have a generic plan that turns out 
> better than the specific plan (due to bad stats or config settings or 
> just planner limitations). The question is (I guess): How many more 
> winners will there be than losers?

That's a good and surprising point, and therefore I'd like to draw 
attention away to a different point.  :-)

Yes, there will be losers in the sense that people may have optimized 
their use of prepared statements to whatever the current planner does. 
Maybe somebody out there even deliberately uses them to trick the 
planner into a different plan.  But that is always going to happen; 
we're aiming for better plans, not for giving more detailed control over 
them.  If you really can't take a change, don't upgrade.

The competing point is: people out there may currently be forgoing 
prepared statements entirely because of erratic performance.  To those 
people, if we can help them, it's like having a new feature.


Jeroen

In response to

Responses

pgsql-hackers by date

Next:From: Jeroen VermeulenDate: 2010-02-09 14:28:01
Subject: Re: Avoiding bad prepared-statement plans.
Previous:From: Dave PageDate: 2010-02-09 14:22:55
Subject: Re: About psycopg2 (by its author)

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