What a nice catch!
Sent from my iPad
On Aug 10, 2019, at 6:05 AM, Thomas Rosenstein
wrote:
>> [ re-adding list ]
>>
>> "Thomas Rosenstein" writes:
On 9 Aug 2019, at 0:45, Tom Lane wrote:
However ... it sort of looks like the planner didn't even consider
the second plan shape in the "wrong" case. If it had, then even
if it costed it 3X more than it did in the "right" case, the second
plan would still have won out by orders of magnitude. So there's
something else going on.
Can you show the actual query and table and view definitions?
>>
>>> View definition:
>>> SELECT l.id,
>>> l.created_at,
>>> ...
>>> togdpr(l.comment) AS comment,
>>> ...
>>>FROM loans l;
>>
>> Ah-hah. I'd been thinking about permissions on the table and
>> view, but here's the other moving part: functions in the view.
>> I bet you were incautious about making this function definition
>> and allowed togdpr() to be marked volatile --- which it will
>> be by default. That inhibits a lot of optimizations.
>>
>> 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.
>>
>>regards, tom lane
>
> 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?
>
> Thanks again!
>
>
