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

Re: Avoiding bad prepared-statement plans.

From: Richard Huxton <dev(at)archonet(dot)com>
To: Jeroen Vermeulen <jtv(at)xs4all(dot)nl>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Avoiding bad prepared-statement plans.
Date: 2010-02-09 13:50:32
Message-ID: 4B716828.2030303@archonet.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On 09/02/10 12:08, Jeroen Vermeulen wrote:
>
> = Projected-cost threshold =

[snip - this is the simple bit. Sounds very sensible. ]

> = 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.

> = 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.


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?

-- 
   Richard Huxton
   Archonet Ltd

In response to

Responses

pgsql-hackers by date

Next:From: Federico Di GregorioDate: 2010-02-09 13:56:19
Subject: About psycopg2 (by its author)
Previous:From: Jeroen VermeulenDate: 2010-02-09 13:46:55
Subject: Re: Avoiding bad prepared-statement plans.

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