From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Reece Hart <reece@in-machina.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: query optimization differs between view and explicit
Date: Fri, 30 Jan 2004 00:26:47 -0500
Message-ID: <4862.1075440407@sss.pgh.pa.us> (raw)
In-Reply-To: <20040129171201.I35951@megazone.bigpanda.com>
References: <1075422699.4062.164.camel@tallac>
<20040129171201.I35951@megazone.bigpanda.com>
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Thu, 29 Jan 2004, Reece Hart wrote:
>> I have a large query which I would like to place in a view. The explicit
>> query is sufficiently fast, but the same query as a view is much slower
>> and uses a different plan. I would appreciate an explanation of why this
>> is, and, more importantly whether/how I might coax the view to use a
>> different plan.
> Well, in general [ they're not the same query ]
Right. The reason the performance is so much worse is that the
restriction pseq_id=76 cannot be "pushed down" into the view subquery;
we have to form the entire logical output of the view and then filter
on pseq_id=76. In your inline query you have done the pushing down
anyway and so the restriction is applied much lower in the plan,
resulting in lots less work. But the results might be different.
The point that Stephan makes is explicitly understood by the planner as
of PG 7.4:
* 3. If the subquery uses DISTINCT ON, we must not push down any quals that
* refer to non-DISTINCT output columns, because that could change the set
* of rows returned.
It's hard to give any advice on how to make a faster view without more
context. What's the actual intention in all this? What's the semantics
of pseq_id --- is it unique? It might be you could fix the problem by
adding pseq_id to the DISTINCT ON list, but we don't have enough info
to understand whether that would break the desired behavior.
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, sszabo@megazone.bigpanda.com, reece@in-machina.com
Subject: Re: query optimization differs between view and explicit
In-Reply-To: <4862.1075440407@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