🥇Mõistame PostgreSQL-päringute plaane veel mugavamalt

Pool aastat tagasi esitlesime explain.tensor.ru — avalikku teenust päringute plaanide analüüsimiseks ja visualiseerimiseks PostgreSQL-ile.

Viimase kuue kuu jooksul tegime sellest ettekande PGConf.Russia 2020-l, koostasime üldistava artikli SQL-päringute kiirusest soovituste põhjal, mida see annab... kuid kõige tähtsam on, et kogusime teie tagasisidet ja jälgisime tegelikke kasutusjuhtumeid.

Ja nüüd oleme valmis rääkima uutest võimalustest, millest saate kasu lõigata.

Erinevate plaaniformaatide tugi

Plaan logist koos päringuga

Valime otse konsoolist terve ploki, alates reast, millel on Query Text, koos kõigi juhtivate tühikutega:

        Query Text: INSERT INTO dicquery_20200604 VALUES ($1.*) ON CONFLICT (query) DO NOTHING;
                           Insert on dicquery_20200604 (cost=0.00..0.05 rows=1 width=52) (actual time=40.376..40.376 rows=0 loops=1)
          Conflict Resolution: NOTHING
          Conflict Arbiter Indexes: dicquery_20200604_pkey
          Tuples Inserted: 1
          Conflicting Tuples: 0
          Buffers: shared hit=9 read=1 dirtied=1
          -> Result (cost=0.00..0.05 rows=1 width=52) (actual time=0.001..0.001 rows=1 loops=1)

… ja viskame kõik kopeeritud otse plaani välja, midagi ei jagades:

Lõpptulemusena saame boonuseks analüüsitud plaanile veel ka vahekaardi „kontekst”, kus meie päring on esitatud oma täies ilus:

JSON ja YAML

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM pg_class;

"[
  {
    "Plan": {
      "Node Type": "Seq Scan",
      "Parallel Aware": false,
      "Relation Name": "pg_class",
      "Alias": "pg_class",
      "Startup Cost": 0.00,
      "Total Cost": 1336.20,
      "Plan Rows": 13804,
      "Plan Width": 539,
      "Actual Startup Time": 0.006,
      "Actual Total Time": 1.838,
      "Actual Rows": 10266,
      "Actual Loops": 1,
      "Shared Hit Blocks": 646,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0
    },
    "Planning Time": 5.135,
    "Triggers": [
    ],
    "Execution Time": 2.389
  }
]"

Oli see siis väliste jutumärkidega, nagu kopib pgAdmin, või ilma — viskame samasse väljasse, tulemuseks — ilu:

Täpsem visualiseerimine

Planeerimise aeg / Täitmise aeg

Nüüd on paremini näha, kuhu kadus lisakäitumise aeg päringu täitmises:

I/O ajastus

Mõnikord tuleb kokku puutuda olukorraga, kus plaanis ei tundunud kurte, kuid täitmise aeg oli mingil moel ebanormaalselt pikk.

Siis tuleb öelda: "Oh, ilmselt oli sel hetkel serveri kett liiga koormatud, seetõttu luges nii kaua!" Kuid see ei ole just kõige täpsem...

Kuid seda on võimalik kindlalt määrata. Asi on selles, et PG-serveri konfiguratsiooni valikute hulgas on track_io_timing:

Sisaldab sisendi/väljundi operatsioonide aja mõõtmist. See parameeter on vaikimisi välja lülitatud, kuna selleks on vajalik pidev hetkeaja pärimine operatsioonisüsteemilt, mis võib mõnel platvormil tööprotsessi oluliselt aeglustada. Ajakulu mõõtmise hindamiseks oma platvormil saate kasutada tööriista pg_test_timing. Sisendi/väljundi statistikat saab kätte vaate pg_stat_database kaudu, EXPLAIN väljundis (kui kasutate parameetrit BUFFERS) ja vaate pg_stat_statements kaudu.

Seda parameetrit saab aktiveerida ka kohaliku sessiooni raames:

SET track_io_timing = TRUE;

Nüüd on aga parim osa — oleme õppinud neid andmeid mõistma ja kuvama, arvestades kõiki täitmispuu transformaate:

Siit on näha, et 0.790 ms kogu täitmisajast läks 0.718 ms andmelehe lugemiseks, 0.044 ms selle kirjutamiseks, ning kogu muu kasulik tegevus kestis vaid 0.028 ms!

Tulevik PostgreSQL 13-ga

Kogu uuenduste ülevaate leiate ühelt põhjalikult kirjutatud artiklilt, kuid räägime täpsemalt plaanide muudatustest.

Planeerimise bufrid

Ressursside arvestamine, mis on määratud planeerijale, kajastub veel ühes paranduses, mis ei kuulu pg_stat_statements alla. EXPLAIN variant BUFFERS näitab, kui palju bufreid kasutati planeerimise etapis:
 Seq Scan on pg_class (tegelikud read=386 silmus=1)
   Buffers: shared hit=9 loetud=4
 Planeerimise aeg: 0.782 ms
   Buffers: shared hit=103 loetud=11
 Täitmise aeg: 0.219 ms

Inkrementaalne sortimine

Juhtudel, mil on vajalik sortimine mitme võtme järgi (k1, k2, k3…), saab planeerija nüüd kasutada teadlikkust sellest, et andmed on juba sorteeritud mitme esialgse võtme järgi (näiteks k1 ja k2). Sel juhul ei ole vaja kogu andmemassi uuesti sorteerida, vaid jagada see järjestikusteks gruppideks ühesuguste k1 ja k2 väärtustega ning „dosoortida“ võtme k3 järgi.
Nii jaguneb kogu sortimine mitmeks järjestikuseks väiksema mahuga sortimiseks. See vähendab vajaliku mälu mahtu, lisaks võimaldab see esimesed andmed välja anda varem, kui kogu sortimine on täielikult lõpule viidud.
 Inkrementaalne sort (tegelikud read=2949857 silmus=1)
   Sortimisvõti: ticket_no, passenger_id
   Eelsorteeritud võti: ticket_no
   Täis-sortimised: 92184 Sortimismeetod: quicksort Mälu: avg=31kB tipp=31kB
   ->  Indeksi skaneerimine kasutades tickets_pkey tickete peal (tegelikud read=2949857 silmus=1)
 Planeerimise aeg: 2.137 ms
 Täitmise aeg: 2230.019 ms

UI/UX täiustused.

Kuvapildid, need on igal pool!

Nüüd on igas sakkides võimalik kiiresti sakkide ekraanipilt võtta lõikelauale saka kogu laiuse ja sügavuse ulatuses — „sihtmärgiga“ paremal ülal:

Tegelikult on enamik pilte selle avaldamise jaoks saadud just nii.