# PostgreSQL Index Usage and Optimization # PostgreSQL Index Usage and Optimization If you've spent any time tuning PostgreSQL, you've hit the same frustrating moment: you added an index, you're sure it should be used, and the plan still shows a sequential scan. Or the index *is* used, but the query is no faster. Indexing is the single biggest lever in SQL performance, and it's also the category where most of the bad advice lives. "Add an index" is a useful instinct, but it's not the job. The job is: add the right index, in the right shape, for the right query, and drop the ones you don't need. This is article 2 in a series on PostgreSQL query analysis. The pillar is [The Complete Guide to PostgreSQL SQL Query Analysis & Optimization](https://mydba.dev/blog/postgres-query-analysis-complete-guide); we assume you can already read EXPLAIN output. The running dataset is 500k-row `sim_bp_orders` / 200k-row `sim_bp_users` / 50k-row `sim_bp_products` on Neon Postgres 17.8. Every EXPLAIN block below is captured from a real run. ## When the planner picks an index An index is a data structure; "using an index" is a planner decision. PostgreSQL looks at the available indexes on a table, estimates the cost of each candidate plan — sequential scan, index scan, index-only scan, bitmap scan — and picks the cheapest. Three things drive that choice. **Selectivity.** The estimated fraction of rows the query will return. If the filter returns 0.1% of rows, an index scan is almost always cheaper. If the filter returns 30%, it depends on the rest of the query shape, including whether an `ORDER BY` or `LIMIT` interacts with the access path. If the filter returns 70%, the planner will almost always choose a sequential scan because visiting most of the heap sequentially costs less than reading index pages plus random heap I/O. **Correlation.** If the rows matching the filter are physically clustered on disk, the planner's random-access penalty shrinks and an index scan becomes more attractive. If they're scattered, random I/O dominates and seq scan wins. The `pg_stats.correlation` column (range -1 to 1) tells you how clustered each column's values are. Time-series tables (`created_at`) often have near-1 correlation because they're append-mostly; status columns usually hover near 0. **Cost parameters.** `random_page_cost` (default 4.0) vs `seq_page_cost` (default 1.0). On SSD-backed storage those defaults are too conservative; lowering `random_page_cost` to 1.5 or 2.0 makes the planner reach for indexes more readily. Setting it *below* `seq_page_cost` is almost always wrong. If you're tempted to go there, you probably want to raise `effective_cache_size` instead. When a plan has a `Seq Scan`, no index-type nodes at all, and more than two nodes total, you probably have a missing or ignored index. It's a signal, not a verdict — some queries genuinely don't want an index — but it's always worth checking whether an obvious filter or join column is un-indexed. ## The boring case that still matters The cheapest index in any database is the primary-key btree: ```sql SELECT * FROM sim_bp_users WHERE user_id = 12345; ``` ``` Index Scan using sim_bp_users_pkey on sim_bp_users (cost=0.42..8.44 rows=1 width=51) (actual time=8.683..8.686 rows=1 loops=1) Index Cond: (sim_bp_users.user_id = 12345) Buffers: shared read=4 Execution Time: 9.700 ms ``` Four shared-buffer reads for a 200,000-row table: some combination of btree-descent pages plus the heap page for the matching row. The 9.7 ms execution time is likely dominated by the cold-cache reads against Neon's networked storage; on a warm-cache benchmark this drops to sub-millisecond. This is the shape every OLTP single-row lookup should have. ## The four design choices that matter ### 1. Column selection — matching the query shape A composite index on `(user_id, created_at)` helps: - `WHERE user_id = ?` (uses the leading column alone). - `WHERE user_id = ? AND created_at > ?` (uses both). - `WHERE user_id = ? ORDER BY created_at DESC LIMIT n` (uses leading equality + sorted trailing column). It does **not** help `WHERE created_at > ?` in isolation, because the index is organised first by `user_id` — PostgreSQL would have to read every leaf page. This is the leftmost-prefix rule: a btree composite index can answer queries that use a contiguous prefix of its columns, starting with the leading one. Rule of thumb: leading columns should be equality predicates, trailing columns range predicates or sort keys. `(tenant_id, created_at)`, not `(created_at, tenant_id)`. ### 2. Partial indexes — when 80% of the table is irrelevant ```sql CREATE INDEX idx_bp_orders_pending_recent ON sim_bp_orders (created_at) WHERE status = 'pending'; ``` The index only contains rows where `status = 'pending'`, so it's roughly one-fifth the size of a full index on `created_at`. The planner will use this index for any query whose `WHERE` clause *implies* `status = 'pending'` — it proves this by theorem-proving over the predicates. `WHERE status = 'pending' AND created_at > now() - interval '1 day'` works, but `WHERE status IN ('pending', 'shipped') AND ...` doesn't, because the `IN` predicate doesn't imply the partial predicate. Partial indexes are fragile to query rewording: a function on the indexed column, a cast, or a change of predicate can stop the implication proof from succeeding even when the rewritten query is logically equivalent. ### 3. Covering indexes — eliminating heap fetches `INCLUDE` tucks non-key columns into the leaf pages: ```sql CREATE INDEX idx_bp_orders_pending_by_amount ON sim_bp_orders (total_amount_cents DESC) INCLUDE (order_id, user_id, created_at) WHERE status = 'pending'; ``` A query that `SELECT`s any combination of `order_id, user_id, total_amount_cents, created_at` from this index can be served entirely from index pages — provided the visibility map marks the relevant heap pages as all-visible. On a write-heavy table where autovacuum can't keep up, you may see non-zero `Heap Fetches:` in EXPLAIN, which defeats most of the benefit. `INCLUDE` columns cannot be used for index conditions. Rule: put columns used for filtering/joining/ordering in the key; put columns you're only retrieving in `INCLUDE`. ### 4. Expression indexes — indexing computed values This is where most "why isn't my index being used?" problems live. A btree on `email` can't serve `WHERE lower(email) = ?` or `WHERE lower(email) LIKE 'prefix%'` because the indexed value (`email`) isn't the value the predicate is comparing (`lower(email)`). Case-insensitive prefix search on a 200k-row table: ``` Gather (cost=1000.00..5841.09 rows=1000 width=25) (actual time=0.553..122.758 rows=1 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on sim_bp_users Filter: (lower((email)::text) ~~ 'user12%'::text) Rows Removed by Filter: 94444 Execution Time: 122.833 ms ``` Parallel seq scan, 94,000 rows filtered per worker, 122 ms. The fix: ```sql CREATE INDEX idx_bp_users_email_lower ON sim_bp_users (lower(email) text_pattern_ops); ``` For equality on lowercased email, a plain `CREATE INDEX ... (lower(email))` is enough. For prefix `LIKE`, `text_pattern_ops` is needed because PostgreSQL can only rewrite `LIKE 'prefix%'` into an index range scan when the index orders text by byte value rather than by the database's default collation rules. The existing `idx_sim_bp_users_email_pattern` on `email text_pattern_ops` demonstrates this: ``` Index Only Scan using idx_sim_bp_users_email_pattern on sim_bp_users (cost=0.42..29.87 rows=20 width=8) (actual time=0.057..24.729 rows=20 loops=1) Index Cond: ((email ~>=~ 'user12'::text) AND (email ~<~ 'user13'::text)) Filter: ((email)::text ~~ 'user12%'::text) Heap Fetches: 0 Execution Time: 24.757 ms ``` The Index Cond uses `~>=~` and `~<~` — real PostgreSQL operators from `text_pattern_ops` that do byte-order comparisons, distinct from the default locale-aware `>=` and `<`. 24.7 ms vs 122.8 ms — five times faster, and the gap widens on larger tables. ## Index types beyond btree ### GIN — when equality becomes containment ```sql CREATE INDEX idx_events_data_gin ON events USING gin (event_data jsonb_path_ops); -- Now this is sargable: SELECT * FROM events WHERE event_data @> '{"type": "purchase"}'; ``` `jsonb_path_ops` is a more specialised operator class than the default `jsonb_ops`: it indexes only the `@>` operator but produces a significantly smaller and faster index. GIN with `pg_trgm` turns substring `LIKE` queries (`LIKE '%needle%'`) into index-backed scans. ### BRIN — when the data is physically ordered ```sql CREATE INDEX idx_bp_orders_created_at_brin ON sim_bp_orders USING brin (created_at); ``` For our 500,000-row orders table, a BRIN index is ~24 kB; a btree on the same column is ~5 MB. BRIN loses effectiveness immediately if the data isn't correlated — on a shuffled table, the min/max of every page range overlaps the whole value domain and the planner can't skip anything. BRIN is effectively useless on uncorrelated columns and brilliant on time-series data. ### GiST / SP-GiST / hash Geometric types, ranges, and fuzzy matching use GiST or SP-GiST. Hash indexes only support equality and are usually beaten by btrees even for point lookups — use them only when you've measured a specific case where they win. ## When NOT to add an index 1. **Write-heavy, read-light tables.** Every index is write cost. 2. **Low selectivity.** A btree on a boolean `is_active` where 90% of rows are active will never be used. A partial index on the minority value is better. 3. **Queries that need most of the table.** Reports aggregating over large windows are best served by parallel seq scan. 4. **Redundant indexes.** `(a, b, c)` subsumes `(a, b)` and `(a)`. Drop the prefixes. ## Finding unused indexes ```sql SELECT s.indexrelname AS index_name, s.relname AS table_name, pg_size_pretty(pg_relation_size(s.indexrelid)) AS size, s.idx_scan FROM pg_stat_user_indexes s WHERE s.schemaname = 'public' AND s.idx_scan = 0 AND NOT EXISTS ( SELECT 1 FROM pg_constraint c WHERE c.conindid = s.indexrelid AND c.contype IN ('p', 'u', 'x') ) ORDER BY pg_relation_size(s.indexrelid) DESC; ``` Real result from the running database: | index_name | size | idx_scan | |---|---|---| | idx_sim_bp_users_username_pattern | 6184 kB | **0** | | idx_sim_bp_users_email_pattern | 7960 kB | 1 | One 6 MB index with zero scans is a straightforward drop. The `NOT EXISTS` clause skips PK/unique/exclusion indexes — those enforce integrity and are used internally even if no user query hits them. Two caveats: `pg_stat_reset()` zeros the counter, and a replica's stats only count scans on that replica. ## Adding the right index — a complete example ```sql SELECT order_id, user_id, total_amount_cents, created_at FROM sim_bp_orders WHERE status = 'pending' ORDER BY total_amount_cents DESC LIMIT 50; ``` 51 ms seq scan with a top-n heapsort. Three candidates: 1. **`(status)`** — cheapest, still needs a sort step. 2. **`(status, total_amount_cents DESC)`** — solves filter and sort. The sort is free because the index is already ordered on the trailing column within each status group. 3. **`(total_amount_cents DESC) WHERE status = 'pending'`** — only pending rows indexed. Smaller, faster to maintain, but only helps pending queries. Option 3 plus `INCLUDE (order_id, user_id, created_at)` gives Index Only Scan and is the right call here. If the dashboard later adds `status IN ('pending', 'processing')`, you'd want option 2 instead. Design indexes for the query you have, but re-read the plans every six months to notice when the query has changed underneath you.