Postgres not using your index? It's counting pages, not rows

Slow Postgres, part 1 of 8. The SQL and raw numbers for every test are on GitHub.

Seq Scan reads heap pages in order; Index Scan reads the B-tree, then one heap page per TID

A Seq Scan reads each page once, in order. An index lookup walks the B-tree and then reads one heap page per TID.

You add an index on status, run EXPLAIN, and Postgres reads the whole table anyway. ANALYZE doesn't change the plan. Someone suggests SET enable_seqscan = off, the plan finally shows your index, and the query gets slower.

Usually the planner is right. It prices a plan by the 8 KB pages it has to read and by whether it reads them in order or jumps around between them, and once you count pages the same way, you can answer most "why is it ignoring my index" tickets yourself.

This series started...

Copyright of this story solely belongs to hackernoon.com. To see the full text click HERE