Thread: AW: 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?

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

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