Indexes, EXPLAIN, and Query Performance

Why Query Performance Matters

As databases grow, poorly written queries can turn a fraction-of-a-second response into a multi-second delay. A 50,000-row table may seem small, but production systems routinely store millions of rows. Developing good habits now—reading query plans, creating targeted indexes, and knowing when not to index—will carry through your entire career as a developer or database administrator.

How PostgreSQL Executes a Query

When you submit a SELECT statement, PostgreSQL does not run it immediately. First, the query planner examines available statistics about the table (row counts, column value distributions) and generates a query plan: a tree of steps describing exactly how data will be retrieved and joined. The planner chooses the plan it estimates will cost the least work. Understanding that plan is the first step toward improving it.

Sequential Scans vs. Index Scans

A sequential scan (Seq Scan) reads every row in a table from beginning to end—like reading a book cover to cover to find one sentence. For small tables or queries that return most rows, this is fine. For large tables where only a few rows match, it is wasteful.

An index scan uses a pre-built data structure to jump directly to matching rows—like using a book's index to find the exact page. PostgreSQL supports several index types; the default and most broadly useful is the B-tree index, which handles equality checks (`=`), range queries (`<`, `>`, `BETWEEN`), and `ORDER BY` efficiently.

Creating a B-tree Index

The syntax is straightforward:

```sql

CREATE INDEX idx_orders_customer_id

ON orders (customer_id);

```

PostgreSQL will build the index immediately and maintain it automatically as rows are inserted, updated, or deleted. Name indexes descriptively so you can identify them later (`idx_tablename_columnname` is a common convention).

Reading EXPLAIN ANALYZE Output

`EXPLAIN` shows the planner's estimated plan without running the query. `EXPLAIN ANALYZE` actually executes the query and adds real timing and row-count data. Use `EXPLAIN ANALYZE` during development; avoid it on destructive statements in production unless you wrap them in a transaction you roll back.

A typical output node looks like this:

```

Seq Scan on orders (cost=0.00..1540.00 rows=50000 width=72)

(actual time=0.032..18.451 rows=50000 loops=1)

```

Key fields to annotate:

• cost=start..total — Planner's estimated work units. The first number is startup cost (before the first row is returned); the second is total cost. Lower is better, but the numbers are relative, not seconds.
• rows (estimated) — How many rows the planner expects to return.
• actual time=start..total — Real milliseconds. The second number is total elapsed time for this node.
• rows (actual) — Real rows returned. A large gap between estimated and actual rows signals stale statistics; run `ANALYZE tablename;` to refresh them.
• loops — How many times this node executed (relevant in nested loops).

After adding an index, a node may change from `Seq Scan` to `Index Scan` or `Bitmap Index Scan`, and the total cost and actual time should drop noticeably.

Identifying Bottleneck Columns

Look for these patterns in a query plan:

1. Seq Scan on a large table with a low `rows` estimate — the planner wants few rows but has no index to use.

2. High actual time on a single node — that node is doing most of the work.

3. Filter lines inside a Seq Scan — rows are being discarded after a full read, meaning the WHERE clause column has no index.

Columns that frequently appear in `WHERE`, `JOIN ON`, and `ORDER BY` clauses are prime index candidates.

When NOT to Index

Indexes are not free. Each index must be updated every time a row is inserted, updated, or deleted, adding overhead to write operations. Guidelines from PostgreSQL documentation and industry practice:

• Low-cardinality columns — A column like `gender` or `is_active` that holds only two or three distinct values gives the planner little help; a sequential scan may be faster because it avoids index overhead.
• Write-heavy tables — Tables that receive constant bulk inserts (log tables, event streams) pay a high maintenance cost for every index. Profile before indexing.
• Small tables — PostgreSQL's planner will often choose a sequential scan for tables with only a few hundred rows because the overhead of an index lookup exceeds the cost of reading the whole table.
• Rarely queried columns — An unused index wastes storage and slows writes without helping any query.

Recording Results: The Performance Log

A structured log makes your work reproducible and defensible. For each index you create, record: the index name, the query tested, the before cost (from EXPLAIN ANALYZE total), the after cost, and a written interpretation explaining why cost changed. This habit mirrors professional database tuning documentation.

Safety and Best Practices

PostgreSQL itself does not involve physical safety hazards, but responsible database work has its own critical rules:

• Never run EXPLAIN ANALYZE on a DELETE or UPDATE in production without wrapping it in `BEGIN; ... ROLLBACK;`—the statement executes for real.
• Back up data before adding indexes to production tables; though `CREATE INDEX` is non-destructive, always work safely.
• Use `CREATE INDEX CONCURRENTLY` on live production tables to avoid locking all reads and writes during a long index build (not needed in this lab environment, but essential knowledge for the field).
• Check index usage with `pg_stat_user_indexes` after deployment; drop indexes that are never used.