Postgres not using your index? It's counting pages, not rows
You get a Seq Scan even with an index because the planner prices a query in pages, and a small table is cheaper to read whole.
PostgreSQL planners determine query plans based on the number of 8 KB pages they need to read and whether those pages are read sequentially or scattered. Sometimes, an index lookup may result in reading the entire heap, which can make you wonder why the planner is ignoring your index. The reason lies in the fact that PostgreSQL prices a plan in pages, and in the example provided, an index on the status column for a table with 30,000 rows saved 12 page reads out of 221.
The index was most effective when the matching rows were scattered across the table, but it still performed better than a full scan in most cases. When dealing with smaller tables, the Seq Scan might be chosen even when only a few rows match, so it's essential to use the pg_stats information before creating an index and avoid including enable_seqscan in application code.
Written by urgent.news from HackerNoon's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.