Re: Automatic function replanning

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Neil Conway <neilc(at)samurai(dot)com>
Cc: Joachim Wieland <joe(at)mcknight(dot)de>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Automatic function replanning
Date: 2005-12-15 17:13:16
Message-ID: 20051215171316.GF40699@pervasive.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Tue, Dec 13, 2005 at 04:49:10PM -0500, Neil Conway wrote:
> On Tue, 2005-12-13 at 22:32 +0100, Joachim Wieland wrote:
> > there's a topic that comes up from time to time on the lists, the problem
> > that pgsql functions get planned only once and thereafter the same query
> > plan is used until server shutdown or explicit recreation of the function.
>
> The problem really has nothing to do with functions, per se: whenever a
> plan is created and then stored for future use, the assumptions made by
> that plan may be invalidated by the time the plan is executed. This
> applies to PREPARE, pl/pgsql functions, perhaps the plan caching done by
> the RI triggers, and so forth.
>
> I also think that invalidating cached plans on a periodic basis is the
> wrong approach -- we can use sinval to invalidate plans as soon as a
> dependent database object changes and not before. This thread contains
> some ideas on how to do this:
>
> http://archives.postgresql.org/pgsql-hackers/2005-03/msg00426.php
>
> I got somewhat sidetracked by the complexities of the "central plan
> caching module" that Tom would like to see, but I'm still hoping to take
> a look at this for 8.2.

As for predicate-driven plan changes (ie: query is planned the first
time with a predicate that has high cardinality, but there are also low
cardinality values that will be queried on), it would make more sense to
track the amount of work (probably tuples fetched) normally required to
execute a prepared statement. Any time that prepared statement is
executed with a set of predicates that substantially changes the amount
of work required it should be remembered and considered for re-planning
the next time the query is executed with those predicates.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Qingqing Zhou 2005-12-15 17:36:24 Re: Which qsort is used
Previous Message Jim C. Nasby 2005-12-15 17:06:22 Re: Which qsort is used