Postgres doesn't use index with "ORDER BY" Postgres doesn't use index with "ORDER BY" postgresql postgresql

Postgres doesn't use index with "ORDER BY"


If your return all rows from the table an index scan will be slower than a table scan. Why do you think you need the index usage?

You might try to use

set enable_seqscan = false

in order to disable the sequential scan, but I'm sure that will be slower than with the sequential scan.

ORDER BY is the only method to sort your data. Any other sorting you might see is pure coincidence

Edit
To clear things up: I do not recommend to turn seq scan off. I just posted this as a way to show that the seq scan is indeed faster than the index scan. Once turned off the execution plan using the index scan will most probably be slower than the seq scan showing the OP that there is no need for an index scan.


In addition to the answer of a_horse_with_no_name:

Using an index is actually two distinct operations: First the value you desire is looked for in the index. In the index is the address of the complete record which gets then dereferenced. Both operations are very fast for specific queries.

If you intend to use all or most records anyway, the benefit goes away. If you want all records and you go through the index, it takes longer because for every record there are two seeks. It's easier to just run over the whole table without the index as this takes one seek per column (yes, I know, actually it's less than that because whole blocks are read etc... I just want to keep it simple).