Why does PostgreSQL perform sequential scan on indexed column?

by

If the SELECT returns more than approximately 5-10% of all rows in the table, a sequential scan is much faster than an index scan.

This is because an index scan requires several IO operations for each row (look up the row in the index, then retrieve the row from the heap). Whereas a sequential scan only requires a single IO for each row – or even less because a block (page) on the disk contains more than one row, so more than one row can be fetched with a single IO operation.

Btw: this is true for other DBMS as well – some optimizations as “index only scans” taken aside (but for a SELECT * it’s highly unlikely such a DBMS would go for an “index only scan”)

More Related Contents:

  1. PostgreSQL LIKE query performance variations
  2. How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
  3. Do nullable columns occupy additional space in PostgreSQL?
  4. In-order sequence generation
  5. Does a Postgres UNIQUE constraint imply an index?
  6. Difference between GiST and GIN index
  7. Postgres query optimization (forcing an index scan)
  8. How does PostgreSQL perform ORDER BY with a b-tree index on the field?
  9. How to reset sequence in postgres and fill id column with new data?
  10. Optimize Postgres query on timestamp range
  11. Multicolumn index on 3 fields with heterogenous data types
  12. How to add a conditional unique index on PostgreSQL
  13. PostgreSQL composite primary key
  14. How to reset postgres’ primary key sequence when it falls out of sync?
  15. How to import CSV file data into a PostgreSQL table
  16. The forgotten assignment operator “=” and the commonplace “:=”
  17. Index for finding an element in a JSON array
  18. Equivalent to unpivot() in PostgreSQL
  19. Passing user id to PostgreSQL triggers
  20. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  21. How to generate the “create table” sql statement for an existing table in postgreSQL
  22. PostgreSQL: serial vs identity
  23. Postgres query to check a string is a number
  24. Using pyspark to connect to PostgreSQL
  25. Returning from a function with OUT parameter
  26. Joining Results from Two Separate Databases
  27. Update timestamp when row is updated in PostgreSQL
  28. Postgresql query array of objects in JSONB field
  29. PostgreSQL next value of the sequences?
  30. Spring Docker container cannot access Postgres Docker container

Leave a Comment