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.
Date: Fri, 09 Aug 2019 11:16:09 -0400
Message-ID: <8769.1565363769@sss.pgh.pa.us> (raw)
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>
[ 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, thomas.rosenstein@creamfinance.com, pgsql-performance@lists.postgresql.org
Subject: Re: Postgres not using correct indices for views.
In-Reply-To: <8769.1565363769@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox