From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Reece Hart <reece@in-machina.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: query optimization differs between view and explicit
Date: Thu, 29 Jan 2004 17:20:57 -0800 (PST)
Message-ID: <20040129171201.I35951@megazone.bigpanda.com> (raw)
In-Reply-To: <1075422699.4062.164.camel@tallac>
References: <1075422699.4062.164.camel@tallac>
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
select distinct on (A) A, B
from table
where B=10
order by A,B;
is not always the same as
select * from
(select distinct on (A) A, B
from table order by A,B) foo
where B=10;
If A is not unique, then given two rows of the
same A value one with B=10 and one with another B
value less than 10, the former is guaranteed to give
you an A,10 row, the latter will give no such row AFAICS.
If A is unique then the two queries are equivalent,
but then distinct on (A) isn't terribly meaningful.
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: sszabo@megazone.bigpanda.com, reece@in-machina.com
Subject: Re: query optimization differs between view and explicit
In-Reply-To: <20040129171201.I35951@megazone.bigpanda.com>
* 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