"Thomas Rosenstein" <thomas.rosenstein@creamfinance.com> writes:
> On 9 Aug 2019, at 0:45, Tom Lane wrote:
>> I'm guessing about what that function does, but if you could
>> safely mark it stable or even immutable, I bet this view would
>> behave better.
> Yep that was IT! Perfect, thank you soo much!
> Why does it inhibit functionalities like using the correct index, if the
> function is only in the select?
> Could that still be improved from pg side?
Possibly, but there's a lot of work between here and there, and it's
limited by how much we want to change the semantics around volatile
functions. The core problem that's breaking this case for you is
that we won't flatten a view (i.e., pull up the sub-SELECT into the
parent query) if its targetlist has volatile functions, for fear
of changing the number of times such functions get invoked.
Now, we're not totally consistent about that anyway --- for example,
the code is willing to push down qual expressions into an un-flattened
sub-SELECT, which could remove rows from the output of the sub-SELECT's
FROM and thereby reduce the number of calls of any volatile functions
in its tlist. (That particular behavior is very ancient, and I wonder
whether we'd reject it if it were proposed today.)
The thing that's missing to make this better is to be willing to
push down join quals not just restriction quals. That would require
being able to make "parameterized paths" for subqueries, which is
something that's on the radar screen but nobody's really worked on it.
There are substantial concerns about whether it'd make subquery planning
noticeably more expensive.
regards, tom lane
