Re: Postgres not using correct indices for views.

> [ 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!