From: Tom Lane <tgl@sss.pgh.pa.us>
To: Max Kremer <mkremer@trialfire.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Planner is ignoring index when view is queried but uses index when running underlying view's sql
Date: Tue, 07 Jun 2016 17:40:51 -0400
Message-ID: <14298.1465335651@sss.pgh.pa.us> (raw)
In-Reply-To: <CAEbO6DUxbJVuXQAS8eSZV92StMKijHXWftgACH_qMoZpg1=+Yw@mail.gmail.com>
References: <CAEbO6DUxbJVuXQAS8eSZV92StMKijHXWftgACH_qMoZpg1=+Yw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Max Kremer <mkremer@trialfire.com> writes:
> I've encountered a very strange result when using a view in a query VS the
> underlying sql of the view. When accessing the view I get a different query
> plan than running the sql that makes up the view. Using the view is 10x
> slower and generates a strange query plan that seems to ignore
> the existence of an index.
I believe the problem is that the WITH attached to the view's SELECT
prevents the view sub-select from being flattened into the outer query
(ie, this is another way in which WITH acts as an optimization fence).
So the sub-select is planned without awareness of the ORDER BY/LIMIT
that would encourage picking a suitably ordered join plan.
Try writing the CTE as a plain sub-select, instead.
(AFAIR, this behavior just stems from a lack of round tuits and not
any fundamental difficulty: the WITHs could perfectly well be hoisted
up to the outer query. But don't hold your breath waiting for that
to happen.)
(BTW, what PG version is that?)
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, mkremer@trialfire.com
Subject: Re: Planner is ignoring index when view is queried but uses index when running underlying view's sql
In-Reply-To: <14298.1465335651@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