Skip to content

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:
    1. Apply the temporary fix to src/planner/plans/load.py
    2. DATABASE_URL=postgresql+psycopg://your_username@data.cs.jmu.edu/sec1 python scripts/load.py --drop
    3. Discard the temporary fix: git restore src/planner/plans/load.py

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: requirements opens a pull request with only the student_program and program.code fixes
    • Wed 11:59 pm: plans opens a pull request with only the session.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; see core/gp4 branch)
    • Thu during class: every team merges main into its gp4 branch – we will do this together, so please wait until Thursday
  • 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 VIEW and CREATE MATERIALIZED VIEW on the air database
    • EXPLAIN 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_event in your own schema

GP4 team time [15 min]

  • Fix your GP3 data, and decide who writes which view

Your To-Do List

  • Week 11 reading ← due Wednesday
  • Requirements: early GP4 pull request ← due Wednesday, 11:59 pm
  • GP4 ← due Monday