🥇PostgreSQL Antipatterns: registri navigeerimine

Täna ei tule mingeid keerulisi juhtumeid ega arusaamatuid SQL-algoritme. Kõik on väga lihtne, isegi Kapten Ilmselge tasemel — teeme sündmuste registri vaatamine aja järgi sortimisega.

Ehk siis on meil andmebaasis tabel events, millel on väli ts — see on just see aeg, mille järgi me soovime neid kirjeid korrastatult kuvada:

CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

CREATE INDEX ON events(ts DESC);

Selge, et meil ei ole seal kümmet kirjet, seega vajame mingil kujul leheküljelist navigeerimist.

#0. «Я у мамы погроммист»

cur.execute("SELECT * FROM events;")
rows = cur.fetchall();
rows.sort(key=lambda row: row.ts, reverse=True);
limit = 26
print(rows[offset:offset+limit]);

Peaaegu mitte naljaga — harva, aga esineb looduses. Mõnikord on pärast ORM-i kasutamist raske üle minna 'otse' SQL-i töötlemisele.

Aga liikume edasi levinumate ja vähem ilmselgete probleemide juurde.

#1. OFFSET

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - kirjeid lehe peal, $1 - lehe algus

Kust see number 26 siin tuli? See on ligikaudne kirje arv, et täita ühte ekraani. Täpsemalt, 25 kuvatavat kirjet pluss 1, mis näitab, et valikus on veel midagi ning on mõtet edasi liikuda.
Loomulikult ei pea seda väärtust 'sisse kirjutama' päringu kehas, vaid seda saab edastada parameetrina. Kuid sel juhul ei saa PostgreSQL-i planeerija toetuda teadmisele, et kirjeid peaks olema suhteliselt vähe — ja vali tõhusaks plaaniks lihtsalt vale plaani.

Ja seni, kuni rakenduse liideses on sündmuste registri vaatamine realiseeritud visuaalsete 'lehekülgede' vahetamine, ei pane keegi kaua midagi kahtlast tähele. Just kuni hetkeni, mil UI/UX mugavuse nimel ei otsustata liidest muuta 'lõputuks kerimiseks' — st kõik registreerimise kirjed joonistatakse ühte loendisse, mida kasutaja saab üles-alla kerida.

Ja nüüd, järgmise testimise ajal tabate end kirjete dubleerimiselt registris. Miks, kui tabelil on korralik indeks (ts), millele teie päring toetub?

Just sellepärast, et te ei arvestanud, et ts ei ole selle tabeli unikaalne võti . Tegelikult pole ka selle väärtused unikaalsed, nagu iga «aeg» reaalses elus — seetõttu võib sama kirje kahe naaberpäringu vahel hõlpsasti «hüppata» ühelt lehelt teisele olenevalt väärtuse võtme sortimise erinevast järjestusest.

Tegelikult peitub siin veel teine probleem, mida on palju keerulisem märgata — mõned kirjed ei kuvata üldse! Lõppude lõpuks võtsid «dubleeritud» kirjed kellegi koha. Üksikasjaliku selgituse koos kaunite piltidega saate lugeda siit.

Laieneme indeksit

Kaval arendaja mõistab — indeksivõti peab olema ainulaadne ning kõige lihtsam viis selle saavutamiseks on laiendada seda juba ainsaks peamiseks võtmega:

LOO UNIKAALNE INDeks EVENTS(ts DESC, id DESC);

Ja päring muutub:

VALI
  ...
KORDA
  ts DESC, id DESC
LIMIT 26 OFFSET $1;

#2. Переход на «курсоры»

Mõne aja pärast tuleb teie juurde DBA ja „rõõmustab”, et teie päringud koormavad serverit oma «hobuse» OFFSET’itega, ja üldiselt on aeg liikuda navigatsioonile viimase kuvatud väärtuse järgi. Teie päring muutub taas:

VALI
  ...
KUS
  (ts, id) < ($1, $2) -- viimased eelnevalt saadud väärtused
KORDA
  ts DESC, id DESC
LIMIT 26;

Te hingasite kergendatult, kuni ei tulnud...

#3. Чистка индексов

Sest ühel päeval luges teie DBA artikli ebaefektiivsete indeksite kohta ja mõistis, et „mitte viimane” ajatemperatuur — see pole hea. Ja taas tuli ta teie juurde — nüüd mõttega, et see indeks peab siiski tagasi minema (ts DESC).

Aga mis on algse «hüppamise» kirje probleemiga?.. Kõik on keeruline — peab valima blokid, millel ei ole fikseeritud arvu kirjeid!

Tegelikult, kes keelab meil lugeda mitte „täpselt 26”, vaid „vähemalt 26”? Näiteks nii, et järgmises plokis oleksid kirjed, millel on veendumus, et keerukad väärtused ts — siis ei tohiks ka olla probleeme kirje „hüppamisega” plokkide vahel!

Nii saavutame me selle:

VALI
  ...
KUS
  ts = coalesce((
    VALI
      ts
    FROM
      events
    KUS
      ts < $1
    KORDA
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
KORDA
  ts DESC;

Mis siin üldse toimub?

Liigume 25 kirje võrra „alla” ja saame „piirväärtuse” ts.
Kui seal ei ole enam midagi, siis asendame NULL-väärtuse -infinity.
Vähendame kogu segment väärtusi saadud väärtuse ning edastatud liidest parameetri $1 (eelnevalt „viimase” joonistatud väärtuse) vahel. ts ja edastatud liidesest parameetriga $1 (eelneva „viimase“ joonistatud väärtusega).
Kui plokk tagastati vähem kui 26 kirjet, on see viimane.

Või sama asi pildina:

Kuna nüüd on meil valim ei oma mingit kindlat "algust", siis ei takista meid miski selle päringu "tagurpidi" pööramisest ja rakendades dünaamilist andmeplokkide laadimist "tugipunktist" mõlemas suunas — nii alla kui ka üles.

Note

Jah, sellisel juhul pöördume indeksisse kaks korda, kuid kõik on "puhtalt indekseeritud". Seetõttu toob sisemine päring kaasa vaid ühe täiendava Index Only Scan.
On piisavalt ilmne, et seda meetodit saab kasutada ainult siis, kui teil on väärtused ts võivad juhuslikult ristuda ja neid on vähe.. Kui aga teie tüüpiline juhtum on "miljon kirjet ajaloos 00:00:00.000", siis ei tasu seda nii teha. Ehkki, kui see juba nii läks, kasutage laiendatud indeksi varianti.

Allikas: habr.com