2016-01-13 11:44 GMT+01:00 Vladimir Sitnikov <sitnikov.vladi...@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 >
