Re: Postgres not using correct indices for views.

[ re-adding list ]

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