Skip to main content
PostgreSQL · lesson 2 of 7

Intermediate

6

Advanced

1

Also part of PostgreSQL

7
Open the PostgreSQL page →
PostgreSQL intermediate Lesson 2 of 7

Also part of: Databases

Indexing Strategy: When an Index Actually Helps

Indexing a low-selectivity column barely moves the needle — 13ms to 13ms — while indexing a high-selectivity one drops a query from 14ms to 1ms. Selectivity, not the presence of an index, is what determines the payoff.

By SkillByExample · Published

postgresql indexing b-tree selectivity explain-analyze

An index doesn’t make a query fast by existing — it makes a query fast when the column is selective enough for the index to actually narrow the search.

Baseline: no index on genre

From the previous lesson, filtering books by genre with no index costs a full table scan:

 Seq Scan on books  (cost=0.00..6338.00 rows=40380 width=118) (actual time=0.006..13.769 rows=40000 loops=1)
   Filter: (genre = 'mystery'::text)
   Rows Removed by Filter: 160000
 Execution Time: 14.749 ms

genre has only 5 distinct values, so any one value matches roughly 40,000 of the 200,000 rows — 20% of the table.

Indexing a low-selectivity column

CREATE INDEX idx_books_genre ON books(genre);
EXPLAIN ANALYZE SELECT * FROM books WHERE genre = 'mystery';
CREATE INDEX
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on books  (cost=453.24..4795.99 rows=40380 width=118) (actual time=1.591..12.086 rows=40000 loops=1)
   Recheck Cond: (genre = 'mystery'::text)
   Heap Blocks: exact=3838
   ->  Bitmap Index Scan on idx_books_genre  (cost=0.00..443.14 rows=40380 width=0) (actual time=1.222..1.223 rows=40000 loops=1)
         Index Cond: (genre = 'mystery'::text)
 Planning Time: 0.490 ms
 Execution Time: 13.053 ms
(7 rows)

14.7ms down to 13.1ms — barely a dent. The planner switched to a Bitmap Heap Scan (build a bitmap of matching pages from the index, then read exactly those pages), which is cheaper than a naive row-by-row Index Scan when many rows match, but it still has to touch 3,838 heap pages (Heap Blocks: exact=3838) to fetch 40,000 rows. The index didn’t fail — the column just isn’t selective enough for any index to help much.

Indexing a higher-selectivity column

CREATE INDEX idx_books_author ON books(author);
EXPLAIN ANALYZE SELECT * FROM books WHERE author = 'Author 42';
CREATE INDEX
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on books  (cost=7.50..1171.18 rows=398 width=118) (actual time=0.086..0.994 rows=400 loops=1)
   Recheck Cond: (author = 'Author 42'::text)
   Heap Blocks: exact=400
   ->  Bitmap Index Scan on idx_books_author  (cost=0.00..7.40 rows=398 width=0) (actual time=0.053..0.053 rows=400 loops=1)
         Index Cond: (author = 'Author 42'::text)
 Planning Time: 0.605 ms
 Execution Time: 1.059 ms
(7 rows)

1.06ms. author has 500 distinct values, so any one value matches roughly 400 rows — 0.2% of the table, a hundred times more selective than genre. Same index type, same table, same kind of query — the only thing that changed is how selective the filtered column is, and that alone is the difference between a 13ms query and a 1ms one.

Practice

1. Compute the selectivity ratio for both indexed columns and confirm it against the plan output. 
SELECT
    (SELECT count(DISTINCT genre) FROM books)::float / count(*) AS genre_selectivity,
    (SELECT count(DISTINCT author) FROM books)::float / count(*) AS author_selectivity
FROM books;
 genre_selectivity | author_selectivity
--------------------+--------------------
            2.5e-05 |             0.0025
(1 row)

author’s ratio is 100x higher than genre’s — exactly matching the 100x difference in query time observed above (13ms vs. 1ms). Selectivity isn’t a vague concept here; it’s a number that predicts the payoff.

2. Drop the genre index and confirm the plan reverts to a full Seq Scan. 
DROP INDEX idx_books_genre;
EXPLAIN ANALYZE SELECT * FROM books WHERE genre = 'mystery';
DROP INDEX
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Seq Scan on books  (cost=0.00..6338.00 rows=40380 width=118) (actual time=0.021..24.435 rows=40000 loops=1)
   Filter: (genre = 'mystery'::text)
   Rows Removed by Filter: 160000
 Planning Time: 0.941 ms
 Execution Time: 25.643 ms
(5 rows)

Back to the original Seq Scan plan, and the same order of magnitude as before the index existed — confirming the index was never contributing much for this column, not that dropping it caused a regression.

3. Check how much disk space the author index actually uses. 
SELECT pg_size_pretty(pg_relation_size('idx_books_author'));
 pg_size_pretty
----------------
 1368 kB

Roughly 1.4MB for 200,000 rows — a real, measurable cost that every future INSERT into books also has to pay by updating this index, which is the tradeoff behind “don’t index everything.”

Next: JSONB and full-text — the index types that don’t fit the plain B-tree model at all.

Frequently Asked Questions

Why did indexing genre barely speed up the query?
genre has only 5 distinct values across 200,000 rows, so any single value matches roughly 40,000 rows — 20% of the table. An index still has to look up 40,000 index entries and then fetch the corresponding table pages, which isn't meaningfully cheaper than just scanning the table directly. Low selectivity is the reason, not a flaw in the index.
What is a Bitmap Heap Scan, and why did it show up instead of a plain Index Scan?
When a query is expected to match many rows, Postgres builds an in-memory bitmap of matching heap pages from the index (Bitmap Index Scan), then reads exactly those pages once, in physical order (Bitmap Heap Scan) — cheaper than a plain Index Scan's per-row heap lookups when many rows match. It's still slower than an index doing its best case, which is what a plain Index Scan on a highly selective column looks like.
How do I know if a column is selective enough to be worth indexing?
Divide the number of distinct values by the total row count — the closer that ratio is to 1 (every value unique), the more selective, and the more an index helps. genre's ratio here is 5/200000 (very low); author's is 500/200000 (still not high, but each value matches only ~400 rows instead of ~40,000, which is enough to make the index worthwhile).
Does creating an index have a cost beyond disk space?
Yes — every INSERT, UPDATE, or DELETE that touches an indexed column has to also update the index, so more indexes mean slower writes and more disk I/O, not just more storage. An index on a rarely-queried, low-selectivity column can be a net loss: it costs on every write and barely helps on read.
Should every foreign key column be indexed?
Not automatically — Postgres does not create an index on a foreign key column by default (unlike the primary key it references), and whether it's worth adding one depends on whether that column is actually queried or joined on frequently. It's a common recommendation because foreign keys are joined on often, but check with EXPLAIN ANALYZE rather than indexing every foreign key reflexively.