Pavel Stehule <pavel.stehule@gmail.com> writes: > 2018-06-04 9:59 GMT+02:00 Vik Fearing <vik.fearing@2ndquadrant.com>: >> On 04/06/18 09:37, Pavel Stehule wrote: >>> Yes, it is incorrect mark. Unfortunately - this is often workaround for >>> wrong estimations - so I afraid, in this case, your proposed fix breaks >>> lot of applications.
>> I would say such applications are already broken.
> I cannot to agree, not in this moment: > 1. there is not any workaround, how to force subselect evaluation in > planning time - what can be correct for once only evaluated queries.
There's the sadly-underdocumented trick of writing the expression inside a sub-select so that it becomes an InitPlan. We could doubtless improve our support for that --- for instance, teach estimate_expression_value() how to get an estimated value when the expression is stable --- but I'm unconvinced that we need new infrastructure for this. Inventing a new function volatility class would be an enormous mess from users' standpoint, especially if the reason was only to distinguish cheating uses from non-cheating uses of the existing class. I am not inclined to promise that we'll never break cheating uses.
In this moment it is only one possible solution. The situation will be different, when there will be cleaner, better substitution. Isn't necessary to talk about it more now - or it is theme for other thread. When I worked for GoodData we should to use this technique for optimization of queries for snow flake schema. Native optimization of star schema or snow flake schema queries can be the best of.
For my test, and maybe for first releases the good optimization of faster expression evaluation can be based on immutable functions from pg_catalog schema. It is not final design, and I don't try to speak about some timing. I understand well, so working with just this subset of functions can be frustrating for users, but it can be good for start - the problem with not too strong immutable functions can be long (there can be some relation to driver level, some similar optimization cannot be principally possible when execution plan can be reused. But some drivers use prepared statements implicitly for only one execution due protection against SQL injection).