Ciclul de viață al interogării și planificatorul

Îmbunătățirea performanței interogărilor în PostgreSQL

Amy McCarty

Instructor

Ciclul de viață de bază al interogării

Sistem Pași front-end Procese back-end
1 Parser Trimite interogarea la baza de date Verifică sintaxa. Traduce SQL într-o sintaxă mai prietenoasă pentru calculator, pe baza regulilor stocate în sistem.
2 Planificator & Optimizator Evaluează și optimizează sarcinile interogării Folosește statisticile bazei de date pentru a crea planul de interogare. Calculează costurile și alege cel mai bun plan.
3 Executor Returnează rezultatele interogării Urmează planul de interogare pentru a executa interogarea.
Îmbunătățirea performanței interogărilor în PostgreSQL

Planificatorul și optimizatorul de interogări

Receptiv la modificările structurii SQL

  • Generează arbori de plan
    • Noduri corespunzătoare pașilor
    • Vizualizare cu EXPLAIN
  • Estimează costul fiecărui arbore
    • Statistici din pg_tables
    • Optimizare bazată pe timp
1 Plan tree: https://www.postgresql.org/docs/current/querytree.html
Îmbunătățirea performanței interogărilor în PostgreSQL

Statistici din 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 |
  • Indecși de coloană
  • Numărul valorilor nule
  • Lățimea coloanei
  • Valori distincte
Îmbunătățirea performanței interogărilor în PostgreSQL

EXPLAIN

 

  • Fereastră asupra planului de interogare
  • Pași și estimări de cost
    • Nu execută interogarea

 

  • Scanare secvențială a tabelului cheeses
  • Estimări de cost și dimensiune

 

EXPLAIN
SELECT * FROM cheeses

 

Seq Scan on cheeses 
(cost=0.00..10.50 rows=5725 width=296)
Îmbunătățirea performanței interogărilor în PostgreSQL

EXPLAIN: Scanare

 

  • Pas din planul de interogare
  • Returnează rânduri

 

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

 

 

 

 

  • Seq Scan : parcurgerea tuturor rândurilor din tabel
Îmbunătățirea performanței interogărilor în PostgreSQL

EXPLAIN: Cost

 

  • Fără unitate de măsură
  • Comparați structuri cu același rezultat
    • Nu comparați interogări cu rezultate diferite

 

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

 

 

 

 

 

  • 0.00.. : timp de inițializare

  • ..10.50 : timp total

  • timp total = inițializare + execuție

Îmbunătățirea performanței interogărilor în PostgreSQL

EXPLAIN: Dimensiune

 

  • Estimări de dimensiune

 

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

 

 

 

  • rows : rânduri examinate pentru executarea interogării
  • width : lățimea în octeți a rândurilor
Îmbunătățirea performanței interogărilor în PostgreSQL

EXPLAIN cu o clauză 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[]))
  • De jos în sus
    • Pasul 1: Filtrare
    • Pasul 2: Scanare secvențială
  • Clauza WHERE
    • Reduce rândurile de scanat și crește costul total
Îmbunătățirea performanței interogărilor în PostgreSQL

EXPLAIN cu un index

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[]))
  • Pasul 1: Bitmap Index Scan
    • Index Cond explică pasul de scanare
  • INDEX
    • Costul de inițializare a crescut de la 0
    • Costul total a scăzut de la 379
Îmbunătățirea performanței interogărilor în PostgreSQL

Să exersăm!

Îmbunătățirea performanței interogărilor în PostgreSQL

Preparing Video For Download...