Re: plans for PostgreSQL 12

2018-06-04 21:53 GMT+02:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > 2018-06-04 9:59 GMT+02:00 Vik Fearing <vik(dot)fearing(at)2ndquadrant(dot)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).

Regards

Pavel

>
> regards, tom lane
>