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