Implicit order-by in Postgresql?
Frequently one wants a data set returned in the same order as the
index used in the query. Informix (at least) has implicit order-by,
which means that the data will be returned in collating order if the
query forces use of the appropriate index.
Does Postgresql do this?
If not, does an Order-by force a sort even if an index has the correct
order to satisfy the order-by?
Thanks
John Moore
newsNOSPAM@tinyvital.com
Frequently one wants a data set returned in the same order as the
index used in the query. Informix (at least) has implicit order-by,
which means that the data will be returned in collating order if the
query forces use of the appropriate index.Does Postgresql do this?
Yes, but same as in Informix you can not rely on the optimizer to choose
that plan, if he thinks another access is cheaper. (In Informix you can
force a certain index with an optimizer directive which is not available
in PostgreSQL, but for this particular case simply use an order by in both
databases)
If not, does an Order-by force a sort even if an index has the correct
order to satisfy the order-by?
If a btree index is chosen that satisfies the order by, the sort is
avoided.
Andreas
Import Notes
Resolved by subject fallback
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:
If not, does an Order-by force a sort even if an index has the correct
order to satisfy the order-by?
If a btree index is chosen that satisfies the order by, the sort is
avoided.
And, of course, selection of that index is encouraged, because the
optimizer will realize that any other plan will require an explicit
sort step with extra costs.
Bottom line: say what you mean, don't try to outsmart the system.
If you want your results delivered in order, say ORDER BY.
regards, tom lane
earthlink wrote:
Frequently one wants a data set returned in the same order as the
index used in the query. Informix (at least) has implicit order-by,
which means that the data will be returned in collating order if the
query forces use of the appropriate index.Does Postgresql do this?
If data is fetched by walking an index, then , yes it produces an
implicit order. Just don't count on it to stay that way - the use of a
particular index depends on too many things.
If not, does an Order-by force a sort even if an index has the correct
order to satisfy the order-by?
Dropping unneeded sort node from a query tree was one of the earliest
optimisations in postgresql.
---------------
Hannu