Is an index scan always faster than a sequential scan?
Understand the concept
An index scan follows an index to locate matching rows, while a sequential scan reads the table directly. If a query needs much of a table, following many scattered row locations can cost more than scanning it. PostgreSQL's planner weighs expected row count, I/O and other costs rather than always preferring indexes.
Important ideas
-
No. Reading much of a table may be faster with a sequential scan.
-
Compare estimated rows, actual rows, filters and I/O.
-
An index-only scan also depends on PostgreSQL visibility information, not just covering columns.
-
An index-only scan can still visit the table to check visibility when visibility-map information is insufficient.
Can you explain it without the notes?
Is an index scan always faster than a sequential scan?
Use your own words. Explain an example and one limitation before checking the explanation.
Check your explanation
An index scan follows an index to locate matching rows, while a sequential scan reads the table directly. If a query needs much of a table, following many scattered row locations can cost more than scanning it. PostgreSQL's planner weighs expected row count, I/O and other costs rather than always preferring indexes.
Turn this concept into a clear answer.
- A sequential scan is evidence to understand, not automatically a performance bug.
Adapted from 05_SQL_POSTGRESQL.md · “Is an index scan always faster than a sequential scan?”. Examples are study fragments; framework behavior can depend on your version.