Ciclo della query e planner

Migliorare le prestazioni delle query in PostgreSQL

Amy McCarty

Instructor

Ciclo base della query

Sistema Passi front end Processi back end
1 Parser Invia la query al database Controlla la sintassi. Traduce SQL in sintassi più adatta alla macchina in base a regole di sistema.
2 Planner & Optimizer Valuta e ottimizza i task della query Usa le statistiche del database per creare il piano. Calcola i costi e sceglie il piano migliore.
3 Executor Restituisce i risultati Segue il piano per eseguire la query.
Migliorare le prestazioni delle query in PostgreSQL

Planner e ottimizzatore di query

Reattivo ai cambi di struttura SQL

  • Genera plan tree
    • Nodi corrispondenti ai passi
    • Visualizza con EXPLAIN
  • Stima il costo di ogni albero
    • Statistiche da pg_tables
    • Ottimizzazione basata sul tempo
1 Plan tree: https://www.postgresql.org/docs/current/querytree.html
Migliorare le prestazioni delle query in PostgreSQL

Statistiche da pg_tables

SELECT * FROM pg_class
WHERE relname = 'mytable'

-- sample of output columns
| relname | relhasindex |

SELECT * FROM pg_stats
WHERE tablename = 'mytable'

-- sample of output columns
null_frac | avg_width | n_distinct |
  • Indici di colonna
  • Conteggio dei null
  • Larghezza colonna
  • Valori distinti
Migliorare le prestazioni delle query in PostgreSQL

EXPLAIN

 

  • Finestra sul piano della query
  • Passi e stime di costo
    • Non esegue la query

 

  • Scansione sequenziale della tabella cheeses
  • Stime di costo e dimensione

 

EXPLAIN
SELECT * FROM cheeses

 

Seq Scan on cheeses 
(cost=0.00..10.50 rows=5725 width=296)
Migliorare le prestazioni delle query in PostgreSQL

EXPLAIN: Scan

 

  • Passo del piano
  • Restituisce righe

 

Seq Scan su cheeses (cost=0.00..10.50 rows=5725 width=296)

 

 

 

 

  • Seq Scan: scansione di tutte le righe della tabella
Migliorare le prestazioni delle query in PostgreSQL

EXPLAIN: Costo

 

  • Senza unità
  • Confronta strutture con lo stesso output
    • Non confrontare query con output diversi

 

Seq Scan su cheeses (cost=0.00..10.50 rows=5725 width=296)

 

 

 

 

 

  • 0.00.. : tempo di avvio

  • ..10.50 : tempo totale

  • tempo totale = avvio + esecuzione

Migliorare le prestazioni delle query in PostgreSQL

EXPLAIN: Dimensioni

 

  • Stime di dimensione

 

Seq Scan su cheeses (cost=0.00..10.50 rows=5725 width=296)

 

 

 

  • rows: righe che la query deve esaminare
  • width: larghezza in byte delle righe
Migliorare le prestazioni delle query in PostgreSQL

EXPLAIN con WHERE

EXPLAIN
SELECT * FROM cheeses WHERE species IN ('goat','sheep') 

Seq Scan on cheeses (cost=0.00..378.90 rows=3 width=118)
 -> Filter: (species = ANY ('{"goat","sheep"}'::text[]))
  • Dal basso verso l'alto
    • Passo 1: Filtro
    • Passo 2: Scansione sequenziale
  • Clausola WHERE
    • Riduce le righe da scansionare e aumenta il costo totale
Migliorare le prestazioni delle query in PostgreSQL

EXPLAIN con un indice

EXPLAIN
SELECT * FROM cheeses WHERE species IN ('goat','sheep') -- index on species column

Bitmap Index Scan using species_idx on cheeses (cost=0.29..12.66 rows=3 width=118)
  Index Cond: (species = ANY ('{"goat","sheep"}'::text[]))
  • Passo 1: Bitmap Index Scan
    • Index Cond spiega il passo di scansione
  • INDICE
    • Il costo di avvio è aumentato da 0
    • Il costo totale è sceso da 379
Migliorare le prestazioni delle query in PostgreSQL

Ayo berlatih!

Migliorare le prestazioni delle query in PostgreSQL

Preparing Video For Download...