Re: Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

2016-01-13 11:44 GMT+01:00 Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com>:

> >the implementation is simply - but it hard to design some really general
> - it is task for UI
>
> Can you please rephrase?
>

Sorry - It is task for artifical inteligency

>
> Current design is "if the cost of a generic plan is less than the one
> of a custom plan+replan, prefer generic".
> I think that is wrong.
>
> "Generic plan" misunderestimates a cost in a sense that it assumes
> some pre-defined selectivities.
>

Generic plan in Postgres is optimized for most common values - so in
avarage it should be optimal. But the reality is different - the wrong
estimation can be everywhere and the estimation can be lower or upper than
reality.

> In other words, if "skewed" values are used, "custom plan" would
> likely to have *worse cost* than the one of a generic plan, yet custom
> plan is much more suitable for a particular parameter set.
> As backend refers to boundParams, it does see that particular
> condition is tough, while generic estimator just the cost.
>

And there is a second issue - you have not a idea, what parameter vector
will follow. You cannot to check and optimize plans every where, because a
planning can be expensive, and you should to reuse plan more times. What
was true, for first iterations, then it should not be true in following
iterations.

I like a strategy based on risks. Probably there are situation, when the
generic plan is great every time - INSERTs, UPDATEs via PK, simple SELECTs
via PK. generic plan can be well if almost all data has similar
probability. Elsewhere on bigger data, the probability of pretty slow plan
is higher, and then we should to prefer custom plan.

so the strategy - if cost of generic plan is less than some MAGIC CONSTANT
(can be specified by GUC), then use generic plan. Elsewhere use a custom
plan everytime.

It allow to controll the plan reusing. When MAGIC CONSTANT = 0 .. use
custom plan everytime, When MAGIC CONSTANT = M, then use generic plan
always.

Regards

Pavel

> Looking into plancache.c comments I see 3 possible plans:
> 1) custom plan with PARAM_FLAG_CONST=1. It should probably
> constant-fold based on input parameters.
>
> 2) custom plan with PARAM_FLAG_CONST=0. I think it should just use
> given parameters for selectivity estimations. The generated plan
> should still be valid for use with other input values.
> 3) generic plan. The plan with all variables. <-- here's current behavior
>
> 1 has a replan cost.
>
> 2&3 can be cached and reused.
>
> Is that correct?
> I think #2 is better option than #3 since it gives better plan
> stability, thus it is much easier to test and reason about.
>
> This all boils down to adjustment in a single line:
>
> https://github.com/postgres/postgres/blob/ee943004466418595363d567f18c053bae407792/src/backend/utils/cache/plancache.c#L1151-L1152
>
> Does that make sense?
>
> Vladimir
>