Thread: Bypassing useless ORDER BY in a VIEW

Hi,

While designing a complex database structure I wanted to ask a basic question about views.

If I give an ORDER BY clause in a VIEW and then use it in another query where the VIEW's ORDER BY is immaterial, would the planner be able to discard this ORDER BY clause ?

Any pointers / feedbacks would be really helpful.

Regards,
Robins Tharakan

"Robins Tharakan" <tharakan@gmail.com> writes:
> If I give an ORDER BY clause in a VIEW and then use it in another query
> where the VIEW's ORDER BY is immaterial, would the planner be able to
> discard this ORDER BY clause ?

No.  That's a feature not a bug; the sorts of cases where you want an
ORDER BY in a subquery, it's because you really want those rows computed
in that order (eg you've got side-effect-causing functions reading the
results).  Postgres will never discard an ORDER BY as "immaterial".

A rule of thumb is that ORDER BY in a view is bad design, IMHO.

            regards, tom lane

On 2008-02-28 09:13, Tom Lane wrote:
> A rule of thumb is that ORDER BY in a view is bad design, IMHO.
>
>             regards, tom lane
>

I was surprised to find out that apparently it's also a PostgreSQL
extension;  standard SQL apparently disallows ORDER BY in VIEWs:

http://en.wikipedia.org/wiki/Order_by_(SQL)

When I found this out, I removed all the ORDER BYs from my VIEWs (which
had been there for the convenience of subsequent SELECTs).

Of course, where ORDER BY in a VIEW is really helpful, is with OFFSET
and/or LIMIT clauses (which are also PostgreSQL extensions), which is
equivalent to what you point out.

--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.

"Dean Gibson (DB Administrator)" <postgresql@ultimeth.com> writes:
> Of course, where ORDER BY in a VIEW is really helpful, is with OFFSET
> and/or LIMIT clauses (which are also PostgreSQL extensions), which is
> equivalent to what you point out.

Right, which is the main reason why we allow it.  I think that these
are sort of poor man's cases of things that SQL2003 covers with
"windowing functions".

The SQL spec treats ORDER BY as a cosmetic thing that you can slap onto
the final output of a SELECT.  They don't consider it useful in
subqueries (including views) because row ordering is never supposed to
be a semantically significant aspect of a set of rows.

            regards, tom lane

Frankly put, i didn't know that this perspective exists and then thanks for putting it that way then !!

Guess I should take a relook at how I plan to use those VIEWS.

Thanks
Robins