🥇PostgreSQL Antipatterns: navigacija po registru

Danes ne bo nikakršnih zapletenih primerov in neobičajnih algoritmov v SQL-u. Vse bo zelo preprosto, na ravni Kapitana Očitno — delamo pregled dnevnika dogodkov s razvrstitvijo po času.

Torej, imamo v bazi tabelo events, ki ima polje ts — točno tisti čas, po katerem želimo, da so ti zapisi razvrščeni:

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

CREATE INDEX ON events(ts DESC);

Jasno je, da tam ne bo le deset zapisov, zato bomo potrebovali nek način stranično navigacijo.

#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]);

Tudi skoraj ne šala — redko, a se pojavlja v naravi. Včasih je težko preklopiti na "direkten" način dela z SQL po delu z ORM.

Toda preidimo k bolj pogostim in manj očitnim težavam.

#1. OFFSET

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - zapisov na strani, $1 - začetek strani

Od kod se je vzela številka 26? To je približno število zapisov za zapolnitev enega zaslona. Natančneje, 25 prikazanih zapisov, plus 1, ki signalizira, da je v izboru še kaj drugega in ima smisel nadaljevati.
Seveda, te vrednosti ni treba "vrezati" v telo poizvedbe, ampak jih prenesti prek parametra. Toda v tem primeru načrtovalnik PostgreSQL ne bo mogel upoštevati, da bi moralo biti zapisov razmeroma malo — in lahko izbere neučinkovit načrt.

In medtem ko je v vmesniku aplikacije pregled dnevnika uresničen kot preklapljanje med vizualnimi "stranmi", nihče dolgo ne opazi nič sumljivega. Točno do trenutka, ko se v prizadevanju za udobje UI/UX odločijo, da preuredijo vmesnik na "neskončno pomikanje" — torej so vsi zapisi dnevnika narisani kot en seznam, ki ga uporabnik lahko pomika navzgor in navzdol.

In tako, med ponovnim testiranjem vas ujamejo pri podvajanju zapisov v dnevniku. Zakaj, kajti na tabeli je normalen indeks (ts), na katerega se opira vaša poizvedba?

In točno zato, ker niste upoštevali, da ts ni edinstven ključ v tej tabeli. V bistvu pa vrednosti niso edinstvene, kot je pri vsakem »času« v resničnih pogojih — zato se lahko ista vnos pri dveh sosednjih poizvedbah »preskoči« s strani na stran zaradi druge končne razvrstitve v okviru sortiranja enake vrednosti ključa.

Dejansko pa se tu skriva še drugi problem, ki ga je težje opaziti — nekateri vnosi ne bodo prikazani sploh! Ker so »podvojeni« vnosi zasedli nek prostor. Podrobno razlago s čudovitimi slikami lahko preberete tukaj.

Razširitev indeksa

Prevaro razvijalec razume — treba je narediti ključ indeksa edinstven, najpreprostejši način pa je razširiti ga s predhodno edinstvenim poljem, ki je lahko PK:

CREATE UNIQUE INDEX ON events(ts DESC, id DESC);

In poizvedba se mutira:

SELECT
  ...
ORDER BY
  ts DESC, id DESC
LIMIT 26 OFFSET $1;

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

Nekaj časa kasneje k vam pride DBA in »veseli« vas, da vaše poizvedbe hudo obremenjujejo strežnik s svojimi velikimi OFFSET, in sploh, čas je, da preidemo na navigacijo od zadnje prikazane vrednosti. Vaša poizvedba se spet mutira:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- zadnje prejeto vrednosti na prejšnjem koraku
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Olajšano ste zadihali, dokler ni prišlo do…

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

Ker je enkrat vaš DBA prebral članek o iskanju neučinkovitih indeksov in je razumel, da »nezadnji« časovni žig — to je slabo. In spet je prišel k vam — tokrat s miselnostjo, da bi moral tisti indeks spet postati (ts DESC).

Toda kaj storiti s prvotnim problemom »preskakovanja« zapisov med stranmi?.. Vse je preprosto — treba je izbrati bloke z nefixiranim številom zapisov!

Torej kdo nam prepoveduje brati ne »natančno 26«, temveč »vsaj 26«? Na primer, tako, da so v naslednjem bloku zapisi z zagotovo drugimi vrednostmi ts — tako ne bo težav s »preskakovanjem« zapisov med bloki!

Tukaj je, kako to doseči:

SELECT
  ...
WHERE
  ts = coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < $1
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;

Kaj se tukaj sploh dogaja?

Skočimo 25 zapisov »navzdol« in dobimo »mejno« vrednost ts.
Če tam ni že nič, nadomestimo NULL-vredno z -infinity.
Odštejemo celoten segment vrednosti med pridobljeno vrednostjo ts in posredovanim iz vmesnika parametrom $1 (prejšnja »zadnja« narisana vrednost).
Če blok vrne manj kot 26 zapisov, je to zadnji.

Ali enako v sliki:

Ker imamo zdaj izbor nima nekega določenega „začetka“, nam to ne preprečuje, da „razvijemo“ ta poizvedbo v obratni smeri in izvedemo dinamično nalaganje podatkovnih blokov od „referenčne točke“ v obe smeri — tako navzdol kot navzgor.

Napomena

Da, v tem primeru se sklicujemo na indeks dvakrat, vendar vse „čisto po indeksu“. Zato bo vključen poizvedba privedla le do enega dodatnega Index Only Scan.
Očitno je, da lahko to metodo uporabljate le, ko imate vrednosti ts ki se lahko prekrivajo le naključno, in jih je malo.. Če pa je vaš tipičen primer „milijon zapisov ob 00:00:00.000“, tega ne bi smeli početi. V smislu, ne bi smeli dovoliti takega primera. Ampak, če se je tako zgodilo, uporabite možnost z razširjenim indeksom.

Izvor: habr.com