Project 3: Indexing and Query Optimization

   
Weight 10% of course grade
Released Monday, October 19, 2026
Due Friday, November 13, 2026 at 11:59 PM

Goal

Treat your database as a system that can be measured and tuned. Identify the slowest queries on your dataset. Form hypotheses about why they are slow. Apply remedies (indexes, restructuring, materialization). Measure before and after. Write a tuning report that a senior engineer would believe.

This is the most rigorous project before the Final Project. The storage, indexing, and query-optimization lectures inform every part of it.

What You Will Do

The whole project is a loop of measure, change, and measure again, and we give you the script that does the measuring. Read this section before anything else, since every later section fills in a step of it.

  1. Write 5 to 10 SELECT queries that matter for your dataset into bench/queries/, one statement per file, named b01-...sql through b10-...sql.
  2. Copy our two files, bench/benchmark.py and bench/environment.sql, into your repository unchanged.
  3. Run uv run --env-file .env bench/benchmark.py before. This is the baseline.
  4. Read the plans it wrote, decide what to change, and write those changes into schema-changes.sql with a matching schema-changes-undo.sql.
  5. Apply the changes with psql "$DATABASE_URL" -f schema-changes.sql.
  6. Run uv run --env-file .env bench/benchmark.py after.
  7. Write tuning.md, comparing the two runs, and update README.md with the recipe someone else follows to repeat steps 3 to 6.
  8. Tag the commit v3 and push.

benchmark.py does the same work in step 3 and step 6; the only difference is the word before or after, which picks the output directory. On each run it:

Everything under bench/before/ and bench/after/ is generated. Do not edit those files by hand; rerun the script instead. The report in tuning.md is where your own words go.

Who Runs Your Code

The staff and your peer reviewers run your repository, not just read it. They clone it, build a .env from your .env.example, load your data with your load.py into an empty PostgreSQL database, and run benchmark.py twice with schema-changes.sql in between. The staff run this on Linux (HiPerGator or the CISE server), and most peer reviewers will be on Linux or macOS. Prepare for a Linux environment:

Cap the load at about 10 minutes

A reviewer has to load your data before they can benchmark it, so the load must finish in a known, fixed time. Make your loader stop at a row cap, set the default cap so that a full load from a fresh database takes at most about 10 minutes on a laptop, and read the cap from an environment variable (for example MAX_ROWS) so you can raise it for your own experiments. State the default cap, the resulting table sizes, and the measured load time in README.md and in the environment section of tuning.md. A cap that is a parameter was the most common suggestion in the Project 1 feedback; now it is a requirement, because the benchmark numbers only mean something at a stated data size.

If the capped load is too small for an index to matter, see the FAQ on scaling a table up inside the database, which is far cheaper than downloading more.

Deliverables

cop5725fa26-project/
├── ... (from previous projects)
├── .env.example                 # must list DATABASE_URL with a placeholder value
├── bench/
│   ├── benchmark.py             # given to you; runs everything below
│   ├── environment.sql          # given to you; the server facts you report
│   ├── queries/                 # 5-10 queries to benchmark, one statement per file
│   │   ├── b01-lookup-<table>-by-<key>.sql
│   │   ├── b02-...
│   │   └── ...
│   ├── before/                  # generated by benchmark.py before tuning
│   │   ├── environment.txt
│   │   ├── timings.csv
│   │   ├── summary.csv
│   │   ├── b01-lookup-<table>-by-<key>.txt
│   │   └── ...
│   └── after/                   # generated by benchmark.py after tuning
│       ├── environment.txt
│       ├── timings.csv
│       ├── summary.csv
│       ├── b01-lookup-<table>-by-<key>.txt
│       └── ...
├── schema-changes.sql           # CREATE INDEX, ALTER TABLE, etc.
├── schema-changes-undo.sql      # drops everything schema-changes.sql created
├── tuning.md                    # narrative report
└── README.md                    # updated

.env and .env.example

The connection string lives in .env as DATABASE_URL, the same convention as Projects 0 through 2, and .env never enters git. .env.example is committed and lists every variable with a placeholder and a comment saying where the value comes from, so a reviewer can build a working .env in under a minute:

# Copy to .env and fill in real values. Never commit .env.
# DATABASE_URL: your PostgreSQL connection string (pg-info.sh prints it on HiPerGator)
DATABASE_URL=postgresql://user:password@host:5432/database
# MAX_ROWS: row cap for load.py; the default loads in about 10 minutes
MAX_ROWS=500000

Every command in this project loads the file the same way:

uv run --env-file .env bench/benchmark.py before

For psql commands, export the variables once per terminal session with set -a; source .env; set +a.

bench/queries/

5 to 10 representative queries from your project. These should be queries that matter for your dataset, the kind a user of your design scenario would run often. Cover this mix:

File names follow the pattern bNN-<pattern>-<table>-<detail>.sql, where NN is a two-digit number and the rest is a short lowercase slug. The pattern word is one of lookup, range, agg, or join.

b01-lookup-order-by-id.sql
b02-lookup-customer-by-email.sql
b03-range-events-by-week.sql
b04-range-orders-by-amount.sql
b05-agg-sales-by-region.sql
b06-agg-events-by-day.sql
b07-join-orders-customers.sql

The plan file for a query takes the query’s file name with .txt in place of .sql, in whichever phase directory the run wrote to. Keep the names fixed once you start measuring; renaming a query between the two runs breaks the comparison.

Write one SELECT statement per file, end it with a semicolon, and open the file with a header comment stating the question:

-- b03-range-events-by-week.sql
-- Question: How many events happened in each of the last 8 weeks?
-- Pattern: range scan on events.created_at

SELECT date_trunc('week', created_at) AS week, count(*)
FROM   events
WHERE  created_at BETWEEN now() - interval '8 weeks' AND now()
GROUP BY week
ORDER BY week;

benchmark.py wraps the statement in EXPLAIN ANALYZE, which executes it, so a query that writes rows would write them five times. Only SELECT statements belong here.

bench/benchmark.py

The script described in What You Will Do. Copy it into bench/benchmark.py unchanged; the grader diffs it against this copy.

uv run --env-file .env bench/benchmark.py before
set -a; source .env; set +a
psql "$DATABASE_URL" -f schema-changes.sql
uv run --env-file .env bench/benchmark.py after
bench/benchmark.py (click to expand) 
"""Run every query in bench/queries/ and save plans, timings, and server facts.

Usage:
    uv run --env-file .env bench/benchmark.py before
    uv run --env-file .env bench/benchmark.py after

Each run writes into bench/<phase>/:
    environment.txt      the server facts from environment.sql, plus the client OS
    <query stem>.txt     EXPLAIN (ANALYZE, BUFFERS, VERBOSE) output, one file per query
    timings.csv          planning and execution time for every run of every query
    summary.csv          cold time and warm median per query
"""

import argparse
import csv
import os
from pathlib import Path
import platform
import re
import statistics
import sys

from loguru import logger
import psycopg

BENCH_DIR = Path(__file__).parent
QUERY_DIR = BENCH_DIR / "queries"
ENVIRONMENT_SQL = BENCH_DIR / "environment.sql"
RUNS = 5
TIME_PATTERN = re.compile(r"^(Planning|Execution) time: ([0-9.]+) ms$", re.IGNORECASE)


def read_query(path: Path) -> str:
    """Return the single SELECT statement in a query file, without its trailing semicolon.

    Args:
        path: Path to the .sql file.
    """
    return path.read_text().strip().rstrip(";")


def explain_analyze(cursor: psycopg.Cursor, sql: str) -> tuple[str, float, float]:
    """Run one query under EXPLAIN ANALYZE and return the plan and its two timings.

    Args:
        cursor: An open psycopg cursor.
        sql: The query text, one statement, no trailing semicolon.

    Returns:
        The plan text, the planning time in ms, and the execution time in ms.
    """
    cursor.execute(f"EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT) {sql}")
    lines = [row[0] for row in cursor.fetchall()]
    times = {}
    for line in lines:
        match = TIME_PATTERN.match(line)
        if match:
            times[match.group(1).capitalize()] = float(match.group(2))
    return "\n".join(lines), times.get("Planning", 0.0), times.get("Execution", 0.0)


def write_environment(cursor: psycopg.Cursor, out_dir: Path) -> None:
    """Save the server facts from environment.sql and the client platform.

    Args:
        cursor: An open psycopg cursor.
        out_dir: The phase directory to write environment.txt into.
    """
    cursor.execute(ENVIRONMENT_SQL.read_text())
    rows = cursor.fetchall()
    width = max(len(name) for name, _ in rows)
    lines = [f"{'client_os':<{width}}  {platform.platform()}",
             f"{'client_python':<{width}}  {platform.python_version()}"]
    lines += [f"{name:<{width}}  {value}" for name, value in rows]
    (out_dir / "environment.txt").write_text("\n".join(lines) + "\n")


def benchmark_query(cursor: psycopg.Cursor, path: Path, out_dir: Path, runs: int) -> list[dict]:
    """Run one query several times, save its warm plan, and return one row per run.

    Args:
        cursor: An open psycopg cursor.
        path: Path to the .sql file.
        out_dir: The phase directory to write the plan into.
        runs: How many times to run the query.
    """
    sql = read_query(path)
    rows = []
    plan = ""
    for run in range(1, runs + 1):
        plan, planning_ms, execution_ms = explain_analyze(cursor, sql)
        rows.append({"query": path.stem, "run": run,
                     "planning_ms": planning_ms, "execution_ms": execution_ms})
        logger.info(f"{path.stem} run {run}: {execution_ms:.2f} ms")
    (out_dir / f"{path.stem}.txt").write_text(plan + "\n")
    return rows


def main() -> None:
    """Benchmark every query file and write the phase directory."""
    parser = argparse.ArgumentParser(description=__doc__.splitlines()[0])
    parser.add_argument("phase", choices=["before", "after"],
                        help="before or after applying schema-changes.sql")
    parser.add_argument("--runs", type=int, default=RUNS,
                        help=f"runs per query (default {RUNS})")
    args = parser.parse_args()

    database_url = os.environ.get("DATABASE_URL")
    if not database_url:
        logger.error("DATABASE_URL is not set. Run with: uv run --env-file .env bench/benchmark.py before")
        sys.exit(1)

    query_files = sorted(QUERY_DIR.glob("b*.sql"))
    if not query_files:
        logger.error(f"No b*.sql files found in {QUERY_DIR}")
        sys.exit(1)

    out_dir = BENCH_DIR / args.phase
    out_dir.mkdir(exist_ok=True)
    logger.info(f"Phase '{args.phase}': {len(query_files)} queries, {args.runs} runs each, output in {out_dir}")

    timings = []
    with psycopg.connect(database_url, autocommit=True, client_encoding="utf8") as conn:
        with conn.cursor() as cursor:
            write_environment(cursor, out_dir)
            for path in query_files:
                timings += benchmark_query(cursor, path, out_dir, args.runs)

    with (out_dir / "timings.csv").open("w", newline="") as f:
        writer = csv.DictWriter(f, fieldnames=["query", "run", "planning_ms", "execution_ms"])
        writer.writeheader()
        writer.writerows(timings)

    with (out_dir / "summary.csv").open("w", newline="") as f:
        writer = csv.writer(f)
        writer.writerow(["query", "runs", "cold_execution_ms", "warm_median_execution_ms"])
        for path in query_files:
            runs = [r["execution_ms"] for r in timings if r["query"] == path.stem]
            warm = runs[1:] or runs
            writer.writerow([path.stem, len(runs), f"{runs[0]:.2f}", f"{statistics.median(warm):.2f}"])
            logger.info(f"{path.stem}: cold {runs[0]:.2f} ms, warm median {statistics.median(warm):.2f} ms")

    logger.info(f"Wrote {out_dir / 'environment.txt'}, {len(query_files)} plan files, timings.csv, summary.csv")


if __name__ == "__main__":
    main()

psycopg and loguru are already dependencies from Projects 1 and 2. If uv run complains that one is missing, add it once:

uv add 'psycopg[binary]' loguru

The script sets client_encoding to UTF-8 so it behaves the same on a server created with SQL_ASCII encoding, which is the case on the CISE server. Change --runs only if 5 runs of a query take longer than a minute or two, and say so in the report.

bench/environment.sql and the test environment

A tuning number means nothing without the machine it came from. Your report states where the benchmark ran, and environment.sql collects the server side of that in one query. benchmark.py runs it for you and saves the result as environment.txt in each phase directory; you can also run it by hand with psql "$DATABASE_URL" -f bench/environment.sql.

bench/environment.sql (click to expand) 
-- environment.sql
-- Reports the server facts every tuning report must state.
-- benchmark.py runs this file and saves the output as environment.txt;
-- you can also run it by hand: psql "$DATABASE_URL" -f bench/environment.sql

SELECT name, value
FROM (
  SELECT 0 AS ord, 'postgresql_version'::text AS name, version()::text AS value
  UNION ALL
  SELECT 1, 'database', current_database()
  UNION ALL
  SELECT 2, 'database_size', pg_size_pretty(pg_database_size(current_database()))
  UNION ALL
  SELECT 3, 'server_started', pg_postmaster_start_time()::text
  UNION ALL
  SELECT 4, name::text, current_setting(name)
  FROM pg_settings
  WHERE name IN ('server_version', 'shared_buffers', 'work_mem',
                 'maintenance_work_mem', 'effective_cache_size',
                 'random_page_cost', 'seq_page_cost', 'effective_io_concurrency',
                 'max_parallel_workers_per_gather', 'max_worker_processes',
                 'jit', 'enable_seqscan', 'enable_indexscan', 'enable_bitmapscan',
                 'default_statistics_target', 'autovacuum', 'track_io_timing',
                 'max_connections', 'huge_pages')
  UNION ALL
  SELECT 5, 'table:' || relname,
         n_live_tup::text || ' rows, ' || pg_size_pretty(pg_total_relation_size(relid))
         || ', last analyze ' || coalesce(greatest(last_analyze, last_autoanalyze)::text, 'never')
  FROM pg_stat_user_tables
  UNION ALL
  SELECT 6, 'index:' || indexrelname,
         relname || ', ' || pg_size_pretty(pg_relation_size(indexrelid))
  FROM pg_stat_user_indexes
) facts
ORDER BY ord, name;

The values it reports, and the ones your tuning.md must state:

Value Why it matters
postgresql_version The full version() string: major version, build platform, and architecture. The planner changes between major versions.
database_size and one table: row per table Row counts and on-disk size per table. A 10,000-row table fits in cache and tells you nothing about indexes.
one index: row per index The indexes present when the run happened. The after list should be the before list plus the ones in schema-changes.sql.
shared_buffers, effective_cache_size The planner’s model of how much data sits in memory. Both change the cost of a sequential scan relative to an index scan.
work_mem The memory available to each sort or hash. Below it, sorts spill to disk and hash joins batch.
random_page_cost, seq_page_cost The cost model for index access versus sequential access. The default of 4 assumes spinning disks.
max_parallel_workers_per_gather, jit Whether parallel plans and JIT compilation were available. A Gather node in one plan and not the other usually traces back here.
default_statistics_target, autovacuum, last analyze per table Whether the planner’s statistics were fresh. A stale ANALYZE explains more bad plans than any index does.
track_io_timing Whether BUFFERS output includes I/O times. Off by default; superusers can turn it on.
server_started Confirms the server was not restarted between the two runs, which would reset the buffer cache.
client_os, client_python The operating system and Python version of the machine that ran benchmark.py.

The query cannot see the hardware or where the server lives, so you write those in the report yourself. Say whether PostgreSQL runs on your laptop, the CISE server, or HiPerGator, and whether that is a different machine from the one running benchmark.py. When the server is your own machine, give the CPU model, core count, RAM, and whether the data directory sits on an SSD. On a shared server, name the server and note that the hardware is shared.

Run before and after on the same server with the same settings. The pair of environment.txt files should differ only in the index: rows and the last analyze timestamps. If anything else differs, explain it in the report.

schema-changes.sql and schema-changes-undo.sql

Every change you made: CREATE INDEX, ALTER TABLE, CLUSTER, CREATE MATERIALIZED VIEW, and so on, with a comment on each statement naming the query it accelerates.

-- Speeds up b03-range-events-by-week (range scan on created_at)
CREATE INDEX IF NOT EXISTS idx_events_created_at_brin
  ON events USING BRIN (created_at);

-- Speeds up b02-lookup-customer-by-email (expression index on lower(email))
CREATE INDEX IF NOT EXISTS idx_customers_email_lower
  ON customers (lower(email));

-- Speeds up b04-range-orders-by-amount for the pending subset only
CREATE INDEX IF NOT EXISTS idx_orders_pending_created_at
  ON orders (created_at)
  WHERE status = 'pending';

ANALYZE;

Use IF NOT EXISTS so the file runs cleanly twice, and end with ANALYZE so the planner sees the new indexes with fresh statistics.

schema-changes-undo.sql reverses the file, one DROP ... IF EXISTS per object, so a reviewer can return to the before state without reloading your data.

DROP INDEX IF EXISTS idx_events_created_at_brin;
DROP INDEX IF EXISTS idx_customers_email_lower;
DROP INDEX IF EXISTS idx_orders_pending_created_at;

Keep a failed experiment out of schema-changes.sql and describe it in the report instead. The file should produce the after state and nothing else.

Advice from Projects 1 and 2

These are the problems the staff saw most often while running the Project 1 submissions, and the ones that will hurt more here because a reviewer runs your whole pipeline before the benchmark can start.

PostgreSQL Documentation

Anchor every claim in the report on the PostgreSQL documentation rather than on a blog post. These pages cover everything this project asks for.

Reading plans:

Indexes:

Other interventions:

Settings that appear in environment.txt:

tuning.md

The narrative report, 2 to 4 pages. Required sections:

1. Test Environment

Where the benchmark ran, in a short table built from bench/before/environment.txt and the hardware facts listed above. State the server location, the PostgreSQL version, the row cap and the table sizes it produced, and the planner settings from the table in the environment section.

2. Workload Summary

What queries does your dataset’s use case run? Why these and not others?

3. Initial Measurements

For each benchmark query, the baseline from bench/before/:

4. Hypotheses and Interventions

For each slow query:

Be honest about failed experiments. A change that made things slower is still data.

5. Final Measurements

The same table as Section 3, built from bench/after/, side by side with the baseline.

6. What You Would Do Differently

If you were running this on production data 10× the size, what additional tools would you reach for? (Partitioning, replication, materialized views, and so on.) One paragraph.

Updated README.md

Add a “Project 3 Tuning” section:

cp .env.example .env            # then edit DATABASE_URL
uv sync
uv run --env-file .env load.py  # Project 1 loader, into an empty database
uv run --env-file .env bench/benchmark.py before
set -a; source .env; set +a
psql "$DATABASE_URL" -f schema-changes.sql
uv run --env-file .env bench/benchmark.py after

A reviewer who follows the recipe on a fresh database should end up with their own bench/before/ and bench/after/ to compare against yours.

Submission

Tag the commit you want graded v3 and push it with main by November 13.

git tag v3
git push origin main v3

We grade the commit the v3 tag points at, not the tip of main, so anything you push after tagging goes unread. To replace a tag before the deadline, delete it on both sides and tag again:

git tag -d v3
git push origin :refs/tags/v3
git tag v3
git push origin main v3

Grading Rubric

100 points total. The staff grade the v3 tag by running the reproduction recipe from your README on their own Linux server, then reading tuning.md against the plans in bench/before/ and bench/after/.

Component Points Criteria
Workload selection 10 Queries reflect the dataset’s use case; the required mix of patterns; file names follow the convention
Baseline measurements 15 bench/before/ complete and generated by benchmark.py; environment and row cap stated in the report
Tuning interventions 25 Index and restructuring choices fit the plans; more than one index type tried; schema-changes.sql and its undo both run cleanly
Post-tuning measurements 15 bench/after/ complete; clear improvement, or an honest no-change; same server and settings as before
tuning.md narrative 25 Hypotheses stated; interventions explained; before and after compared with numbers from summary.csv
Repo hygiene 10 Tag v3; benchmark.py and environment.sql unchanged; .env.example lists every variable; the capped load finishes in about 10 minutes; no .env in the repository

Peer-Grading Subset

Peer reviewers run your reproduction recipe and grade only:

  1. Tuning narrative clarity (0-5): does the report tell a coherent story?
  2. Plan-reading skill (0-5): are the EXPLAIN outputs interpreted correctly?
  3. Honesty (0-5): does the student admit failed experiments and surprising results?

Peer scores are normalized and contribute 30%; instructor and TA scores contribute 70%. There are no in-class presentations for Project 3; peer reviews are assigned and collected through Canvas after the deadline.

Common Pitfalls

FAQ

Q: Can I use DuckDB for Project 3? A: PostgreSQL is required for the indexing portion (DuckDB has limited explicit-index support). You may use DuckDB for some queries to compare engines, in a separate file outside bench/queries/.

Q: My capped dataset is too small to show meaningful tuning effects. A: Document that, then scale a table up inside the database rather than raising the download cap. INSERT INTO events SELECT ... FROM events CROSS JOIN generate_series(1, 100) multiplies a table in seconds. Put the statement in load.py behind an environment variable so a reviewer can reproduce it, and keep the whole load inside the 10-minute budget.

Q: My query is already fast. Should I still tune it? A: Find a slower one. The point of Project 3 is the tuning process, not chasing milliseconds on already-fast queries.

Q: Do I have to use partial, expression, or BRIN indexes? A: No. Use whatever fits. The rubric rewards demonstrating more than one index type where each fits.

Q: Can I run the benchmark on the CISE server or HiPerGator? A: Yes. Both are shared machines, so timings will be noisier than on a laptop. Say which one you used in the Test Environment section and run before and after close together in time.

Q: I develop on Windows. What do I need to change? A: Nothing in the commands, if you run them inside WSL with a Linux PostgreSQL or a remote server. Check line endings and the byte order mark on any file you created in PowerShell, and do a fresh clone inside WSL before you tag.

back