PostgreSQL: Create Index using ORDER BY (ASC/DESC)

 Recently, I received a message like “ORDER BY clause is not working in PostgreSQL Index”.

One of the users created an index with ORDER BY DESC and whenever he was selecting data at that time Query optimizer was not using INDEX SCAN ONLY.

So here, I demonstrate and validate this:

If we know in which order we are going to SELECT data, we should apply for proper ORDER BY ASC/DESC in the index.

Please check the below full demonstration, and then validate your self:

Create a table with sample records:

1 2 3 4

CREATE TABLE tbl_testIndex(a INTEGER, b INTEGER);   INSERT INTO tbl_testIndex SELECT x, x FROM generate_series(1, 5000) AS f(x);

Create a sample index without any ORDER BY:

1	CREATE INDEX tbl_testIndex_idx ON tbl_testIndex (a, b);

Check the execution plan:
Index only scan is working…

1 2 3 4 5 6 7

EXPLAIN SELECT *FROM tbl_testIndex ORDER BY a,b;   'Index Only Scan using tbl_testindex_idx on tbl_testindex (cost=0.28..194.16 rows=5000 width=8)'

Check the below execution plan:
Sequential Scan of the index which is not good.
Because the index is not available for ORDER BY b DESC.

1 2 3 4 5 6 7 8

EXPLAIN SELECT *FROM tbl_testIndex ORDER BY a,b DESC;   'Sort (cost=380.19..392.69 rows=5000 width=8)' ' Sort Key: a, b DESC' ' -> Seq Scan on tbl_testindex (cost=0.00..73.00 rows=5000 width=8)'

Now, drop the old index:

1	DROP INDEX tbl_testIndex_idx;

Create a new index with b DESC:

1	CREATE INDEX tbl_testIndex_idx ON tbl_testIndex (a, b DESC);

Check the below execution plan:
Index scan is working…

1 2 3 4 5 6 7

EXPLAIN SELECT *FROM tbl_testIndex ORDER BY a,b DESC;   'Index Only Scan using tbl_testindex_idx on tbl_testindex (cost=0.28..194.16 rows=5000 width=8)'

Check the below execution plans:
You can find sequential scan of index because we are not using only ORDER BY b DESC

1 2 3 4 5 6 7 8 9 10 11 12

EXPLAIN SELECT *FROM tbl_testIndex ORDER BY a,b;   EXPLAIN SELECT *FROM tbl_testIndex ORDER BY a DESC,b DESC;   'Sort (cost=380.19..392.69 rows=5000 width=8)' ' Sort Key: a DESC, b DESC' ' -> Seq Scan on tbl_testindex (cost=0.00..73.00 rows=5000 width=8)'

Now, check the below execution plan:
You can find like Index scan backward, where we gave ORDER By a DESC and internally, it is using backward index scan.

1 2 3 4 5 6 7

EXPLAIN SELECT *FROM tbl_testIndex ORDER BY a DESC,b;   'Index Only Scan Backward using tbl_testindex_idx on tbl_testindex (cost=0.28..194.16 rows=5000 width=8)'