Creare e usare un indice

Stai lavorando con i dati dell'indice di qualità dell'aria (AQI) degli Stati Uniti. L'AQI tiene traccia di quattro inquinanti: ozono, particolato, monossido di carbonio e anidride solforosa. Vuoi capire se uno di questi inquinanti è la principale causa della scarsa qualità dell'aria.

La tabella daily_aqi contiene misurazioni giornaliere dell'AQI. Il campo defining_parameter indica quale dei quattro inquinanti è stato il peggiore per quel giorno.

Scriverai molte query usando il tipo di inquinante come filtro. Prima di esplorare i dati, vuoi verificare se defining_parameter ha un indice. In caso contrario, vuoi aggiungerne uno.

Questo esercizio fa parte del corso

Migliorare le prestazioni delle query in PostgreSQL

Visualizza il corso

Esercizio pratico interattivo

Prova a risolvere questo esercizio completando il codice di esempio.

SELECT indexname
FROM ___
___ tablename = ___; -- Filter condition

Modifica ed esegui il codice

Questo esercizio fa parte del corso

Migliorare le prestazioni delle query in PostgreSQL

IntermediárioNível de habilidade

4.8+

Inizia il corso gratis

Bundle up as you dive into the Winter Olympics! You will learn how to join, subquery, and create temporary tables while finding which Olympic athletes brave sub-freezing temperatures to train. You will also learn about the query planner and how its functionality can guide your SQL structure to faster queries.

Exercise 1: All about joins Exercise 2: Where are all the athletes from Exercise 3: Using different joins to explore athletes' regions Exercise 4: What about the weather Exercise 5: Subqueries and common table expressions (cte)Exercise 6: Filtering to freezing with a subquery Exercise 7: Where winter is white Exercise 8: Countries with subqueries or CTEs Exercise 9: Working with temporary tables Exercise 10: Canadians temp table Exercise 11: Analyze that temp table

Dig up those past algebra memories while learning the SQL order of operations. Find which countries "should" have the most athletes by looking at population and gross domestic product (GDP) while learning the best way to filter. You will also learn when your query aggregates (sums, counts, etc.) and how you can structure your query to optimize this process.

Exercise 1: What you write is not what SQL sees Exercise 2: Order of operations impact on query structure Exercise 3: Group by and aggregations Exercise 4: Count and count distinct Exercise 5: Filtering in the WHERE clause Exercise 6: OR versus IN with athletes Exercise 7: Data type filters Exercise 8: EXPLAIN the filter query plan step Exercise 9: Filtering while joining Exercise 10: Where to place a region filter Exercise 11: Filtering in the join, where, and select Exercise 12: Aggregating with different data granularities Exercise 13: Aggregate before joining tables Exercise 14: South African trends

Zero in on the properties that improve database performance. Discover when your table is not a table but a view. Learn how your database's storage structure (row or column oriented) impacts your query structure. You will explore volcanic smog while using partitions and indexes to speed your queries.

Exercise 1: Query, tabelle e viste, accidenti Exercise 2: Caricamento e archiviazione dei dati Exercise 3: Individuare il tipo di tabella Exercise 4: Archiviazione per righe e partizioni Exercise 5: Proprietà dell'archiviazione orientata alle righe Exercise 6: Anteprima di una tabella orientata alle righe Exercise 7: Partizionare per posizione Exercise 8: Usare e creare indici Exercise 9: Trovare gli indici del database Exercise 10: Creare e usare un indice

Esercizio in corso

Exercise 11: Confronta i tempi di esecuzione Exercise 12: Uso dell'archiviazione orientata alle colonne Exercise 13: Proprietà dell'archiviazione orientata alle colonne Exercise 14: Usare l'information schema

Learn the lingo of the Query Lifecycle and dive into the query planner. Explore how the query planner creates and optimizes the query plan. Find your next vacation locale by looking for countries with recent population growth while also seeing how a join impacts the query steps. Fine tune your optimization techniques by seeing how different filters speed your query times.

Exercise 1: Query lifecycle and the planner Exercise 2: Exploring pg_tables Exercise 3: Basic EXPLAIN Exercise 4: Index scans Exercise 5: EXPLAIN the WHERE Exercise 6: A deeper dive into EXPLAIN Exercise 7: EXPLAIN parameters Exercise 8: Aggregating and sorting populations Exercise 9: Joining in the query plan Exercise 10: Query structure and query execution Exercise 11: Subqueries vs. CTEs Exercise 12: Why the difference?Exercise 13: Filtering impacts Exercise 14: Congratulations