Advanced8 min read PostgreSQL

Indexes, Views & Materialized Views

What to index and why, when a view is enough, and when a materialized view is the right shape for a dashboard extract. The sequel to EXPLAIN & Query Performance.

CREATE INDEX
composite index
partial index
CREATE VIEW
MATERIALIZED VIEW
REFRESH
performance

Use this when you need to:

Turn a 4-second "students by term" lookup into a 4-millisecond one with the right composite index
Give the census-headcount query a name so five reports stop pasting the same 40 lines
Precompute the retention dashboard's numbers nightly instead of recalculating them on every open
Know which index is worth its write cost and which one is dead weight

The QueryU editor runs SELECT only, so the CREATE statements in this lesson will not execute here — read them, then use them on a database you own. Everything is written against the practice schema so it maps directly onto what you have been querying.

What an index is for

A B-tree index is a sorted copy of one or more columns with pointers back to the rows. It pays off when a query needs a small fraction of a large table — a filter on a selective column, a join key, or an ORDER BY that would otherwise sort everything. It costs disk space and slows every INSERT, UPDATE and DELETE on the table, because the copy has to be maintained. EXPLAIN & Query Performance showed you how to see the Seq Scan; this is how you remove it.

-- The enrollment table is queried by student, and by student-within-term
CREATE INDEX idx_scs_student_term
  ON ods_stu_course_sec (scs_student, scs_term);

-- Column order matters: this index serves
--   WHERE scs_student = '0100042'
--   WHERE scs_student = '0100042' AND scs_term = '2026FA'
-- but NOT
--   WHERE scs_term = '2026FA'            -- scs_term is not the leading column
-- If you also filter by term alone, add a second index led by scs_term.

Partial and covering indexes

-- Partial: only index the rows the reports actually ask for.
-- Smaller, faster to maintain, and the planner uses it whenever the
-- query's WHERE clause implies the index's WHERE clause.
CREATE INDEX idx_sttr_registered_by_term
  ON ods_student_terms (sttr_term)
  WHERE sttr_current_status = 'R';

-- Covering: INCLUDE carries extra columns so the query never touches the table
CREATE INDEX idx_terms_start
  ON ods_terms (term_start_date)
  INCLUDE (terms_id, term_desc);

-- Unique: doubles as a constraint. One aid row per student per aid year.
CREATE UNIQUE INDEX uq_fa_student_year
  ON ods_financial_aid (fa_student, fa_year);

Expression indexes make function-wrapped filters indexable: CREATE INDEX ON ods_person (LOWER(preferred_email_address)) lets WHERE LOWER(preferred_email_address) = '…' use the index. Without it, wrapping an indexed column in a function forces a sequential scan — one of the most common causes of a slow report.

Views — a saved query with a name

A view stores the query text, not the result. Selecting from it runs the query fresh, so it is always current, costs no storage, and inherits whatever indexes the underlying tables have. Use one when several reports share the same logic and you want a single place to fix it.

CREATE VIEW v_registered_by_term AS
SELECT
  t.terms_id,
  t.term_desc,
  t.term_start_date,
  COUNT(*) FILTER (WHERE st.sttr_current_status = 'R') AS registered
FROM ods_student_terms st
  JOIN ods_terms t ON t.terms_id = st.sttr_term
GROUP BY t.terms_id, t.term_desc, t.term_start_date;

-- Consumers no longer need to know about the status code or the date trap
SELECT terms_id, registered
FROM v_registered_by_term
ORDER BY term_start_date;

-- The view's query, runnable here as a plain SELECT
SELECT
  t.terms_id,
  t.term_desc,
  COUNT(*) FILTER (WHERE st.sttr_current_status = 'R') AS registered
FROM ods_student_terms st
  JOIN ods_terms t ON t.terms_id = st.sttr_term
GROUP BY t.terms_id, t.term_desc, t.term_start_date
ORDER BY t.term_start_date;

Materialized views — a stored result you refresh

A materialized view runs the query once and stores the rows. Reads are as fast as reading a table, and you can index the result. The trade is staleness: the data is as old as the last REFRESH. That is exactly the shape of a dashboard extract — heavy aggregate, many readers, refreshed on a schedule after the nightly load.

CREATE MATERIALIZED VIEW mv_census_snapshot AS
SELECT
  st.sttr_term,
  st.academic_level_desc                              AS level,
  COUNT(*)                                            AS headcount,
  COUNT(*) FILTER (WHERE st.sttr_student_load = 'F')  AS full_time
FROM ods_student_terms st
WHERE st.sttr_current_status = 'R'
GROUP BY st.sttr_term, st.academic_level_desc;

-- A unique index is REQUIRED for CONCURRENTLY, which lets readers keep
-- querying the old rows while the refresh builds the new ones.
CREATE UNIQUE INDEX uq_mv_census_snapshot
  ON mv_census_snapshot (sttr_term, level);

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_census_snapshot;

Without CONCURRENTLY, REFRESH takes an exclusive lock and every dashboard query blocks until it finishes. Schedule the refresh (pg_cron, or the same job that loads the warehouse) and record the refresh time in a column so the dashboard can print "as of".

Choosing between them

Index when a specific query is slow and the plan shows a scan you can remove. View when the problem is duplicated logic, not speed. Materialized view when the query is heavy, read far more often than the data changes, and a defined staleness is acceptable. If the numbers must be live to the second, a materialized view is the wrong tool — go back to indexes.

Practice challenge

The advising office runs "students by advisor" dozens of times a day against ods_advisor_assignments, always filtered to active assignments. Write the index that serves that query, then a view that joins advisor and student names from ods_person so the office never has to. (Design exercise — the sandbox is read-only, so check your answer by reading it back against the rules above.)

Solution

-- Partial index: the filter is always saa_is_active = 'Y', lookups are by advisor
CREATE INDEX idx_saa_active_by_advisor
  ON ods_advisor_assignments (saa_advisor)
  WHERE saa_is_active = 'Y';

CREATE VIEW v_active_advisees AS
SELECT
  aa.saa_advisor,
  adv.last_name || ', ' || adv.first_name AS advisor_name,
  aa.saa_student,
  stu.last_name || ', ' || stu.first_name AS student_name,
  aa.advisor_type_desc,
  aa.saa_start_date
FROM ods_advisor_assignments aa
  JOIN ods_person adv ON adv.id = aa.saa_advisor
  JOIN ods_person stu ON stu.id = aa.saa_student
WHERE aa.saa_is_active = 'Y';

Practise this on real university data

Write real PostgreSQL in your browser and get it graded on the rows it returns. No signup and nothing to install for your first query.

Run your first query Browse practice problems

See all 21 lessons