ComeçarComece de graça

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.

Este exercício faz parte do curso

Improving Query Performance in PostgreSQL

Ver curso

Instruções do exercício

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

Exercício interativo prático

Experimente este exercício completando este código de exemplo.

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 (___,___);
Editar e executar o código