Re: optimization with limit and order by in a view

Thank you. I already removed the order by in the view
definition which was put in against my recommendation...

But it is an interesting case.

If I understand you, a subquery with an order by cannot
be flattened.

cheers,

elein

On Tue, Jul 13, 2004 at 05:55:50PM -0400, Tom Lane wrote:
> elein <elein(at)varlena(dot)com> writes:
> > Brain dead java beans want order by clauses in views
> > that they use.
>
> That's *quite* brain dead, considering that standard SQL doesn't allow
> ORDER BY in view definitions at all. Sure you can't fix it on the
> client side?
>
> > What I found was that if I moved the order by outside
> > of the view definition, the query went from 5000-7000ms
> > down to 70-1.5ms.
>
> Yeah. The planner can't flatten a subquery that contains ORDER BY into
> the parent query, because there'd be no place to put the ORDER BY. So
> when you write it that way, the subquery is planned independently and
> it doesn't realize that it should use a fast-start plan instead of a
> minimum-total-time plan.
>
> I can think of various possible kluges to get around this in simple
> cases, but nothing I like much...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html