From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Thomas Rosenstein" <thomas.rosenstein@creamfinance.com>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: Postgres not using correct indices for views.
In-reply-to: <D292DF5F-D573-4B3B-894C-A8F204E9783A@creamfinance.com>
References: <EC37F8FB-D294-409A-97CC-72C7A5A6CE42@creamfinance.com>
 <31061.1565280312@sss.pgh.pa.us>
 <F99F7963-C6CF-4E70-9D1E-FD1E601340B4@creamfinance.com>
 <27100.1565304307@sss.pgh.pa.us>
 <D292DF5F-D573-4B3B-894C-A8F204E9783A@creamfinance.com>
Comments: In-reply-to "Thomas Rosenstein" <thomas.rosenstein@creamfinance.com>
	message dated "Fri, 09 Aug 2019 09:32:06 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <8768.1565363769.1@sss.pgh.pa.us>
Date: Fri, 09 Aug 2019 11:16:09 -0400
Message-ID: <8769.1565363769@sss.pgh.pa.us>
Precedence: bulk

[ 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