Index Best Practices and EXPLAIN
Reading Plans, Spotting Seq Scans, Avoiding Over-Indexing
LinkedIn Hook
"You added an index. The query got slower. You added another. The writes fell off a cliff."
Most engineers learn indexes the same way: a query is slow, somebody says "add an index on that column," the query gets faster, and the lesson stops there. So they keep adding indexes — one per WHERE clause, one per JOIN, one per ORDER BY — until a 12-column table has 18 indexes, every INSERT writes to all of them, and the planner is now choosing the wrong one because the statistics got confused.
Indexing is not "more is better." It is a budget. Every index speeds up some reads and slows down every write. Every index takes disk space, sits in the buffer cache competing with your hot data, and gives the query planner one more option to evaluate (and potentially get wrong). The skill is not adding indexes — it is knowing which ones to add, which ones to remove, and how to read EXPLAIN ANALYZE so you can tell the difference between a query that is fast because of your index and a query that is fast despite it.
EXPLAIN ANALYZE is the single most important tool in a backend engineer's database toolkit. It tells you exactly what the planner chose, how long each step took, how many rows it expected vs how many it actually saw, and whether your shiny new index was even used. The estimate-vs-actual gap is where 90% of "mystery slow queries" hide. The seq scan you assumed was an index scan is where the other 10% hide.
In Lesson 9.6, I break down index best practices end to end: how to read EXPLAIN ANALYZE in PostgreSQL, how to spot a sequential scan that should not be there, how to avoid over-indexing, when to reach for partial and functional indexes, and how to use pg_stat_user_indexes to find the indexes nobody is actually using.
Read the full lesson -> [link]
#SQL #PostgreSQL #Database #QueryPerformance #Indexing #BackendDevelopment #InterviewPrep
What You'll Learn
- How to read a PostgreSQL EXPLAIN ANALYZE plan node by node — cost, rows, time, loops
- The difference between EXPLAIN (estimate only) and EXPLAIN ANALYZE (actually runs the query)
- How to spot a sequential scan that should be an index scan and what causes it
- Why over-indexing slows down writes, wastes cache, and confuses the planner
- Partial indexes for filtering hot subsets of a table (e.g. "only active users")
- Functional (expression) indexes for queries that wrap a column in a function
- How to find unused indexes with
pg_stat_user_indexesand drop them safely - Key differences between PostgreSQL EXPLAIN and MySQL EXPLAIN output
The Roadmap App Analogy — The Planner Is a GPS, EXPLAIN Is the Route Preview
Imagine you open a maps app to drive across town. You type in a destination and the app shows you three possible routes: a 12-minute highway route, a 15-minute back-streets route, and a 22-minute scenic route. It picked the highway because at this hour traffic is light and the highway is shortest. But the app does not just silently start navigating — it shows you the route on the screen first, with the time estimate, the distance, and the steps. If the chosen route looks wrong (maybe you know there is construction the app does not), you can override it.
That is exactly what EXPLAIN does for a SQL query. The query planner is the GPS — it considers many possible ways to execute the same query (sequential scan, index scan, bitmap scan, hash join, nested loop join) and picks the one it thinks is cheapest based on table statistics. EXPLAIN is the route preview: it shows you which plan the planner chose, the estimated cost, the estimated row counts, and how the steps connect. EXPLAIN ANALYZE goes one step further — it actually drives the route and reports the real time, the real row counts, and any traffic the planner did not predict.
The reason this matters: the planner's estimates can be wrong. If table statistics are stale, if the data distribution is skewed, if a parameter is unusual, the planner picks the wrong route and you get a slow query. The only way to catch that is to compare what the planner predicted against what really happened. A 10x gap between estimated rows and actual rows is the single biggest red flag in query tuning, and it lives right there in the EXPLAIN ANALYZE output once you know how to read it.
+---------------------------------------------------------------+
| PLANNER vs REALITY |
+---------------------------------------------------------------+
| |
| EXPLAIN -> "Here is the route I would take." |
| (estimates only, query never runs) |
| |
| EXPLAIN ANALYZE -> "Here is the route I took, and |
| here is what actually happened." |
| (query runs, real timings, real row counts) |
| |
| THE GAP TO WATCH: |
| |
| rows=10 (estimate) |
| actual rows=4823 <- planner was wrong by 482x |
| |
| -> stale statistics, skewed data, or bad correlation |
| -> fix with ANALYZE, extended statistics, or rewrite |
| |
+---------------------------------------------------------------+Napkin AI Visual Prompt: "Dark navy gradient (#0a0f1f -> #111a2e). Center: a GPS map UI with three route options drawn in white monospace lines. Sky blue (#4fc3f7) highlight on the chosen route labeled 'Index Scan: 0.4ms'. Rose (#ff5c8a) outline on a rejected route labeled 'Seq Scan: 240ms'. Below the map, an EXPLAIN ANALYZE output box in white monospace showing 'rows=10' on the left and 'actual rows=4823' on the right with a sky blue arrow between them and the label '482x miss'. Subtle grid pattern overlay."
Reading EXPLAIN ANALYZE — The Core Vocabulary
PostgreSQL's EXPLAIN ANALYZE output is a tree of nodes. Each node represents one operation (a scan, a join, a sort, an aggregate), and the tree is read inside out, bottom up: the deepest indented node runs first, its results flow up to its parent, and so on until the top node returns the final result to the client.
Every node prints a header like this:
Index Scan using idx_users_email on users (cost=0.42..8.44 rows=1 width=64) (actual time=0.018..0.020 rows=1 loops=1)Each piece matters:
- Index Scan using idx_users_email on users — the operation and the object. This node walked the
idx_users_emailindex on theuserstable. - cost=0.42..8.44 — the planner's cost estimate in arbitrary units. The first number is the startup cost (work before the first row can be returned), the second is the total cost. Lower is better, and the planner chooses the plan with the lowest total cost.
- rows=1 — the planner's estimate of how many rows this node will return.
- width=64 — the average estimated width of a row in bytes (mostly only matters for memory planning).
- actual time=0.018..0.020 — the real wall-clock time in milliseconds for the first row and the last row, measured during this run.
- rows=1 (the second one, inside
actual) — the real number of rows this node returned. - loops=1 — how many times this node was executed. For the inner side of a nested loop join, loops can be large; the printed actual time is per loop, so you multiply by loops to get the total.
The single most important thing to compare: estimated rows vs actual rows. A 10x or larger gap means the planner has bad statistics for this table or column, and any plan it chose downstream of that misestimate is likely wrong.
+---------------------------------------------------------------+
| ANATOMY OF AN EXPLAIN ANALYZE NODE |
+---------------------------------------------------------------+
| |
| Index Scan using idx_users_email on users |
| ^op ^index ^table |
| |
| (cost=0.42..8.44 rows=1 width=64) |
| ^startup ^total ^est ^bytes |
| |
| (actual time=0.018..0.020 rows=1 loops=1) |
| ^first ^last ^real ^times-run |
| |
| COMPARE: planner rows=1 vs actual rows=1 -> good |
| COMPARE: planner rows=10 vs actual rows=4823 -> 482x BAD |
| |
+---------------------------------------------------------------+The common scan node types you will see:
- Seq Scan — read every row of the table from start to end. Fine for small tables and queries that touch a large fraction of rows. A red flag for big tables when you expected an index to be used.
- Index Scan — walk an index in order, fetching matching rows from the table heap. Best for selective queries returning a small number of rows.
- Index Only Scan — answer the query entirely from the index, without ever touching the heap. Requires a covering index and a recently-vacuumed table (visibility map).
- Bitmap Heap Scan + Bitmap Index Scan — use the index to build a bitmap of matching pages, then read those pages in physical order. Good when an index returns many rows (hundreds to tens of thousands).
- Sort — buffer rows and sort them. Look for
Sort Method: external merge Disk— that means the sort spilled to disk and is much slower thanquicksort Memory.
Example 1 — Spotting a Seq Scan That Should Not Be There
Let's build a realistic example: a users table with a million rows, a few queries, and the EXPLAIN output that exposes the problem.
-- Setup: a users table with a million rows
-- We will use this throughout the lesson
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email TEXT NOT NULL,
status TEXT NOT NULL, -- 'active', 'inactive', 'banned'
signup_date DATE NOT NULL,
last_login TIMESTAMPTZ
);
-- Insert 1 million synthetic rows
INSERT INTO users (email, status, signup_date, last_login)
SELECT
'user' || g || '@example.com',
CASE WHEN g % 100 < 95 THEN 'active'
WHEN g % 100 < 99 THEN 'inactive'
ELSE 'banned' END,
DATE '2020-01-01' + (g % 1500),
NOW() - ((g % 365) || ' days')::interval
FROM generate_series(1, 1000000) g;
-- Refresh planner statistics so cost estimates are accurate
ANALYZE users;Now run a query that filters on email — a column with no index yet — and ask Postgres to explain it.
EXPLAIN ANALYZE
SELECT id, email, status
FROM users
WHERE email = 'user42@example.com';Output (abbreviated):
Seq Scan on users (cost=0.00..23334.00 rows=1 width=37)
(actual time=0.412..187.624 rows=1 loops=1)
Filter: (email = 'user42@example.com'::text)
Rows Removed by Filter: 999999
Planning Time: 0.082 ms
Execution Time: 187.651 msRead this carefully. The planner estimated 1 row (correct), but it had to do a Seq Scan — read all 1,000,000 rows and throw away 999,999 of them — because there is no index on email. The query took 187 ms for a single-row lookup. On a high-traffic API endpoint, that is catastrophic.
Add the missing index and re-run:
-- Add the index that should have been there from the start
CREATE INDEX idx_users_email ON users(email);
EXPLAIN ANALYZE
SELECT id, email, status
FROM users
WHERE email = 'user42@example.com';Output:
Index Scan using idx_users_email on users (cost=0.42..8.44 rows=1 width=37)
(actual time=0.029..0.031 rows=1 loops=1)
Index Cond: (email = 'user42@example.com'::text)
Planning Time: 0.124 ms
Execution Time: 0.052 msThe plan switched to Index Scan, the actual time dropped from 187 ms to 0.05 ms — a 3,750x improvement — and the Rows Removed by Filter line is gone because the index returned exactly the right row without any post-filtering. This is the canonical "missing index" fix, and EXPLAIN ANALYZE made it impossible to miss.
Example 2 — Functional Indexes for Queries That Wrap a Column
Here is a subtler trap. Suppose you allow case-insensitive email lookup:
EXPLAIN ANALYZE
SELECT id, email
FROM users
WHERE LOWER(email) = 'user42@example.com';Output:
Seq Scan on users (cost=0.00..25834.00 rows=5000 width=29)
(actual time=0.523..201.882 rows=1 loops=1)
Filter: (lower(email) = 'user42@example.com'::text)
Rows Removed by Filter: 999999
Execution Time: 201.911 msBack to a Seq Scan — even though we built idx_users_email in the previous example. Why? Because the index was built on email, but the query filters on LOWER(email). To the planner, those are completely different expressions. An index on a column cannot serve a query that wraps the column in a function.
The fix is a functional index (also called an expression index) that stores the result of LOWER(email):
-- Functional index: index the EXPRESSION, not the raw column
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
EXPLAIN ANALYZE
SELECT id, email
FROM users
WHERE LOWER(email) = 'user42@example.com';Output:
Index Scan using idx_users_email_lower on users (cost=0.42..8.44 rows=1 width=29)
(actual time=0.034..0.036 rows=1 loops=1)
Index Cond: (lower(email) = 'user42@example.com'::text)
Execution Time: 0.058 msNow the index is used because the indexed expression matches the query expression exactly. The same trick works for DATE(created_at), (price * quantity), EXTRACT(YEAR FROM signup_date), and any other deterministic expression you find yourself filtering on repeatedly. The rule of thumb: if the planner sees a function call wrapping a column in your WHERE clause and you cannot rewrite the query, you need a functional index.
Example 3 — Partial Indexes for Hot Subsets
Most tables have a "hot" subset and a "cold" subset. In our users table, 95% of rows are active, 4% are inactive, and 1% are banned. Almost every query in the application filters on status = 'active' and additionally on something like last_login.
A naive index covers all rows:
CREATE INDEX idx_users_last_login ON users(last_login);This works, but it indexes a million rows when most queries only care about the active ones. A partial index indexes only the rows matching a WHERE clause baked into the index definition:
-- Partial index: only rows where status = 'active' are indexed
CREATE INDEX idx_users_active_last_login
ON users(last_login)
WHERE status = 'active';This index is roughly 5% smaller in disk and memory because it only contains the 950,000 active rows. It is also faster to scan, faster to update on writes that touch non-active rows (which can skip it entirely), and the planner will choose it when the query's WHERE clause is a logical superset of the index's WHERE clause:
EXPLAIN ANALYZE
SELECT id, email
FROM users
WHERE status = 'active'
AND last_login > NOW() - INTERVAL '7 days'
ORDER BY last_login DESC
LIMIT 50;Output:
Limit (cost=0.42..18.62 rows=50 width=29)
(actual time=0.041..0.612 rows=50 loops=1)
-> Index Scan Backward using idx_users_active_last_login on users
(cost=0.42..1841.20 rows=5060 width=29)
(actual time=0.040..0.604 rows=50 loops=1)
Index Cond: (last_login > (now() - '7 days'::interval))
Planning Time: 0.176 ms
Execution Time: 0.638 msTwo things are great about this plan. First, Index Scan Backward means the planner walked the b-tree in reverse to satisfy ORDER BY last_login DESC for free, with no Sort node. Second, the index condition only references last_login because status = 'active' is implicit in the index definition — the planner did not need to recheck it. Partial indexes are one of the most under-used Postgres features and one of the highest-leverage tools when you have a clear hot subset.
The catch: a partial index only helps queries whose WHERE clause logically implies the index's WHERE clause. If your query says WHERE status IN ('active', 'inactive'), the partial index above cannot be used.
Example 4 — Over-Indexing and the Write Tax
Now let's see the dark side. Suppose somebody panicked about performance and added an index on every column "just in case":
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_users_signup_date ON users(signup_date);
CREATE INDEX idx_users_last_login2 ON users(last_login);
CREATE INDEX idx_users_email2 ON users(email);
-- Plus the four we already created above. Eight total.Now measure what an INSERT costs:
EXPLAIN (ANALYZE, BUFFERS)
INSERT INTO users (email, status, signup_date, last_login)
VALUES ('newuser@example.com', 'active', CURRENT_DATE, NOW());Output (abbreviated):
Insert on users (cost=0.00..0.01 rows=0 width=0)
(actual time=4.812..4.813 rows=0 loops=1)
Buffers: shared hit=42 read=3 dirtied=11
Execution Time: 4.901 msCompare against the same INSERT on a copy of the table with only the primary key index:
Insert on users_minimal (cost=0.00..0.01 rows=0 width=0)
(actual time=0.218..0.219 rows=0 loops=1)
Buffers: shared hit=4 dirtied=2
Execution Time: 0.231 msThe over-indexed insert is ~21x slower, dirties 5x more buffer pages, and reads from disk because the working set of all eight indexes no longer fits in cache. Every UPDATE that touches an indexed column has the same problem. On a table that takes 10,000 inserts/second, this is the difference between a healthy database and one that constantly hits I/O saturation.
The core principle: every index makes some reads faster and every write slower. The break-even point depends on your read/write ratio, but for high-write tables, the rule is "the fewest indexes that satisfy the queries you actually run." Not the queries you might run someday.
Finding Unused Indexes with pg_stat_user_indexes
PostgreSQL keeps statistics on every index: how many times it has been scanned, how many tuples it returned, how many were fetched. The pg_stat_user_indexes view exposes all of this, and it is the primary tool for finding indexes that nobody uses.
-- List indexes that have never been scanned since the last stats reset
SELECT
schemaname,
relname AS table_name,
indexrelname AS index_name,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname NOT IN ('pg_catalog', 'pg_toast')
ORDER BY pg_relation_size(indexrelid) DESC;Sample output:
schemaname | table_name | index_name | idx_scan | index_size
------------+------------+-------------------------+----------+------------
public | users | idx_users_signup_date | 0 | 24 MB
public | users | idx_users_status | 0 | 18 MB
public | orders | idx_orders_legacy_ref | 0 | 312 MB
public | events | idx_events_user_session | 0 | 1450 MBEvery row in this output is an index that has cost you disk space and write performance and has not paid you back with a single read. Before dropping, verify three things:
- Stats are not freshly reset. Run
SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();to make sure stats have been collecting for at least a full business cycle (a week, ideally a month). - The index does not enforce a constraint. Unique indexes, primary keys, and exclusion constraints have
idx_scan = 0if no query happens to use them, but dropping them changes table semantics. Filter withAND indexrelid NOT IN (SELECT conindid FROM pg_constraint). - No cron job, batch report, or rare admin query needs it. A monthly invoice run might be the only thing using a particular index, and it will be the only thing that breaks if you drop it.
When you are confident, drop with DROP INDEX CONCURRENTLY idx_name so that the drop does not take an exclusive lock on the table.
-- Safe drop: does not block reads or writes during removal
DROP INDEX CONCURRENTLY idx_orders_legacy_ref;The complementary view, pg_stat_user_tables, tells you the read/write ratio of each table — combine the two to make smart calls about where to add indexes (high-read tables) and where to remove them (high-write tables).
MySQL EXPLAIN — Key Differences
Most of the concepts above transfer directly to MySQL, but the output format is completely different. MySQL prints a tabular row per join step, not a tree of nodes:
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 1000000 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+The columns to focus on:
- type —
ALLis a full table scan (the equivalent of Postgres Seq Scan),refis an index lookup,rangeis an index range scan,const/eq_refare unique-key lookups. Anything other thanALL(orindex, which is a full index scan) generally means an index is being used. - key — the index actually chosen.
NULLmeans no index was used. - rows — the planner's estimate of rows examined.
- Extra —
Using indexmeans a covering / index-only scan,Using filesortmeans a sort that did not match an index,Using temporarymeans a temporary table was created (often expensive).
For real measurements (the equivalent of Postgres EXPLAIN ANALYZE), MySQL 8.0.18+ supports:
EXPLAIN ANALYZE SELECT ... ;This produces a tree-format output similar in spirit to Postgres. The key conceptual difference: MySQL's planner has historically been simpler and more index-driven, while Postgres aggressively considers many plan shapes. In both engines, the workflow is the same: run the query under EXPLAIN, look for full table scans where you expected index usage, and check the gap between estimated and actual rows.
Common Mistakes
1. Reading EXPLAIN without ANALYZE and trusting the cost numbers.
Plain EXPLAIN only shows the planner's estimates. Costs are in arbitrary units that depend on seq_page_cost, random_page_cost, and other GUC settings, and they do not correspond to any real-world unit like milliseconds. Always use EXPLAIN ANALYZE (or EXPLAIN (ANALYZE, BUFFERS) for I/O details) when you actually want to know whether your query is fast. The catch: ANALYZE runs the query for real, so do not run it on a destructive UPDATE or DELETE in production without wrapping it in a transaction you roll back.
2. Adding an index every time a query is slow. Indexes have a cost. Every index slows down INSERT, UPDATE (on indexed columns), and DELETE; competes with hot data for buffer cache; and gives the planner more options to consider — sometimes choosing the wrong one. Before adding an index, ask: is there an existing index I could extend with another column? Is this query running often enough to justify the write tax? Could a partial or covering index replace two existing indexes?
3. Wrapping the indexed column in a function and expecting the index to still work.
WHERE LOWER(email) = ? cannot use an index on email alone. WHERE created_at::date = ? cannot use an index on created_at. WHERE TRIM(name) = ? cannot use an index on name. The fix is either to rewrite the query so the column appears bare on one side of the comparison (WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02') or to create a functional index on the exact expression you query.
4. Forgetting to ANALYZE after a large data load.
The planner relies on table statistics to choose plans. After a bulk INSERT, COPY, or restore, run ANALYZE table_name; to refresh stats. Without it, the planner thinks the table is empty and may choose a nested loop join over a hash join (or vice versa), turning a 100 ms query into a 10-minute one. Autovacuum does this automatically eventually, but "eventually" is not soon enough after a load.
5. Ignoring the gap between estimated and actual rows.
This is the single biggest tuning signal in an EXPLAIN ANALYZE plan. If the planner thinks a node returns 10 rows and it actually returns 5,000, every plan choice downstream of that misestimate is suspect. Look for skewed columns (a few common values dominate), correlated columns (country and language), or queries with parameter values the planner cannot predict. Fixes range from ANALYZE to CREATE STATISTICS for multi-column correlations to query rewrites.
Interview Questions
1. "Walk me through how you would diagnose a query that suddenly got slow in production."
Start with EXPLAIN ANALYZE on the actual query, with realistic parameters, against a database with production-like data. Read the plan inside out: find the most expensive node (highest actual time times loops), check whether the plan shape makes sense (Seq Scan on a large table with a selective filter is suspicious), and compare estimated rows against actual rows on every node. A large estimate-vs-actual gap means stale statistics or skewed data — fix with ANALYZE, extended statistics, or a query rewrite. If the plan looks reasonable but the query is still slow, check EXPLAIN (ANALYZE, BUFFERS) for I/O patterns; lots of read (cache misses) means the working set no longer fits in memory. Finally, compare against the historical plan from pg_stat_statements or your monitoring tool — sometimes the plan flipped because an autovacuum ran, an index was added, or the data crossed a tipping point that flipped the planner's decision.
2. "When would you use a partial index instead of a regular index, and what is the trade-off?"
A partial index makes sense when a clear majority of queries against a table filter on the same predicate, and that predicate selects a meaningful subset of rows. Classic examples: WHERE status = 'active' on a users table where most queries only care about active users, WHERE deleted_at IS NULL on a soft-delete table, WHERE is_published = true on a blog post table. The benefits are smaller index size (less disk, less memory, less of the buffer cache used), faster index scans because there are fewer index entries, and lower write cost because INSERTs and UPDATEs that fall outside the predicate skip the index entirely. The trade-off is rigidity: the index can only serve queries whose WHERE clause is a logical superset of the index's WHERE clause. A partial index WHERE status = 'active' cannot help a query WHERE status IN ('active', 'inactive'). So you have to be confident the predicate matches your query patterns before committing.
3. "What is a functional index, and what problem does it solve?"
A functional index (or expression index) stores the result of an expression rather than a raw column value. The problem it solves: query planners require an exact match between the indexed expression and the filter expression. If you query WHERE LOWER(email) = 'foo@bar.com' and you only have a regular index on email, the planner cannot use it — it sees a function call and gives up, falling back to a sequential scan. Creating CREATE INDEX idx ON users(LOWER(email)) gives the planner an exact match: the indexed expression and the query expression are byte-for-byte identical, so the index is used and the query is fast. Common cases include case-insensitive lookups, date truncation (DATE(created_at)), arithmetic ((price * quantity)), and JSON extraction ((payload->>'user_id')). The cost is the same as any other index — disk space and write overhead — plus the constraint that the expression must be deterministic and immutable.
4. "Your CTO says 'add an index on every column we ever filter by.' Why is that wrong?"
Because indexes are not free. Every index costs disk space, fills buffer cache pages that would otherwise hold hot data, slows down every INSERT, slows down every UPDATE that touches an indexed column, and slows down every DELETE. On a high-write table, going from 2 indexes to 8 indexes can multiply write latency 5-10x and dramatically increase WAL volume. Worse, more indexes give the planner more options to evaluate — and more chances to pick the wrong one based on stale statistics. The right approach is to identify the queries that actually run in production (using pg_stat_statements), index the ones that are slow and frequent, and use partial or covering indexes to combine multiple use cases into one index where possible. Then periodically check pg_stat_user_indexes and drop the ones with idx_scan = 0. The goal is the smallest set of indexes that makes the queries you actually run fast, not insurance against queries you might run someday.
5. "How do you find indexes that should be dropped, and what do you check before dropping them?"
Query pg_stat_user_indexes for indexes where idx_scan = 0, sorted by pg_relation_size(indexrelid) descending, so the biggest waste is at the top. Before dropping any of them, verify three things. First, statistics have been collecting long enough — check pg_stat_database.stats_reset and make sure at least a full business cycle has passed (a week minimum, ideally a month, so monthly batch jobs are included). Second, the index does not back a constraint — primary keys, unique constraints, and exclusion constraints all have backing indexes that report idx_scan = 0 if no query happens to use them, but dropping them changes table semantics. Filter them out with a join on pg_constraint. Third, ask the team — there may be a quarterly report, a debugging script, or an admin query that runs rarely but matters. Once you are confident, use DROP INDEX CONCURRENTLY so the drop does not block reads or writes. And keep a record of what you dropped and when, so if a slow query shows up next week you can correlate.
Quick Reference — Cheat Sheet
+---------------------------------------------------------------+
| EXPLAIN ANALYZE CHEAT SHEET |
+---------------------------------------------------------------+
| |
| RUN A PLAN: |
| EXPLAIN ANALYZE SELECT ... ; |
| EXPLAIN (ANALYZE, BUFFERS) SELECT ... ; -- with I/O |
| EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ... ; |
| |
| READ INSIDE OUT: |
| Deepest indented node runs first. |
| Results flow up to the parent. |
| |
| KEY NUMBERS: |
| cost=startup..total -- planner's guess |
| rows=N -- planner's row estimate |
| actual time=first..last -- real ms (PER LOOP) |
| rows=N (in actual) -- real row count |
| loops=N -- multiply actual time by this |
| |
| RED FLAGS: |
| Seq Scan on a big table with a selective filter |
| estimated rows vs actual rows differ by 10x or more |
| Sort Method: external merge Disk |
| Rows Removed by Filter very high |
| Nested Loop with high loops on the inner side |
| |
+---------------------------------------------------------------+
+---------------------------------------------------------------+
| INDEX TYPES AND WHEN TO USE THEM |
+---------------------------------------------------------------+
| |
| REGULAR B-TREE |
| CREATE INDEX idx ON t(col); |
| -> equality, range, ORDER BY, default choice |
| |
| PARTIAL |
| CREATE INDEX idx ON t(col) WHERE status = 'active'; |
| -> hot subsets, smaller and faster |
| |
| FUNCTIONAL / EXPRESSION |
| CREATE INDEX idx ON t(LOWER(email)); |
| -> queries that wrap the column in a function |
| |
| COVERING (INCLUDE) |
| CREATE INDEX idx ON t(a) INCLUDE (b, c); |
| -> index-only scans, no heap fetch |
| |
| COMPOSITE |
| CREATE INDEX idx ON t(a, b, c); |
| -> queries filtering on a, or a+b, or a+b+c (left prefix) |
| |
+---------------------------------------------------------------+
+---------------------------------------------------------------+
| KEY RULES |
+---------------------------------------------------------------+
| |
| 1. Use EXPLAIN ANALYZE, not plain EXPLAIN, for real timings |
| 2. Compare estimated rows vs actual rows on every node |
| 3. ANALYZE after bulk loads to refresh statistics |
| 4. Wrap a column in a function -> need a functional index |
| 5. Hot subset filters -> partial index |
| 6. Every index taxes every write — the budget is finite |
| 7. Drop unused indexes (pg_stat_user_indexes idx_scan = 0) |
| 8. DROP INDEX CONCURRENTLY in production, never plain DROP |
| |
+---------------------------------------------------------------+| Symptom in EXPLAIN | Likely Cause | Fix |
|---|---|---|
| Seq Scan on big table | Missing index | CREATE INDEX on filter column |
Seq Scan with LOWER() filter | Function wraps column | Functional index |
| Estimated rows off by 100x | Stale stats | ANALYZE table |
| Sort Method: external merge Disk | Sort spilled to disk | Index matching ORDER BY, or raise work_mem |
| Nested Loop, high inner loops | Wrong join order | ANALYZE, rewrite, or enable_nestloop=off |
| Index Scan but slow | Heap fetches dominate | Covering index with INCLUDE |
| Bitmap Heap Scan, lots of pages | Low index selectivity | Partial index, or different filter |
idx_scan = 0 in pg_stat | Index unused | DROP INDEX CONCURRENTLY |
Prev: Lesson 9.5 -- Sorted, Unsorted, Covering Indexes Next: Lesson 10.1 -- What Is Normalization?
This is Lesson 9.6 of the Database Interview Prep Course -- 12 chapters, 58 lessons.