"Thomas Rosenstein" <thomas.rosenstein@xxxxxxxxxxxxxxxx> 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
