Skip site navigation (1) Skip section navigation (2)

Re: query optimization differs between view and explicit

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Reece Hart <reece(at)in-machina(dot)com>
Cc: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: query optimization differs between view and explicit
Date: 2004-01-30 01:20:57
Message-ID: 20040129171201.I35951@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2004-01-30 05:26:47
Subject: Re: query optimization differs between view and explicit
Previous:From: Bill MoranDate: 2004-01-30 01:07:40
Subject: Re: [PERFORM] Set-Returning Functions WAS: On the performance of

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group