Get startedGet started for free

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.

This exercise is part of the course

Improving Query Performance in PostgreSQL

View Course

Exercise instructions

  • 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.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

CREATE INDEX good_index 
ON ___(___);

EXPLAIN
SELECT state_name, county_name, aqi_year, good
FROM annual_aqi
WHERE ___ > 327 -- 90% of the year
AND ___ IN (___,___);
Edit and Run Code