Re: Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102 - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102 |
| Date | |
| Msg-id | CAFj8pRDb9noda3rhcZ_sniH2g=_qRminN6pt=NHDBUmDY6m4OQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102 (Vladimir Sitnikov <sitnikov.vladimir@gmail.com>) |
| Responses |
Re: Fwd: [JDBC] Re: 9.4-1207 behaves differently with
server side prepared statements compared to 9.2-1102
(Vladimir Sitnikov <sitnikov.vladimir@gmail.com>)
Re: Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102 (Marko Tiikkaja <marko@joh.to>) Re: Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102 (Albe Laurenz <laurenz.albe@wien.gv.at>) |
| List | pgsql-hackers |
2016-01-13 11:44 GMT+01:00 Vladimir Sitnikov <sitnikov.vladimir@gmail.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
pgsql-hackers by date: