Re: Prepared statements and generic plans

On Mon, Jun 13, 2016 at 04:29:26PM -0400, David G. Johnston wrote:
> On Mon, Jun 13, 2016 at 3:40 PM, bruce(at)momjian(dot)us <bruce(at)momjian(dot)us> wrote:
> I am not sure how we can improve things, but I wanted to clarify exactly
> what is happening.
>
>
> ​"""
> Comparisons on non-uniformly-distributed
> columns and specification of non-existent values affects the average
> plan cost, and hence if and when a generic plan is chosen
> ​"""
>
> If we are going to be more precise lets do so here as well.  I have, just
> reading this, no clue whether having non-uniformity and often searching for
> non-existent value will increase or decrease the average plan cost.

Well, we can't be more precise here as the average plan cost could go up
or down, depending on the constants used, becuase the values are not
uniformly-distributed.

> I'm still not certain how this is particularly useful.  If we are willing to
> draw a conclusion here in what circumstances would I, as an end-user, want to
> forgo using a prepared statement and instead dynamically construct an SQL
> statement?  Because at this point while this seems like good detail often times
> my choice of parameters is influenced by what I consider data external to the
> query proper and not any kind of inherent performance aspect.  I'd consider
> this advanced usage which doesn't neatly fit into the SQL Command section of
> the docs.

True, but we have lumped all the "prepared" information into that
section, and I don't see a more logical location for this tidbit of
information.

The big point is that the constants don't affect the generic plan, they
just choose if and when the generic plan is chosen.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +