Re: Postgres not using correct indices for views.

What a nice catch!

Sent from my iPad

On Aug 10, 2019, at 6:05 AM, Thomas Rosenstein <thomas(dot)rosenstein(at)creamfinance(dot)com> wrote:

>> [ re-adding list ]
>>
>> "Thomas Rosenstein" <thomas(dot)rosenstein(at)creamfinance(dot)com> 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!
>
>