Re: Transient plans versus the SPI API

On Wed, Aug 3, 2011 at 8:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> I think its possible to tell automatically whether we need to replan
>> always or not based upon the path we take through selectivity
>> functions.
>
> I don't really believe that, or at least I think it would only detect a
> few cases.  Examples of parameter-value-sensitive decisions that are
> made nowhere near the selectivity functions are constraint exclusion and
> LIKE pattern to index-qual conversion.  And in none of these cases do we
> really know at the bottom level whether a different parameter value will
> lead to a significant change in the finished plan.  For instance, if
> there's no index for column foo, it is a waste of time to force
> replanning just because we have varying selectivity estimates for
> "WHERE foo > $1".
>
> I think we'll be a lot better off with the framework discussed last
> year: build a generic plan, as well as custom plans for the first few
> sets of parameter values, and then observe whether there's a significant
> reduction in estimated costs for the custom plans.

The problem there is which executions we build custom plans for. That
turns the problem into a sampling issue and you'll only fix the
problems that occur with a frequency to match your sampling pattern
and rate. Examples of situations where it won't help.

* plans that vary by table size will be about the same in the first 5
executions. After large number of executions, things go bad.

* text search using parameter is provided by user input - sensible
requests have low selectivities; some users put in <space> or "e" and
then we try to retrieve whole table by index scan. Almost impossible
to prevent all potentially high selectivity inputs from user. We could
add LIMIT but frequently ORM generated queries do not do that.

This isn't my-way-or-your-way - I think we need to look at some form
of "safety barriers" so we generate a plan but also know when the plan
has outlived its usefulness and force a re-plan.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services