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.
