Levenscyclus van een query en de planner

Queryprestaties verbeteren in PostgreSQL

Amy McCarty

Instructor

Basislevenscyclus van een query

Systeem Front-end stappen Back-end processen
1 Parser Stuur query naar database Controleert syntax. Vertaalt SQL naar computer-vriendelijkere syntax op basis van systeemsregels.
2 Planner & Optimizer Beoordeel en optimaliseer taken Gebruikt databasestatistieken om een queryplan te maken. Berekent kosten en kiest het beste plan.
3 Executor Geef queryresultaten terug Volgt het queryplan om de query uit te voeren.
Queryprestaties verbeteren in PostgreSQL

Queryplanner en optimizer

Reageert op wijzigingen in SQL-structuur

  • Genereert plantrees
    • Nodes komen overeen met stappen
    • Visualiseren met EXPLAIN
  • Schat kosten van elke boom
    • Statistieken uit pg_tables
    • Tijdgebaseerde optimalisatie
1 Plan tree: https://www.postgresql.org/docs/current/querytree.html
Queryprestaties verbeteren in PostgreSQL

Statistieken uit 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 |
  • Kolomindexen
  • Aantal null-waarden
  • Kolombreedte
  • Unieke waarden
Queryprestaties verbeteren in PostgreSQL

EXPLAIN

 

  • Kijk in het queryplan
  • Stappen en kosten zijn schattingen
    • Voert de query niet uit

 

  • Sequentiële scan van tabel cheeses
  • Schattingen voor kosten en grootte

 

EXPLAIN
SELECT * FROM cheeses

 

Seq Scan on cheeses 
(cost=0.00..10.50 rows=5725 width=296)
Queryprestaties verbeteren in PostgreSQL

EXPLAIN: Scan

 

  • Stap in queryplan
  • Geeft rijen terug

 

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

 

 

 

 

  • Seq Scan: scan van alle rijen in de tabel
Queryprestaties verbeteren in PostgreSQL

EXPLAIN: Cost

 

  • Eenheidsloos
  • Vergelijk structuren met dezelfde output
    • Vergelijk geen queries met andere output

 

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

 

 

 

 

 

  • 0.00.. : opstarttijd

  • ..10.50 : totale tijd

  • totale tijd = opstart + looptijd

Queryprestaties verbeteren in PostgreSQL

EXPLAIN: Size

 

  • Schattingen van grootte

 

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

 

 

 

  • rows: rijen die de query moet bekijken
  • width: bytebreedte van rijen
Queryprestaties verbeteren in PostgreSQL

EXPLAIN met een WHERE-clausule

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[]))
  • Van onder naar boven
    • Stap 1: Filter
    • Stap 2: Sequentiële scan
  • WHERE-clausule
    • Vermindert te scannen rijen en verhoogt totale kosten
Queryprestaties verbeteren in PostgreSQL

EXPLAIN met een 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[]))
  • Stap 1: Bitmap Index Scan
    • Index Cond legt de scanstap uit
  • INDEX
    • Opstartkosten gestegen vanaf 0
    • Totale kosten gedaald van 379
Queryprestaties verbeteren in PostgreSQL

Laten we oefenen!

Queryprestaties verbeteren in PostgreSQL

Preparing Video For Download...