Views and Indexes

CS 374, Fall 2026

Lessons Learned: Why Is plans Slow?

load.py into PostgreSQL on sec1, over a wired campus connection:

core catalog requirements plans advising preferences scheduling
statements 134 1,495 43 27,041 1,103 86 22
time 4.0 s 3.1 s 0.2 s 25.6 s 1.0 s 2.5 s 0.6 s
  • Every statement is one round trip to the server: about 0.9 ms each here
  • On Wi-Fi or a tunnel, 5--20 ms each: the same load takes 2--10 minutes
  • The check uses SQLite, which has no network: 4 s, so nobody noticed

One Flush per Row

flush() sends pending rows now; otherwise SQLAlchemy batches a table's pending rows into one multi-row INSERT.
plans/load.py flushes after each of 17,666 entries, to get its id:

             session.add(entry)
-            session.flush()                                 # line 179
                     PlanEntryGrade(
-                        plan_entry_id=entry.plan_entry_id,  # line 202
+                        plan_entry=entry,
  • Deleting only the flush fails (NOT NULL constraint failed)
  • With the relationship, SQLAlchemy inserts the entries in batches, then fills in each foreign key: same rows, but 1,452 statements in 3.4 s
  • Smaller cases: a flush() per cross-listing, a SELECT per student. catalog, advising
  • Today: make both edits in your local copy, run load.py, then git restore

More Lessons Learned from GP3

Same seed, different data

  • random.choices(...) uses Python's global generator, not your seeded rng, so the data changes on every load. advising
  • select(...) with no order_by, then rng draws while looping over the rows: a table has no order, so PostgreSQL may give each row a different draw than SQLite. requirements, plans, advising, preferences
terms = session.exec(select(Term).order_by(Term.term_code)).all()
pick = rng.choice(terms)

Match on a key, not a name

  • Matching instructors by last name gave both Wangs each other's teaching history. A person's email is unique; a name is not. preferences

Let the Database Say No

  • Contradictions: 126 recommendations whose status disagrees with responded_at; 630 attempts at one course in one term with two grades. A CheckConstraint or UniqueConstraint rejects them. advising, plans
  • Documented is not enforced: a foreign key in schema.dbml but not models.py is checked by nothing. catalog, requirements, plans, preferences
  • Fix the constraint, not the data: 108 two-room meetings dropped to fit a unique index; cross-listings dropped whole for one missing member. scheduling, catalog
  • Text is not a number: CAST('499A' AS integer) is 499 in SQLite and an error in PostgreSQL. requirements
Every one of these passed the check. A constraint is a test that runs on every row, forever.

Introducing the air Database

The air Database

Airports, flights, bookings, and passengers, from postgres_air.

table rows
boarding_pass 25 million
booking_leg 18 million
passenger 16 million
booking 5.6 million
flight 683,000
airport 692
  • A booking has legs (one flight each), and passengers
  • At this size, a careless query takes seconds, not milliseconds

Example Query

Average price and number of passengers on each flight:

SELECT bl.flight_id, f.departure_airport,
  avg(b.price)::numeric(7,2) AS avg_price,
  count(DISTINCT p.passenger_id) AS num_passengers
FROM booking AS b
  JOIN booking_leg AS bl USING (booking_id)
  JOIN flight AS f USING (flight_id)
  JOIN passenger AS p USING (booking_id)
GROUP BY bl.flight_id, f.departure_airport;
  • Four tables, and every report about flights starts with it
  • Copying it into every query means fixing every copy when it changes

A View Is a Named Query

CREATE VIEW flight_stats AS
SELECT bl.flight_id, f.departure_airport, ...    -- the query above
GROUP BY bl.flight_id, f.departure_airport;

Then select from it as if it were a table:

SELECT * FROM flight_stats WHERE flight_id = 4022;
flight_id departure_airport avg_price num_passengers
4022 SEA 1139.21 170
  • The database stores the query, not the rows
  • Every SELECT from the view runs the query again, on today's data

Why Use a View?

  • Name a question: flight_stats says what the four joins mean
  • Write it once: fix the view, and every query that uses it is fixed
  • Hide the details: readers don't need to know which tables answer it
  • Control access: GRANT SELECT on a view, not on the tables under it

In GP4, your views are how other teams read your data:
they select from advising_quiet_student without knowing how you built it.

Views Have No Parameters

There is no "flight stats for a given airport." Instead, keep the column, and filter outside:

SELECT * FROM flight_stats WHERE departure_airport = 'SEA';

PostgreSQL pushes that WHERE down into the view's query, so it does not compute every flight first.

Portable view scripts (SQLite and PostgreSQL):

DROP VIEW IF EXISTS flight_stats;
CREATE VIEW flight_stats AS ...;
  • CREATE OR REPLACE VIEW exists only in PostgreSQL

A Materialized View Stores the Rows

CREATE MATERIALIZED VIEW flight_departure AS
SELECT bl.flight_id, f.departure_airport,
  coalesce(f.actual_departure, f.scheduled_departure)::date AS departure_date,
  count(DISTINCT p.passenger_id) AS num_passengers
FROM booking AS b JOIN booking_leg AS bl USING (booking_id) ...
GROUP BY ...;

CREATE INDEX flight_departure_dep_airport ON flight_departure (departure_airport);
  • Runs the query once and saves the result, like a table
  • Can have its own indexes (a plain view cannot)
  • Goes stale: new bookings do not appear until
REFRESH MATERIALIZED VIEW flight_departure;

How Much Faster?

Both on air, for every flight leaving Seattle:

SELECT count(*), sum(num_passengers) FROM flight_stats     WHERE departure_airport = 'SEA';
SELECT count(*), sum(num_passengers) FROM flight_departure WHERE departure_airport = 'SEA';
source flights time
flight_stats (view) 5,940 10.7 s
flight_departure (materialized, indexed) 5,940 7 ms
  • The view joins 740,000 passenger rows every time it is asked
  • The materialized view reads 5,940 rows it already has

Table, View, Materialized View

table view materialized view
Stores rows a query a query and its rows
Up to date? it is the data always as of the last refresh
Cost of a SELECT read the rows run the query read the rows
Can be indexed yes no yes
INSERT into it yes rarely no
SQLite yes yes no

Materialize when: the query is slow, the data changes rarely, a stale answer is acceptable, and someone owns the refresh.

EXPLAIN and EXPLAIN ANALYZE

Asking PostgreSQL How It Ran

EXPLAIN ANALYZE SELECT * FROM flight WHERE flight_no = '2112';
Gather  (cost=1000.00..13138.32 rows=121 width=71) (actual time=1.149..32.152 rows=182.00 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=3663 read=4905
  ->  Parallel Seq Scan on flight  (cost=0.00..12126.22 rows=50 width=71) (actual time=0.548..24.886 rows=60.67 loops=3)
        Filter: (flight_no = '2112'::text)
        Rows Removed by Filter: 227665
Planning Time: 0.570 ms
Execution Time: 32.226 ms
  • EXPLAIN shows the plan, with estimated cost and rows; in pgAdmin, Explain (F7) and Explain Analyze (Shift+F7) draw it as a diagram
  • EXPLAIN ANALYZE also runs it, and adds the actual times and rows
  • Buffers counts 8 kB pages read; PostgreSQL 18 shows it by default
  • Seq Scan reads every row: here, all 683,000 flights, to find 182

The Same Table, With an Index

flight has an index on departure_airport, and none on flight_no:

EXPLAIN ANALYZE SELECT * FROM flight WHERE departure_airport = 'SEA';
Bitmap Heap Scan on flight  (cost=91.08..8969.54 rows=8084 width=71) (actual time=0.882..5.405 rows=7852.00 loops=1)
  Recheck Cond: (departure_airport = 'SEA'::bpchar)
  ->  Bitmap Index Scan on flight_departure_airport  (cost=0.00..89.05 rows=8084 width=0) (actual time=0.512..0.513 rows=7852.00 loops=1)
        Index Cond: (departure_airport = 'SEA'::bpchar)
Execution Time: 5.758 ms
  • Index Scan / Bitmap Index Scan: look up the matching rows in the index, then fetch only those
  • Times vary: first run reads from disk, later runs from memory. Run it twice.
  • air already has 40 indexes, so there is no "before" to see. Your own schema has only two.

What an Index Is

  • A sorted copy of one or more columns, each entry pointing at its row
  • Like the index of a book: find "SEA" without reading every page
  • PostgreSQL's default is a B-tree: balanced, so a lookup takes a few steps even for millions of rows, and it handles =, <, >, BETWEEN, and ORDER BY
CREATE INDEX ix_activity_status ON activity_event (status_code);
DROP INDEX ix_activity_status;

Automatic indexes: every PRIMARY KEY and UNIQUE constraint.
Not automatic: foreign keys. PostgreSQL does not index booking_leg.flight_id for you; someone had to.

Try It: activity_event

In pgAdmin, on sec1, in your own schema:

EXPLAIN ANALYZE
SELECT occurred_at, path, person_id
FROM activity_event
WHERE status_code = 500;

CREATE INDEX ix_activity_status ON activity_event (status_code);
ANALYZE activity_event;

-- now run the same EXPLAIN ANALYZE again
  • Which kind of scan, before and after? How long? Then try status_code = 200.
  • ANALYZE activity_event refreshes the planner's row estimates. Name the table: a bare ANALYZE tries every table in sec1.

What We Measured

On 45,274 rows of activity_event:

query rows no index with index
status_code = 500 152 Seq Scan 3.5 ms Bitmap Index Scan 0.1 ms
status_code = 200 35,219 Seq Scan 4.8 ms Seq Scan 4.8 ms
  • An index pays off if the query keeps a small fraction of the rows (selectivity)
  • For 78% of the table, reading it in order is cheaper than jumping through an index, and the planner knows it
  • Both are fast at this size. The habit matters because your tables will not stay this small.

Column Order in a Composite Index

The core already has one: ix_activity_person_time on (person_id, occurred_at).

-- uses the index: Bitmap Index Scan, 0.4 ms
SELECT * FROM activity_event WHERE person_id = 128 ORDER BY occurred_at;

-- does not: Seq Scan, 2.1 ms
SELECT * FROM activity_event WHERE occurred_at >= '2026-10-23';
  • Sorted by person_id first, then by time within each person
  • Like a phone book sorted by last name, first name: no help finding every "Pat"
  • Put the column you filter on with = first

What an Index Costs

  • Space: ix_activity_status is 328 kB, on a 6.8 MB table
  • Writes: every INSERT, UPDATE of an indexed column, and DELETE updates every index on the table
  • Choices: the planner considers every index for every query

Index a column when: queries filter, join, or sort on it, they keep a small fraction of the rows, and the table is read far more than it is written.

Don't index: a column with a handful of values spread evenly, a tiny table, or a column no query uses.

Indexes in SQLModel

One column, in the Field:

status_code: int = Field(index=True)    # creates ix_<table>_status_code

Several columns, in __table_args__, as the core does:

class ActivityEvent(SQLModel, table=True):
    __tablename__ = "activity_event"
    __table_args__ = (Index("ix_activity_person_time", "person_id", "occurred_at"),)
  • create_all() builds them, in SQLite and PostgreSQL
  • Index your own tables in GP4, never the core's
  • Say so in schema.dbml: indexes { (person_id, occurred_at) }

GP4: Performance

For your team's slowest view:

  1. Load into your own schema on sec1, and run EXPLAIN ANALYZE SELECT * FROM <your view> ...
  2. Find the Seq Scan doing the most work. Would an index on your tables help?
  3. If so, add it in models.py, reload, and measure again
  4. Decide whether the view should be materialized: how often does its data change, how stale may an answer be, who refreshes it?

Write the times before and after, and your decision, in a "Performance" section of your README. Materialized views stay out of queries.sql, since SQLite has none.

GP4 Team Time

Load your schema on sec1, fix your GP3 data, and start your views