Postgres doesn't use index with "ORDER BY"

Question

• I have a very large table.
• I have an index on columns col1.
• I would like to get the data ordered by col1.
• From the query plan I can say it doesn't use the index.
• When I add "LIMIT", it starts to use the index
• For large value for "LIMIT", it stops using the index.

Any clue?

P.S. I would like to get the data clustered by values of col1 (not necessarily sorted), any suggestions other than "ORDER BY".

THANKS !!

Answer 1

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.

Answer 2

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).