Query Optimization and Indexing

Why Query Optimization Matters

When a table holds millions of rows, a poorly written or poorly supported query can take minutes instead of milliseconds. In production data pipelines, slow queries waste cloud compute dollars, delay dashboards, and frustrate stakeholders. Query optimization is the skill of reading what PostgreSQL actually does with a query and changing the table structure or the query itself so the database does less work.

The two main tools in this lesson are EXPLAIN ANALYZE and indexes.

Reading EXPLAIN ANALYZE Output

`EXPLAIN ANALYZE` runs the query and returns the execution plan PostgreSQL chose, along with actual timing measured during that run. The key syntax is:

```sql

EXPLAIN ANALYZE SELECT * FROM health_records WHERE county = 'Alameda';

```

The output is a tree of nodes. Each node represents one operation — a scan, a join, a sort, an aggregate. Read the tree from the innermost (most-indented) node outward; PostgreSQL executes inner nodes first.

For each node you will see:

• cost=start..total — the planner's estimate of work units (not seconds).
• actual time=start..end — real elapsed milliseconds for that node.
• rows — how many rows the node returned.
• loops — how many times the node executed.

The costliest node is usually the one with the highest `actual time` total or the one labeled Seq Scan on a large table. A sequential scan reads every page of the table regardless of how many rows match — fine for small tables, expensive for millions of rows.

Recognizing a Problem

Look for these warning signs in the plan:

• `Seq Scan` on a table with many rows and a low `rows` return count — the database read everything to find a few rows.
• A large gap between planner `rows` estimate and actual `rows` — stale statistics, solvable with `ANALYZE`.
• High `actual time` on a node that feeds a join — that bottleneck multiplies cost.

Indexes: What They Are and How They Help

An index is a separate data structure that maps column values to the physical locations of matching rows. Instead of reading every page, PostgreSQL jumps directly to the rows it needs.

B-tree Indexes

The default index type in PostgreSQL is the B-tree (balanced tree). It supports equality (`=`) and range comparisons (`<`, `>`, `BETWEEN`). Create one with:

```sql

CREATE INDEX idx_county ON health_records (county);

```

After creation, run `EXPLAIN ANALYZE` again. You should see the `Seq Scan` replaced by an Index Scan or Bitmap Index Scan, and the actual time should drop sharply.

Partial Indexes

A partial index covers only rows that satisfy a `WHERE` condition. If 95 % of queries filter on active records only, indexing just those rows keeps the index small and fast:

```sql

CREATE INDEX idx_active_county

ON health_records (county)

WHERE status = 'active';

```

Partial indexes are useful when a column has a skewed distribution — most values are one thing, but you always query for the rare value.

When Indexes Do Not Help

An index can be ignored by the planner when:

• Selectivity is low — if a query returns more than roughly 5–10 % of all rows, PostgreSQL may decide a sequential scan is faster because random page reads from an index can exceed the cost of a single sequential pass.
• The table is small — the overhead of an index lookup outweighs reading the whole table.
• The column is cast or wrapped in a function — `WHERE UPPER(county) = 'ALAMEDA'` cannot use an index on `county` unless a functional index exists.
• Statistics are stale — run `ANALYZE health_records;` to refresh the planner's row estimates.

Adding too many indexes also slows `INSERT`, `UPDATE`, and `DELETE` operations because each index must be maintained. Index only columns that appear in frequent, selective `WHERE` clauses or join conditions.

Reading the Comparison Table

Your lab notebook table records four columns for each query: query ID, plan node before, actual time before (ms), and actual time after (ms). Calculate the speedup ratio as before ÷ after. A ratio of 10× or greater is common when replacing a sequential scan on a million-row table with a B-tree index scan.

Data Accuracy and Integrity

Record the numbers you actually observe. Do not round aggressively or copy a classmate's timings — execution times vary by machine load, and your instructor will check that results are plausible for your hardware. Fabricating benchmark results is academic dishonesty and also a serious professional ethics violation; in industry, false performance claims can lead to bad infrastructure decisions and cost overruns.

Workplace Safety Note

This lesson runs entirely in software on a local or lab PostgreSQL instance loaded with open, de-identified public data. No personal health information (PHI) is used. When you work with real health data in your career, you must follow your organization's data governance policy, HIPAA requirements where applicable, and any data-use agreements. Never run untested bulk operations — including index builds — on production databases without a maintenance window and a verified backup. Large index builds can lock tables and interrupt live applications.