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

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

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:

Create a sample index without any ORDER BY:

Check the execution plan:
Index only scan is working…

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.

Now, drop the old index:

Create a new index with b DESC:

Check the below execution plan:
Index scan is working…

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

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.