Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> 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 [ they're not the same query ]
Right. The reason the performance is so much worse is that the
restriction pseq_id=76 cannot be "pushed down" into the view subquery;
we have to form the entire logical output of the view and then filter
on pseq_id=76. In your inline query you have done the pushing down
anyway and so the restriction is applied much lower in the plan,
resulting in lots less work. But the results might be different.
The point that Stephan makes is explicitly understood by the planner as
of PG 7.4:
* 3. If the subquery uses DISTINCT ON, we must not push down any quals that
* refer to non-DISTINCT output columns, because that could change the set
* of rows returned.
It's hard to give any advice on how to make a faster view without more
context. What's the actual intention in all this? What's the semantics
of pseq_id --- is it unique? It might be you could fix the problem by
adding pseq_id to the DISTINCT ON list, but we don't have enough info
to understand whether that would break the desired behavior.
regards, tom lane
