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 ON posts (column ASC): ASC can be omitted, then default data in the index is also sorted ascending
CREATE INDEX idx_column_desc ON posts (column DESC): sorted descending
In addition, you can use NULLS FIRST or NULLS LAST to create more variations to sort NULL values to suit your needs
- CREATE INDEX idx_column_desc ON posts (column ASC NULLS FIRST): sorted ascending and NULL is at the end
- CREATE INDEX idx_column_desc ON posts (column DESC NULLS LAST): sorted descending and NULL is at the top

Notes

If you only create an Index on 1 column, whether you create an ASC or DESC Index, you can query in the reverse direction and the Index still works
If creating a Composite Index, it is still based on that principle but you must pay attention to the usage order of columns, you can see specifics in this article
As for the NULL value, the Index only works if querying in the exact order as when created, otherwise Postgres must use a less efficient solution, Seq Scan, and resort the data

Detail

First, create a table as follows

CREATE TABLE users (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone_number VARCHAR(20) NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Then you can optionally select Index types like this

-- Query 1
CREATE INDEX idx_users_phone_number ON users(phone_number);
CREATE INDEX idx_users_phone_number ON users(phone_number ASC NULLS LAST);

-- Query 2
CREATE INDEX idx_users_phone_number ON users(phone_number DESC);
CREATE INDEX idx_users_phone_number ON users(phone_number DESC NULLS FIRST);

-- Query 3
CREATE INDEX idx_users_phone_number_asc_nulls_first ON users(phone_number ASC NULLS FIRST);
CREATE INDEX idx_users_phone_number_desc_nulls_last ON users(phone_number DESC NULLS LAST);

Query 1: When using users(phone_number), the default is ascending sort and NULL values are placed last, so its full query will be users(phone_number ASC NULLS LAST)
Query 2: When using users(phone_number DESC), the default is descending sort and NULL values are placed first, so its full query will be users(phone_number DESC NULLS FIRST)
Query 3: This is how to create Indexes in the reverse direction including ASC NULLS FIRST and DESC NULLS LAST

Next is checking Index operation

-- Query 1
SELECT * FROM users ORDER BY phone_number LIMIT 10
SELECT * FROM users ORDER BY phone_number ASC NULLS LAST LIMIT 10

-- Query 2
SELECT * FROM users ORDER BY phone_number DESC LIMIT 10
SELECT * FROM users ORDER BY phone_number DESC NULLS FIRST LIMIT 10

-- Query 3
SELECT * FROM users ORDER BY phone_number ASC NULLS FIRST LIMIT 10
SELECT * FROM users ORDER BY phone_number DESC NULLS LAST LIMIT 10

Query 1: Both of these queries are ASC NULLS LAST, you need to add LIMIT to trigger the Index, because if retrieving all table data, Postgres will choose to use Seq Scan alt text

Query 2: Both of these queries are DESC NULLS FIRST, even if you do not create an Index for DESC, Postgres will automatically know how to fetch data backwards from ASC alt text

Query 3: These are 2 queries that will sort in the opposite direction, ASC NULLS FIRST and DESC NULLS LAST, which default does not support

ASC NULLS FIRST: Default only has ASC NULLS LAST, if there is no Index, it must use Seq Scan to sort ascending and then put NULL values at the top
DESC NULLS LAST: Default only has DESC NULLS FIRST, if there is no Index, it must use Seq Scan to sort descending and then put NULL values at the bottom
Even if you do not create an Index for DESC NULLS LAST, Postgres knows how to retrieve data backwards from the ASC NULLS FIRST Index
You can check before and after adding the Index to see the difference