Does index help a sql select sorting performance?

I have a SQL statement like this:

SELECT * FROM "table1" WHERE "id" In('1', '2', '3') ORDER BY "createdAt"

I think the benefit for indexing ‘createdAt’ column is quite minimal, since it does the select first then sort 3 rows. Am I correct? Or it’s better to add indexing?

Answer

There are two possible indexing strategies for the query you show:

1. Index the IN condition:

   CREATE INDEX ON table1 (id);
   

   That is a good idea if the condition is selective, that is, if few table rows match the condition.

2. Index the ORDER BY clause:

   CREATE INDEX ON table1 ("createdAt");
   

   Then the database can scan the index to get the result rows in ORDER BY order without an explicit sort.

   This will only be beneficial if the IN condition is not selective, that is, most table rows meet the condition.

   Still, depending on the row size and other parameters, PostgreSQL may choose to use a sequential scan and an explicit sort unless you limit the number of result rows with a LIMIT clause.

Unfortunately it is not possible to have an index support both the IN condition and the ORDER BY – that would only be possible if the WHERE condition were a plain equality comparison.