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
Exercise instructions
- Create an index for the
annual_aqitable using thegoodcolumn. - Only include results with
goodair quality at least 90% of the year. - Focus on recent years by limiting
aqi_yearto 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 (___,___);