From: Tom Lane <tgl@sss.pgh.pa.us>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Rick Vincent <rvincent@temenos.com>
Cc: pgsql-performance@postgresql.org, Manoj Kumar <manojkumar@temenos.com>
Cc: Herve Aubert <haubert@temenos.com>
Subject: Re: Postgres not using index on views
Date: Tue, 07 Apr 2020 01:09:09 -0400
Message-ID: <26256.1586236149@sss.pgh.pa.us> (raw)
In-Reply-To: <20200407045929.GJ2228@telsasoft.com>
References: <DM6PR04MB492354C0895FB26EDFBE1D82C1C20@DM6PR04MB4923.namprd04.prod.outlook.com>
<20200407045929.GJ2228@telsasoft.com>
Justin Pryzby <pryzby@telsasoft.com> writes:
> On Mon, Apr 06, 2020 at 02:19:59PM +0000, Rick Vincent wrote:
>> The following query takes an extremely long time for only 180 rows, and what this means is that we would have to index anything appearing in the where clause for every table in order to use views because the views seem not to consider the select clause. Why is that and does anyone know a way around this?
> Is there a reason why you don't store the extracted value in its own column ?
The planner seems to be quite well aware that the slower query is going to
be slower, since the estimated costs are much higher. Since it's not
choosing to optimize into a faster form, I wonder whether it's constrained
by semantic requirements. In particular, I'm suspicious that some of
those functions you have in the view are marked "volatile", preventing
them from being optimized away.
Beyond that guess, though, there's really not enough info here to say.
The info we usually ask for to debug slow-query problems is explained
at
https://wiki.postgresql.org/wiki/Slow_Query_Questions
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, pryzby@telsasoft.com, rvincent@temenos.com, manojkumar@temenos.com, haubert@temenos.com
Subject: Re: Postgres not using index on views
In-Reply-To: <26256.1586236149@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