Thread: Do Layered Views/Relations Preserve Sort Order ?
Hi All, When there are several views defined on top of each other, are SELECTs on views that do not specify a SORT order guaranteed to preserve the cumulative sort order of the lower-level views ? Is the answer true for any arbitrarily large set of layered views? Is the answer the same if the layers of relations are a mix of views and tables ? Best, Charles
Hi All,
When there are several views defined on top of each other, are SELECTs on views that do not specify a SORT order guaranteed to preserve the cumulative sort order of the lower-level views ?
Is the answer true for any arbitrarily large set of layered views?
Is the answer the same if the layers of relations are a mix of views and tables ?
The answer to any question as broad and non-specific as yours is likely to be answered with a no.
The better question is how expensive is it to sort already sorted data. If its cheap, and it likely is, then placing explicit sorting where you care is the best solution regardless of your level of confidence that lower level sorting is being maintained.
Since tables are never sorted I don't get why you think they enter into the equation.
If ones operates under the guideline that only top-layer queries should contain ORDER BY then your whole structure is unsound. Presumably those queries you rely upon are or were themselves considered top-level queries at one point and now you are adding a dependent to them that they likely were never intended to consider. Simplification queries should not use ORDER BY unless it is necessary to implement their logic. A query whose logic depends on order really should declare that fact.
David J.
On 9/9/15 7:44 PM, David G. Johnston wrote:
David, yes, I agree that sorting at the end is the highest-confidence approach. I don't (yet) have a large stack of views with an assumption of a guaranteed underlying sort order, I'm just trying to get a better sense of what Postgres behavior I can reasonably expect here.Hi All,
When there are several views defined on top of each other, are SELECTs on views that do not specify a SORT order guaranteed to preserve the cumulative sort order of the lower-level views ?
Is the answer true for any arbitrarily large set of layered views?
Is the answer the same if the layers of relations are a mix of views and tables ?The answer to any question as broad and non-specific as yours is likely to be answered with a no.The better question is how expensive is it to sort already sorted data. If its cheap, and it likely is, then placing explicit sorting where you care is the best solution regardless of your level of confidence that lower level sorting is being maintained.Since tables are never sorted I don't get why you think they enter into the equation.If ones operates under the guideline that only top-layer queries should contain ORDER BY then your whole structure is unsound. Presumably those queries you rely upon are or were themselves considered top-level queries at one point and now you are adding a dependent to them that they likely were never intended to consider. Simplification queries should not use ORDER BY unless it is necessary to implement their logic. A query whose logic depends on order really should declare that fact.David J.
Thanks, Charles
On Wed, Sep 9, 2015 at 7:53 PM, Charles Sheridan <cesheri@swbell.net> wrote: > When there are several views defined on top of each other, are SELECTs on > views that do not specify a SORT order guaranteed to preserve the cumulative > sort order of the lower-level views ? > > Is the answer true for any arbitrarily large set of layered views? > > Is the answer the same if the layers of relations are a mix of views and > tables ? If a view definition includes an ORDER BY clause, the output of that view will be sorted accordingly. But if you do something with that output, like join it to another table, then the join might disturb the sort order. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 9/9/15 7:55 PM, Charles Sheridan wrote: >> The better question is how expensive is it to sort already sorted >> data. If its cheap, and it likely is, then placing explicit sorting >> where you care is the best solution regardless of your level of >> confidence that lower level sorting is being maintained. ... > David, yes, I agree that sorting at the end is the highest-confidence > approach. I don't (yet) have a large stack of views with an assumption > of a guaranteed underlying sort order, I'm just trying to get a better > sense of what Postgres behavior I can reasonably expect here. BTW, I believe there is some code in the planner to remove useless ORDER-BYs. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com