Re: query optimization differs between view and explicit

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.