PostgreSQL Sort Index

Official website

In addition to simply finding the rows to is returned by a query, an index may is able to deliver them in a specific sorte D order. This allows a query ' s ORDER by specification to be honored without a separate sorting step. Of the index types currently supported by PostgreSQL, only b-tree can produce sorted output-the other index types re Turn matching rows in an unspecified, implementation-dependent order.

The planner would consider satisfying anORDER bySpecification either by scanning a available index that matches the specification, or by scanning the table in physical O Rder and doing an explicit sort. For a query this requires scanning a large fraction of the table, an explicit sort was likely to being faster than using an in Dex because it requires less disk I/O due to following a sequential access pattern. Indexes is more useful if only a few rows need be fetched. An important special case isORDER byIn combination withLIMITN: An explicit sort would has to process all of the data to identify the firstNRows, but if there are an index matching theORDER by, the firstNRows can is retrieved directly, without scanning the remainder at all.

by default, B-tree indexes store their entries on ascending order with Nulls last. This means, a forward scan of an index in column x produces output satisfying ORDER by x (or more VE rbosely, ORDER by x ASC NULLSlast). The index can also is scanned backward, producing output satisfying order by x DESC (or more verbosely, order by x Desc NULLSFirst, since NULLS first was the default forORDER by DESC).

You can adjust the ordering of a B-tree index by including the options ASC, DESC, NULLSFirst, and/or NULLS last when creating the index; For example:

CREATE INDEX Test2_info_nulls_low on test2 (info nulls first); CREATE INDEX Test3_desc_index on test3 (id desc NULLS last);

An index stored in ascending order with Nulls first can satisfy either order by x ASC nulls First or order by X DESC NULLS last depending on which direction it's scanned in.

You might wonder so bother providing all four options, when both options together with the possibility of backward scan Would cover all the variants of order by . In Single-column indexes the options is indeed redundant, but is multicolumn indexes they can be useful. Consider a two-column index on  (x, y) : This can satisfy order by X, y  if We scan forward, or order by x desc, y DESC  if we scan backward. But it might is, the application frequently needs to USE order by x ASC, y DESC . There is no-to-get-ordering from a-plain index, but it's possible if the index is defined as  (x ASC, y desc)  or  (x DESC, y ASC) .

Obviously, indexes with Non-default sort orderings is a fairly specialized feature, but sometimes they can produce tremen Dous speedups for certain queries. Whether It's worth maintaining such an index depends on how to often you use queries that require a special sort ordering.

The default index is the B-tree index, because the B-tree is the sort tree, so the indexed columns are sorted by default ASC nulls last, and then we set the sort by just setting asc/desc,nulls First/nulls last

PostgreSQL Sort Index