Saame aru, et PostgreSQL päringud on veelgi mugavamad

Kuus kuud tagasi me esitlesime explain.tensor.ru — avalikust teenusest päringute plaanide analüüsimiseks ja visualiseerimiseks PostgreSQL-i jaoks.

Viimaste kuude jooksul oleme koostanud selle kohta ettekande PGConf.Russia 2020, valmistanud kokkuvõtva artikli SQL-päringute kiirendamisest põhinedes soovitustel, mida see pakub... kuid kõige olulisem on, et oleme kogunud teie tagasisidet ja jälginud reaalseid kasutusjuhtumeid.

Ja nüüd oleme valmis rääkima uutest võimalustest, mida saate kasutada.

Toetamine erinevate plaaniformaatide jaoks

Plaan logist, koos päringuga

Otseselt konsoolist valime kogu ploki, alustades reale Query Text, koos kõikide eesolevate tühi kohtadega:

        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 paneme kogu kopeeritud otse plaani väljale, ega midagi jagamata:

Väljundina saame boonuseks analüüsitud plaani juurde ka vahekaart „kontekst”, kus meie päring on esitatud kogu oma hiilguses:

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
  }
]"

Seda, kas tolereerib välistes jutumärkides, nagu pgAdmin kopeerib, või ilma – paneme sama väljale, tulemus on suurepärane:

Täpsem visualiseerimine

Planeerimise aeg / Täitmise aeg

Nüüd on selgem, kuhu täiendav aeg päringu täitmisel kadus:

I/O ajastus

Mõnikord tuleb kokku puutuda olukorraga, kus plaanis tundub, et ressursse on lugemise ja kirjutamise osas mitte liiga palju, kuid täitmise aeg on ebaproportsionaalselt pikk.

Siin tuleb öelda: "Oi, ilmselt oli sellel hetkel serveri kett liiga koormatud, seetõttu lugemine kestis nii kaua!" Aga see ei ole väga täpne…

Kuid seda saab kindlalt määrata. Asi on selles, et PG-serveri seadistuste seas on track_io_timing:

Sisaldab sisend-/väljundoperatsioonide aja mõõtmist. See parameeter on vaikimisi keelatud, kuna selleks on vajalik pidev süsteemi ajaküsimine, mis võib mõnel platvormil tööd oluliselt aeglustada. Aja mõõtmise kulude hindamiseks oma platvormil saab kasutada utiliiti pg_test_timing. Sisend-/väljastatistikate saamiseks saab kasutada vaadet pg_stat_database, EXPLAIN väljundis (kui kasutatakse parameetrit BUFFERS) ja läbi vaate pg_stat_statements.

Seda parameetrit saab lubada ka kohaliku sessiooni raames:

SET track_io_timing = TRUE;

Ja nüüd kõige toredamini — me oleme õppinud neid andmeid mõistma ja kuvama, arvestades kõiki täitmispuu transformatsioone:

Siin võib märgata, et 0.790 ms koguaegsest täitmisest kulus 0.718 ms ühe andmelehe lugemiseks, 0.044 ms selle kirjutamiseks ja kogu muu kasuliku tegevuse jaoks kulus vaid 0.028 ms!

Tulevik PostgreSQL 13-ga

Täieliku ülevaate uuendustest leiate detailse artikli, aga me käsitleme spetsiifilisi muudatusi plaanides.

Planeerimise puuid

Ressursside planeerijale kajastatakse veel ühes plaastrit, mis ei ole seotud pg_stat_statements. EXPLAIN koos BUFFERS valikuga annab teada, kui palju puhvermälus kasutati planeerimise etapis:

 Seq Scan pg_class (rea arvud=386 kordused=1)
   Puhver: jagatud hit=9 loetud=4
 Planeerimise aeg: 0.782 ms
   Puhver: jagatud hit=103 loetud=11
 Käitamise aeg: 0.219 ms

Inkrementaalne sortimine

Juhtudel, kus on vajalik sortimine mitmete võtmete (k1, k2, k3…) järgi, võib planeerija nüüd kasutada teadmisi, et andmed on juba sorteeritud mitme esialgse võtme järgi (näiteks k1 ja k2). Nii et pole vaja kõiki andmeid nullist uuesti sortida, vaid jagada need järjestikusteks rühmadeks, millel on samad k1 ja k2 väärtused, ja 'dosoortida' võtme k3 järgi.

Nii et kogu sortimine laguneb mitmeks järjestikuseks väiksemaks sortimiseks. See vähendab vajaliku mälu mahtu ja võimaldab samuti esimesed andmed välja anda enne, kui kogu sortimine on täielikult tehtud.

 Inkrementaalne sorteerimine (reaalsetest ridadest=2949857 silmus=1)
   Sortimisvõti: ticket_no, passenger_id
   Eelnevalt sortitud võti: ticket_no
   Täissorteerimisrühmad: 92184 Sorteerimismeetod: quicksort Mälu: keskmine=31kB tipp=31kB
   ->  Indeksi skaneerimine kasutades tickets_pkey pileteid (reaalsetest ridadest=2949857 silmus=1)
 Plaanimise aeg: 2.137 ms
 Käitamise aeg: 2230.019 ms

UI/UX täiustused

Kuvatakse ekraanipilte, need on igal pool!

Nüüd on igal vahelehel kiire võimalus võtta vahelehe ekraanipilt lõikepildile täieliku laius ja sügavus vahelehel — „sihtimislaik“ paremal üleval:

Tegelikult on enamik piltidest selle avalduse jaoks saadud just niimoodi.

Soovitused sõlmedes

Nende arv on mitte ainult suurenenud, vaid igaühe kohta on nüüd võimalik detailsemalt lugeda artiklist, liikudes lingile:

Arhiivist kustutamine

Mõned soovisid väga, et lisaks ei olnud võimalik kustutada „täielikult“ isegi avaldamata arhiiviplaneeringud — palun, piisab, kui vajutad vastavale ikoonile:

Noh, ja ärge unustage, et meil on toetusrühm, kuhu saate kirjutada oma märkusi ja ettepanekuid.

Allikas: habr.com