Nov 03: Views and Indexes
Learning Objectives
After today's class, you should be able to:
- Explain the differences between a table, a view, and a materialized view.
- Create indexes and analyze their effect on query performance.
Lesson Outline
Project Database [10 min]
- Lessons learned from GP3
- Load the project database into your own schema on
sec1:- Apply the temporary fix to
src/planner/plans/load.py DATABASE_URL=postgresql+psycopg://your_username@data.cs.jmu.edu/sec1 python scripts/load.py --drop- Discard the temporary fix:
git restore src/planner/plans/load.py
- Apply the temporary fix to
GP4 Overview [10 min]
- GP4 is out, due Monday: one view and one SQLModel query per person
- Changes needed by Thursday:
- Wed 11:59 pm:
requirementsopens a pull request with only thestudent_programandprogram.codefixes - Wed 11:59 pm:
plansopens a pull request with only thesession.flush()fix (that we all applied and discarded) - Thu before class: I'll merge both, plus a core update (views in the build,
TODAY,CURRENT_TERM; seecore/gp4branch) - Thu during class: every team merges
maininto itsgp4branch – we will do this together, so please wait until Thursday
- Wed 11:59 pm:
- Requirements team: ignore "take everyone out of the Robotics minor" in the GP3 feedback. Robotics students can stay, as long as each is also a CS or IT major.
Views and Materialized Views [40 min]
- Views and Indexes
CREATE VIEWandCREATE MATERIALIZED VIEWon theairdatabaseEXPLAIN ANALYZE, sequential vs. index scans, and what an index costs
- air_demo.sql and the air diagram, for following along in pgAdmin
- Try it: create an index on
activity_eventin your own schema
GP4 team time [15 min]
- Fix your GP3 data, and decide who writes which view