Exercises

Objectives

By the end of this lesson, you should be able to:

• Create and query a view and a materialized view
• Create and drop an index, confirming it in pg_indexes
• Read a real execution plan with EXPLAIN

⚠️ A note on verification: every query and result in this lesson was run against a real, live PostgreSQL 18 database.

Setup

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary NUMERIC(10,2) NOT NULL,
    hire_date DATE NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL
);

INSERT INTO employees (first_name, last_name, department, salary, hire_date, email) VALUES
  ('Erin', 'Castillo', 'Engineering', 74000, '2021-03-15', 'erin.castillo@example.com'),
  ('Jordan', 'Blake', 'Sales', 61000, '2022-06-01', 'jordan.blake@example.com'),
  ('Maya', 'Fischer', 'Marketing', 67000, '2020-11-20', 'maya.fischer@example.com'),
  ('Priya', 'Desai', 'Engineering', 89000, '2019-01-10', 'priya.desai@example.com'),
  ('Sam', 'Whitfield', 'Sales', 63000, '2023-02-14', 'sam.whitfield@example.com'),
  ('Devon', 'Ellery', NULL, 58000, '2023-08-30', 'devon.ellery@example.com'),
  ('Taylor', 'Nakamura', 'Engineering', 95000, '2018-05-05', 'taylor.nakamura@example.com'),
  ('Alexis', 'Moreno', 'Marketing', 71000, '2021-09-12', 'alexis.moreno@example.com');

Exercise 1: Views

a) Create a view named engineering_team selecting first_name, last_name, and salary for every Engineering employee. Query it. Expected result: Erin, Priya, and Taylor.

b) Insert a new employee, Robin Ashworth (robin.ashworth@example.com, Engineering, 82000, hired 2024-01-01), then query engineering_team again. Expected result: Robin now appears too, 4 rows total, confirming the view reflects the new row automatically.

Exercise 2: Materialized Views

a) Create a materialized view named salary_summary showing department, headcount, and total salary, grouped by department (excluding NULL). Query it. Expected result: Engineering 4 employees/340000.00, Marketing 2/138000.00, Sales 2/124000.00.

b) Insert a new employee, Casey Fontaine (casey.fontaine@example.com, Sales, 66000, hired 2024-03-01), then query salary_summary again without refreshing. Expected result: unchanged, still showing Sales at 2/124000.00, the materialized view hasn’t refreshed yet.

c) Run REFRESH MATERIALIZED VIEW salary_summary;, then query it again. Expected result: Sales now shows 3 employees/190000.00.

Exercise 3: Indexes

a) Create an index named idx_employees_hire_date on the hire_date column. Query pg_indexes for the employees table and confirm it appears alongside the automatic employees_pkey and employees_email_key indexes.

b) Drop idx_employees_hire_date, then query pg_indexes again and confirm only the two automatic indexes remain.

c) Explain, in your own words, why employees_pkey and employees_email_key already existed without ever running CREATE INDEX for them.

Exercise 4: Reading an Execution Plan

a) Run EXPLAIN on a query selecting every employee with salary > 70000. Record the scan type (Seq Scan or otherwise) and the estimated cost.

b) Explain, in your own words, why this query plan is a sequential scan given the size of the employees table in this exercise (9 rows).

c) Describe what you’d expect to see differently if this same query ran against a table with 500,000 rows and an index on salary.

Recap

This module covered every core database object and performance tool: views for saving a reusable query, materialized views for trading freshness for speed, indexes for faster lookups, and EXPLAIN/EXPLAIN ANALYZE for seeing exactly how PostgreSQL plans and executes a query. Together, these move beyond just writing correct queries into understanding how they actually perform.

Next module: transactions, keeping several changes safe together as one all-or-nothing unit.