Feb 29: Query Optimization

Learning Objectives

After today's class, you should be able to:

• Summarize what information is stored in the system catalogs.
• Explain differences between a TABLE, a VIEW, and an INDEX.
• Describe how query optimization works based on selectivity.

Lesson Outline

HW6 [10 min]

• The postgres_air database (from Tuesday)
  • See postgres_air Queries.sql on Canvas
• Explanation and demo of the jmudb database

Part 1 [35 min]

• Views and System Catalogs
• Exercise: information_schema

Part 2 [30 min]

• Indexes and Query Optimizer
• Exercise: EXPLAIN HW5 queries

Your To-Do List

• Finish reading zyBook sections 3.5 – 3.9
• Before the weekend, finish 40% of Homework 6
  • 2+ queries from air
  • 2+ queries from jmudb
• Before Tuesday, finish 80% of Homework 6
  • That means you should have at least 8 queries working
  • Or you could have 6 queries working and 4 half working