1. Learn
    2. /
  2. Courses
    3. /
  3. Improving Query Performance in PostgreSQL

Exercise

Index scans

Your friend lives in Hawaii where the volcanic smog is aggravating his asthma. You are helping him research new locations to live by using the Environmental Protection Agency's county level air quality data. You decide his new home should have good air quality at least 90% of the days.

You know there are a lot of counties in the USA, and the air quality table is large. Your first query (not shown) did a sequential scan and took a long time to run.

You decide to add an index to the column tracking the number of good air quality days. Examine the query plan and see the type of scan.

Instructions

100 XP
  • Create an index for the annual_aqi table using the good column.
  • Only include results with good air quality at least 90% of the year.
  • Focus on recent years by limiting aqi_year to 2017 and 2018.