LoslegenKostenlos loslegen

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.

Diese Übung ist Teil des Kurses

Improving Query Performance in PostgreSQL

Kurs anzeigen

Anleitung zur Übung

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

Interaktive Übung

Vervollständige den Beispielcode, um diese Übung erfolgreich abzuschließen.

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 (___,___);
Code bearbeiten und ausführen