B-Tree Index with order
Introduction When initializing a B-Tree Index , it also allows the option to choose the data sorting direction, including ASC (default) and DESC This feature is uniquely supported for B-Tree Index only, you cannot use it for other Index types ( GIN, GiST, BRIN,... ) The reason is due to the tree structure ordering keys, which can be read forward or backward to retrieve sorted results Other Index types do not support storing sorted data, they are used to serve specific data types and problems, sorting data order ORDER BY is not in their core design. NULL value In Postgres, NULL is understood as an Unknown value and is always considered greater than any normal value. Therefore, by default: When you create an ASC Index (Ascending): NULL values are at the END ( NULLS LAST ). When you create a DESC Index (Descending): Since NULL is the largest, when sorting from high to low, NULL will be at the BEGINNING ( NULLS FIRST ). Use case Includes forms such as CREATE INDEX idx_column_desc...