pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.



view thread (5+ messages)  latest in thread

Message-ID: <20040129171201.I35951@megazone.bigpanda.com>
Permalink:  ../20040129171201.I35951@megazone.bigpanda.com/
Also on:    postgresql.org/message-id/20040129171201.I35951@megazone.bigpanda.com

 · 

reply

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