pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBypassing useless ORDER BY in a VIEW
5+ messages / 3 participants
[nested] [flat]
* Bypassing useless ORDER BY in a VIEW
@ 2008-02-28 14:31 Robins Tharakan <tharakan@gmail.com>
2008-02-28 17:13 ` Re: Bypassing useless ORDER BY in a VIEW Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Robins Tharakan @ 2008-02-28 14:31 UTC (permalink / raw)
To: pgsql-performance
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*
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Bypassing useless ORDER BY in a VIEW
2008-02-28 14:31 Bypassing useless ORDER BY in a VIEW Robins Tharakan <tharakan@gmail.com>
@ 2008-02-28 17:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-02-28 18:09 ` Re: Bypassing useless ORDER BY in a VIEW Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-29 11:57 ` Re: Bypassing useless ORDER BY in a VIEW Robins Tharakan <tharakan@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Tom Lane @ 2008-02-28 17:13 UTC (permalink / raw)
To: Robins Tharakan <tharakan@gmail.com>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Bypassing useless ORDER BY in a VIEW
2008-02-28 14:31 Bypassing useless ORDER BY in a VIEW Robins Tharakan <tharakan@gmail.com>
2008-02-28 17:13 ` Re: Bypassing useless ORDER BY in a VIEW Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-02-28 18:09 ` Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-28 21:52 ` Re: Bypassing useless ORDER BY in a VIEW Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 5+ messages in thread
From: Dean Gibson (DB Administrator) @ 2008-02-28 18:09 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Bypassing useless ORDER BY in a VIEW
2008-02-28 14:31 Bypassing useless ORDER BY in a VIEW Robins Tharakan <tharakan@gmail.com>
2008-02-28 17:13 ` Re: Bypassing useless ORDER BY in a VIEW Tom Lane <tgl@sss.pgh.pa.us>
2008-02-28 18:09 ` Re: Bypassing useless ORDER BY in a VIEW Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
@ 2008-02-28 21:52 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2008-02-28 21:52 UTC (permalink / raw)
To: pgsql-performance
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Bypassing useless ORDER BY in a VIEW
2008-02-28 14:31 Bypassing useless ORDER BY in a VIEW Robins Tharakan <tharakan@gmail.com>
2008-02-28 17:13 ` Re: Bypassing useless ORDER BY in a VIEW Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-02-29 11:57 ` Robins Tharakan <tharakan@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Robins Tharakan @ 2008-02-29 11:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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*
> A rule of thumb is that ORDER BY in a view is bad design, IMHO.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-02-29 11:57 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-28 14:31 Bypassing useless ORDER BY in a VIEW Robins Tharakan <tharakan@gmail.com>
2008-02-28 17:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-02-28 18:09 ` Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-28 21:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-02-29 11:57 ` Robins Tharakan <tharakan@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox