Re: optimization with limit and order by in a view - Mailing list pgsql-general

elein <elein@varlena.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