ProHoster > Blogg > Administrasjon > Vi forstÄr PostgreSQL-spÞrsmÄl enda bedre

Vi forstÄr PostgreSQL-spÞrsmÄl enda bedre

For et halvt Ă„r siden presenterte vi explain.tensor.ru — en offentlig tjeneste for Ă„ analysere og visualisere spĂžrringsplaner for PostgreSQL.

Vi forstÄr PostgreSQL-spÞrsmÄl enda bedre

I lĂžpet av de siste mĂ„nedene har vi laget en rapport om det til PGConf.Russia 2020, forberedt en oppsummerende artikkel om optimalisering av SQL-spĂžrringer basert pĂ„ anbefalingene det gir... men viktigst av alt – vi har samlet tilbakemeldingene deres og sett pĂ„ reelle bruksomrĂ„der.

Og nÄ er vi klare til Ä fortelle om de nye mulighetene som dere kan dra nytte av.

StĂžtte for ulike planformater

Planen fra loggen, sammen med spĂžrringen

Vi markerer hele blokken fra konsollen, og begynner med linjen som inneholder Query Text, med alle ledende mellomrom:

        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)


 og vi limer alt det kopierte rett inn i feltet for planen, uten Ă„ dele noe:

Vi forstÄr PostgreSQL-spÞrsmÄl enda bedre

PÄ utgangen fÄr vi i tillegg til den analyserte planen ogsÄ fanen «kontekst», hvor spÞrringen vÄr er vist i all sin prakt:

Vi forstÄr PostgreSQL-spÞrsmÄl enda bedre

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

Enten med ytre anfþrselstegn, som pgAdmin kopierer, eller uten – vi kaster det i det samme feltet, og utgangen blir vakker:

Vi forstÄr PostgreSQL-spÞrsmÄl enda bedre

Utvidet visualisering

Planleggingstid / UtfĂžrelsestid

NĂ„ er det bedre Ă„ se hvor det ekstra tiden gikk under utfĂžrelsen av spĂžrringen:

Vi forstÄr PostgreSQL-spÞrsmÄl enda bedre

I/O-tid

Noen ganger mÄ man stÄ overfor situasjonen hvor planen ikke ser ut til Ä ha lest- eller skrevet for mange ressurser, men utfÞrelsestiden er likevel uforholdsmessig lang.

Her mĂ„ vi si: "Åh, sannsynligvis var disken pĂ„ serveren overbelastet pĂ„ det tidspunktet, sĂ„ det tok sĂ„ lang tid Ă„ lese!" Men det er ikke sĂ„ veldig presist ...

Men dette kan bestemmes helt sikkert. Saken er at blant alternativene for PG-serverkonfigurasjonen finnes det track_io_timing:

Inkluderer mÄling av inn-/utdataoperasjoner. Denne parameteren er som standard deaktivert, ettersom det krever konstant forespÞrsel om nÄvÊrende tid fra operativsystemet, noe som kan betydelig redusere ytelsen pÄ enkelte plattformer. For Ä vurdere kostnadene ved tidtaking pÄ din plattform, kan du bruke verktÞyet pg_test_timing. Statistikk over inn-/utdata kan hentes gjennom visningen pg_stat_database, i utskriften EXPLAIN (nÄr parameteren BUFFERS brukes) og gjennom visningen pg_stat_statements.

Denne parameteren kan ogsÄ aktiveres innenfor en lokal Þkt:

SET track_io_timing = TRUE;

NĂ„, det mest interessante – vi har lĂŠrt Ă„ forstĂ„ og vise disse dataene med hensyn til alle transformasjoner i kjĂžreoppsettet:

Vi forstÄr PostgreSQL-spÞrsmÄl enda bedre

Her kan man merke seg at av 0.790 ms totalt kjĂžretid, tok 0.718 ms Ă„ lese Ă©n datasesjon, 0.044 ms – Ă„ skrive den samme, mens hele den Ăžvrige nyttige aktiviteten kun tok 0.028 ms!

Fremtiden med PostgreSQL 13

Du kan lese den fullstendige oversikten over nyhetene i en detaljert artikkel, og vi vil spesifikt gÄ inn pÄ endringene i planene.

Planleggingsbuffere

Regnskapet av ressursene tildelt planleggeren har ogsÄ blitt reflektert i en annen patch, som ikke er relatert til pg_stat_statements. EXPLAIN med BUFFERS-alternativet vil rapportere antallet buffere brukt i planleggingsfasen:

 Seq Scan pÄ pg_class (faktiske rader=386 slÞyfer=1)
   Buffers: delt treff=9 lest=4
 Planleggingstid: 0.782 ms
   Buffers: delt treff=103 lest=11
 KjĂžringstid: 0.219 ms

Vi forstÄr PostgreSQL-spÞrsmÄl enda bedre

Inkrementell sortering

I tilfeller der det er nĂždvendig Ă„ sortere etter mange nĂžkler (k1, k2, k3
), kan planleggeren nĂ„ benytte seg av kunnskap om at dataene allerede er sortert etter noen av de fĂžrste nĂžklene (for eksempel k1 og k2). I sĂ„ fall er det ikke nĂždvendig Ă„ sortere alle dataene pĂ„ nytt, men de kan deles inn i sekvensielle grupper med like verdier for k1 og k2, og “sorteres” etter nĂžkkelen k3.

Dermed brytes hele sorteringen ned til flere sekvensielle sorteringer av mindre stÞrrelse. Dette reduserer den nÞdvendige minnebruken, og gjÞr det ogsÄ mulig Ä levere de fÞrste dataene fÞr hele sorteringen er fullfÞrt.

 Inkrementell Sort (faktiske rader=2949857 slĂžyfer=1)
   SorteringsnĂžkkel: ticket_no, passenger_id
   ForhÄndsortert nÞkkel: ticket_no
   Fullsorteringsgrupper: 92184 Sorteringsmetode: hurtigsortering Minne: gj.snitt=31kB topp=31kB
   ->  Indeksfinn pÄ tickets_pkey pÄ billetter (faktiske rader=2949857 slÞyfer=1)
 Planleggingstid: 2.137 ms
 KjĂžringstid: 2230.019 ms

Vi forstÄr PostgreSQL-spÞrsmÄl enda bedre
Vi forstÄr PostgreSQL-spÞrsmÄl enda bedre

Forbedringer av UI/UX

Skjermbilder, de er overalt!

NĂ„ er det mulig Ă„ raskt ta et skjermbilde av fanen til utklippstavlen i full bredde og dybde av fanen — «sikte» Ăžverst til hĂžyre:

Vi forstÄr PostgreSQL-spÞrsmÄl enda bedre

Faktisk er de fleste bildene for denne publikasjonen hentet nettopp slik.

Anbefalinger pÄ noder

De har ikke bare blitt flere, men man kan ogsÄ lese mer detaljert om hver enkelt i artikkelen, ved Ä klikke pÄ lenken:

Vi forstÄr PostgreSQL-spÞrsmÄl enda bedre

Fjerning fra arkivet

Noen har bedt om Ă„ legge til muligheten Ă„ slette «helt» selv om planer som ikke publiseres i arkivet — vĂŠr sĂ„ snill, klikk pĂ„ den tilsvarende ikonen:

Vi forstÄr PostgreSQL-spÞrsmÄl enda bedre

Og glem ikke at vi har supportgruppe, hvor man kan sende inn sine merknader og forslag.

Kilde: habr.com

KjĂžp pĂ„litelig hosting for nettsteder med DDoS-beskyttelse, VPS VDS-servere đŸ”„ KjĂžp pĂ„litelig hosting for nettsteder med DDoS-beskyttelse, VPS VDS-servere | ProHoster