iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
A single-column ordered index can help a database return rows for ORDER BY without a separate sort. In PostgreSQL, a B-tree index on that column can normally be scanned forward or backward, so a second index with the opposite direction is usually unnecessary. The optimizer chooses whether to use the index; it may still scan the table and sort instead.
What an index does for ORDER BY
ORDER BY tells the database which order the result must have. Without it, row order is unspecified; an index that happens to return rows in a particular order is not a substitute for the clause. PostgreSQL describes B-tree as the index type that can provide sorted output. When a suitable index supplies the requested order, the database may avoid an explicit sort. PostgreSQL 16: Indexes and ORDER BY PostgreSQL 16: SELECT
This can be especially useful with LIMIT: an ordered index may let the database find the first requested rows without reading and sorting every matching row. Whether that is cheaper depends on the query and data. If the query needs a large share of the table, a table scan followed by a sort may be the planner’s preferred approach.
Do you need to specify ASC or DESC for one indexed column?
For a single-column PostgreSQL B-tree index, usually not. A normal ascending index can be scanned backward to serve a descending order, so creating another index solely to reverse that one key is generally redundant. PostgreSQL’s CREATE INDEX documentation explicitly notes this for single-column indexes. PostgreSQL 17: Indexes and ORDER BY
#1 Best Overall
| Query ordering | Typical PostgreSQL B-tree scan |
|---|---|
ORDER BY created_at ASC |
Forward scan of an index on created_at |
ORDER BY created_at DESC |
Backward scan of the same index |
This describes what the index can support, not a guarantee that the planner will use it. Confirm the plan for the actual query before adding a direction-specific index.
When index direction matters
Direction becomes more important for a multicolumn index when the query asks for mixed ordering. A PostgreSQL index on (x, y) can provide ORDER BY x ASC, y ASC in a forward scan, or ORDER BY x DESC, y DESC in a backward scan. Reversing the scan reverses both keys, not just one. A query such as ORDER BY category ASC, created_at DESC may therefore benefit from an index whose key directions match that mixed order. PostgreSQL 17: Indexes and ORDER BY
Rank #2
MySQL 8.4 also documents descending indexes as useful for multicolumn queries with mixed sort directions. Do not assume PostgreSQL’s behavior or null-ordering details apply identically to every engine and index type; check the target database’s documentation and execution plan. MySQL 8.4: Descending Indexes
Recommended Free Tools
Null placement and database differences
In PostgreSQL, a default B-tree index stores ascending keys with nulls last. A forward scan supports the default ascending order, while a backward scan produces descending order with the corresponding default null placement. If the query specifies a particular NULLS FIRST or NULLS LAST requirement, compare that requirement with the index ordering rather than assuming direction alone settles it. PostgreSQL 17: Indexes and ORDER BY
Microsoft’s EF Core index guidance likewise describes an index as generally usable in either direction for a single sort key, while noting that composite sort direction can affect usefulness. This is general ORM guidance, not a substitute for checking the database provider and its plan. Microsoft EF Core: Indexes
Quick Recap
How to decide whether to add an index
- Keep the query’s
ORDER BY; it is what guarantees the requested result order. - Check whether an index already begins with the ordered column, or with the appropriate leading keys for a multicolumn order.
- Inspect the database’s execution plan to see whether it uses an index scan or performs an explicit sort.
- Consider the query’s filters, requested row count, null ordering, database engine, and index type before changing the index.
- Add or change index key directions when the actual workload and plan justify it, particularly for mixed-direction multicolumn ordering—not merely to duplicate a single key as ASC and DESC.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
