Re: Re: [GENERAL] Performance issue with libpq prepared queries on 9.3 and 9.4

On Thu, Nov 13, 2014 at 5:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > Tom Lane-2 wrote
> >> In the meantime, I assume that your real data contains a small
> percentage
> >> of values other than these two? If so, maybe cranking up the statistics
> >> target would help. If the planner knows that there are more than two
> >> values in the column, I think it would be less optimistic about assuming
> >> that the comparison value is one of the big two.
>
> > Is there any value (or can value be added) in creating a partial index of
> > the form:
>
> > archetype IN ('banner','some other rare value')
>
> > such that the planner will see that such a value is possible but
> infrequent
> > and will, in the presence of a plan using a value contained in the
> partial
> > index, refuse to use a generic plan knowing that it will be unable to use
> > the very specific index that the user created?
>
> The existence of such an index wouldn't alter the planner's statistics.
> In theory we could make it do so, but I seriously doubt the cost-benefit
> ratio is attractive, either as to implementation effort or the added
> planning cost.
>
>
> ​
​[adding -general back in...]​

​While "planner hints" comes to mind...on the SQL side can we extend the
"PREPARE" command with two additional keywords?​

​PREPARE
name [ ( data_type [, ...] ) ] [
[NO] GENERIC
​] ​
​AS statement

​I was originally thinking this could attach to EXECUTE and maybe it could
there as well. If EXECUTE is bare whatever the PREPARE used would be in
effect (a bare PREPARE exhibiting the current dynamic behavior). If
EXECUTE and PREPARE disagree execute wins and the current call is
(re-)prepared as requested.

We have introduced intelligence to PREPARE/EXECUTE that is not always
favorable but provide little way to override it if the user has superior
knowledge. The dual role of prepared statements to both prevent
SQL-injection as well as create cache-able generic plans further
complicates things. In effect by supplying NO GENERIC on the PREPARE the
caller is saying they only wish to make use of the SQL-injection aspect of
prepared statements. Adding the EXECUTE piece allows for the same plan to
be used in injection-prevention mode if the caller knows that the
user-supplied value does not play well with the generic plan.

David J.