Indexing Strategy and Query Plan Analysis with EXPLAIN ANALYZE

Indexing Strategy and Query Plan Analysis with EXPLAIN ANALYZE

Why Indexing Decisions Matter

A PostgreSQL database serving hundreds of thousands of rows can respond in milliseconds or drag on for seconds — often the only difference is whether the right index exists. As a database administrator, choosing the correct index type for each query pattern is one of your highest-impact daily responsibilities. Poor indexing causes slow applications; over-indexing wastes disk space and slows write operations because every INSERT, UPDATE, and DELETE must maintain every index on the table. Your goal is a lean, purposeful set of indexes backed by evidence from query plans.

Reading an EXPLAIN ANALYZE Plan

The `EXPLAIN ANALYZE` command actually executes the query and returns a plan annotated with real timing and row counts alongside the planner's estimates. The output is a tree of nodes; execution flows from the innermost (leaf) node upward to the outermost (root) node.

Key fields to locate on every node:

• cost=startup..total — planner's estimated cost in arbitrary units; startup is cost before the first row is returned, total is cost for all rows.
• rows — planner's estimated row count for that node.
• actual time=startup..total — wall-clock milliseconds measured during execution.
• actual rows — rows the node actually produced; compare to estimated rows to spot poor statistics.
• Buffers: shared hit / shared read — cache hits (fast) vs. disk reads (slow); appears when you run `EXPLAIN (ANALYZE, BUFFERS)`.
• Planning Time / Execution Time — summary lines at the bottom; record both when benchmarking.

A Seq Scan reads every page of the table. On a 500,000-row table this is expected and sometimes correct for queries returning a large fraction of rows, but it is a red flag for selective point lookups. An Index Scan or Index Only Scan navigates the index B-tree first and fetches only matching heap pages. A Bitmap Heap Scan combines results from a Bitmap Index Scan and is common for range queries.

Matching Index Types to Query Patterns

PostgreSQL ships with several index access methods. Choosing the right one is not optional — the wrong type simply will not be used by the planner.

| Query Pattern | Recommended Index Type | Notes |

|---|---|---|

| Equality on high-cardinality column | B-tree | Default; also supports range, ORDER BY |

| Equality on low-cardinality column | Partial B-tree or reconsider | Full B-tree rarely helps; planner may prefer Seq Scan |

| Full-text search (`@@` operator) | GIN on `tsvector` | GIN stores posting lists for fast keyword lookup |

| Range scan on a date/timestamp column with mostly sequential inserts | BRIN | Very small; effective when physical order correlates with column value |

| JSONB containment (`@>` operator) | GIN on the JSONB column | GIN indexes JSONB key-value pairs |

| Geometric / nearest-neighbor queries | GiST | Not covered in this lesson but good to know |

A B-tree index is the default when you write `CREATE INDEX` with no method clause. Specify others explicitly: `CREATE INDEX idx_name ON table USING gin(column);`

Before You Index: Run ANALYZE

The planner's cost estimates depend on table statistics stored in `pg_statistic`. If statistics are stale — for example, after a large bulk load — the planner may misjudge row counts and choose a bad plan regardless of what indexes exist. Always run `ANALYZE table_name;` before benchmarking or making indexing decisions. In production, `autovacuum` handles this automatically, but during a hands-on session with freshly loaded data you must run it manually.

This is a data-safety practice, not optional: decisions made on stale statistics can lead to index drops that hurt production performance.

Identifying and Removing Redundant Indexes

Every index on a table adds overhead to writes. PostgreSQL tracks index usage in the `pg_stat_user_indexes` view. The column `idx_scan` counts how many times the planner has chosen that index since statistics were last reset. An `idx_scan` value of zero after a representative workload period is a strong signal the index is unused.

```sql

SELECT schemaname, relname, indexrelname, idx_scan

FROM pg_stat_user_indexes

WHERE idx_scan = 0

ORDER BY relname;

```

Before dropping any index, document your reasoning:

1. Confirm statistics are current (`ANALYZE`).

2. Check whether the index is used during off-peak batch jobs not yet run.

3. Consider creating the index as `CONCURRENTLY` if you need to restore it on a live system.

4. Drop only after written justification is approved.

Drop syntax: `DROP INDEX CONCURRENTLY index_name;` — using `CONCURRENTLY` avoids locking the table in production.

Practical Safety Notes

• Never run destructive DDL (DROP INDEX, DROP TABLE) on a production system during peak hours without a maintenance window and supervisor sign-off. In a vocational lab this means: work only on your assigned VM, never on shared class servers without instructor permission.
• Back up or snapshot your VM before the drop exercise so you can restore the schema if needed.
• `EXPLAIN ANALYZE` executes the query. On a write query (`UPDATE`, `DELETE`) this means data actually changes. Wrap write queries in `BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;` to measure without committing.

Workflow Summary

1. Load data and run `ANALYZE`.

2. Run `EXPLAIN (ANALYZE, BUFFERS)` on the target query; record planning time and execution time.

3. Create the appropriate index type.

4. Run `EXPLAIN (ANALYZE, BUFFERS)` again on the same query.

5. Record the improvement in your index performance table.

6. Repeat for each scenario.

7. Use `pg_stat_user_indexes` to find zero-scan indexes, document justification, then drop with `CONCURRENTLY`.