> Chris Browne wrote:
>> Lukas Smith <mls@pooteeweet.org> writes:
>> > Bruce Momjian wrote:
>> >
>> >> * Flush cached query plans when the dependent objects change,
>> >> when the cardinality of parameters changes dramatically, or
>> >> when new ANALYZE statistics are available
>> >
>> > Wouldn't it also make sense to flush a cached query plan when after
>> > execution it is determined that one or more assumptions that the
>> > cached query plan was based on was found to be off? Like the query
>> > plan was based on the assumption that a particular table would only
>> > return a hand full of rows, but in reality it returned a few
>> > thousand.
>>
>> There is some merit to that.
>>
>> I could also see it being sensible to flush a cached plan any time the
>> query took more than some [arbitrary/GUC-chosen] interval.
>>
>> Supposing it took 20s to execute the query, it would surely seem
>> surprising for re-evaluating the plan to be expected to make up a
>> material proportion of the cost of the *next* invocation.
>>
>> If we flush every plan that took >10s to evaluate, that offers the
>> possibility for it to be done better next time...
>
> Ah, but how do you pass that information back to the optimizer so you
> don't end up getting the same plan again?
We can't, in any direct fashion, of course.
Even if flushing the plan doesn't lead to a better one, now, it
still leaves you ready for when an ANALYZE will come along and change
the stats and possibly improve things.
One possibility to do something indirect would be for this "plan
invalidation" to also feed some stats to pg_autovacuum, such that
every "bad query plan" (as evaluated by the notion that the actual
number of tuples for a given table wildly varied from expectations)
would bump up the stats for the offending table.
That would give some feedback to encourage pg_autovacuum to analyze
trouble tables again sooner.
--
(reverse (concatenate 'string "moc.liamg" "@" "enworbbc"))
http://cbbrowne.com/info/slony.html
``Lisp has jokingly been called "the most intelligent way to misuse a
computer". I think that description is a great compliment because it
transmits the full flavor of liberation: it has assisted a number of our
most gifted fellow humans in thinking previously impossible thoughts.''
-- "The Humble Programmer", E. Dijkstra, CACM, vol. 15, n. 10, 1972