Antipatterns PostgreSQL: JOIN-uri și OR-uri dăunătoare

Atenție la operațiunile care aduc tampon...
Folosind o interogare mică ca exemplu, să ne uităm la câteva abordări universale pentru optimizarea interogărilor în PostgreSQL. Dacă le folosești sau nu, depinde de tine, dar merită să știi despre ele.

În unele versiuni ulterioare ale PG situația se poate schimba pe măsură ce planificatorul devine mai inteligent, dar pentru 9.4/9.6 arată aproximativ la fel, ca în exemplele de aici.

Să luăm o cerere foarte reală:

SELECT
  TRUE
FROM
  "Документ" d
INNER JOIN
  "ДокументРасширение" doc_ex
    USING("@Документ")
INNER JOIN
  "ТипДокумента" t_doc ON
    t_doc."@ТипДокумента" = d."ТипДокумента"
WHERE
  (d."Лицо3" = 19091 or d."Сотрудник" = 19091) AND
  d."$Черновик" IS NULL AND
  d."Удален" IS NOT TRUE AND
  doc_ex."Состояние"[1] IS TRUE AND
  t_doc."ТипДокумента" = 'ПланРабот'
LIMIT 1;

despre numele tabelelor și câmpurilorNumele „rusești” ale câmpurilor și tabelelor pot fi tratate diferit, dar aceasta este o chestiune de gust. Deoarece aici la Tensor nu există dezvoltatori străini, iar PostgreSQL ne permite să dăm nume chiar și în hieroglife, dacă acestea cuprinse între ghilimele, atunci preferăm să numim obiectele fără ambiguitate și clar, astfel încât să nu existe discrepanțe.
Să ne uităm la planul rezultat:

[uita-te pe explic.tensor.ru]

144ms și aproape 53K buffere - adică peste 400MB de date! Și vom avea noroc dacă toate sunt în cache până la momentul solicitării noastre, altfel va dura de multe ori mai mult când citiți de pe disc.

Algoritmul este cel mai important!

Pentru a optimiza cumva orice cerere, trebuie mai întâi să înțelegeți ce ar trebui să facă.
Să lăsăm dezvoltarea structurii bazei de date în sine în afara domeniului de aplicare al acestui articol și să fim de acord că putem „în mod relativ ieftin” rescrie cererea și/sau rostogolește pe bază unele dintre lucrurile de care avem nevoie Indexuri.

Deci cererea:
— verifică existența a cel puțin unui document
- in starea de care avem nevoie si de un anumit tip
- unde autorul sau interpretul este angajatul de care avem nevoie

ÎNSCRIEȚI-VĂ + LIMITĂ 1

Destul de des este mai ușor pentru un dezvoltator să scrie o interogare în care un număr mare de tabele sunt mai întâi alăturate și apoi rămâne o singură înregistrare din întregul set. Dar mai ușor pentru dezvoltator nu înseamnă mai eficient pentru baza de date.
În cazul nostru au fost doar 3 tabele - și care este efectul...

Să scăpăm mai întâi de conexiunea cu tabelul „Tip de document” și, în același timp, să spunem bazei de date că tipul nostru de înregistrare este unic (știm asta, dar programatorul nu are încă idee):

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
  LIMIT 1
)
...
WHERE
  d."ТипДокумента" = (TABLE T)
...

Da, dacă tabelul/CTE constă dintr-un singur câmp dintr-o singură înregistrare, atunci în PG puteți chiar să scrieți așa, în loc de

d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)

Evaluare leneșă în interogările PostgreSQL

BitmapOr vs UNION

În unele cazuri, Bitmap Heap Scan ne va costa foarte mult - de exemplu, în situația noastră, când destul de multe înregistrări îndeplinesc condiția necesară. L-am prins pentru că Condiția SAU transformată în BitmapOr- operare in plan.
Să revenim la problema inițială - trebuie să găsim o înregistrare corespunzătoare la oricine din condiții - adică nu este nevoie să căutați toate înregistrările de 59K în ambele condiții. Există o modalitate de a rezolva o condiție și mergi la al doilea numai când nu s-a găsit nimic în primul. Următorul design ne va ajuta:

(
  SELECT
    ...
  LIMIT 1
)
UNION ALL
(
  SELECT
    ...
  LIMIT 1
)
LIMIT 1

LIMITĂ „Externă” 1 asigură că căutarea se încheie atunci când este găsită prima înregistrare. Și dacă este deja găsit în primul bloc, al doilea bloc nu va fi executat (niciodată executată în respect față de).

„Ascunderea condițiilor dificile sub CASE”

Există un moment extrem de incomod în interogarea inițială - verificarea stării față de tabelul aferent „DocumentExtension”. Indiferent de adevărul altor condiții din expresie (de exemplu, d. „Șters” NU ESTE ADEVĂRAT), această conexiune este întotdeauna executată și „costează resurse”. Mai mult sau mai puțin din ele vor fi cheltuite - depinde de dimensiunea acestui tabel.
Dar puteți modifica interogarea astfel încât căutarea unei înregistrări aferente să aibă loc numai atunci când este cu adevărat necesar:

SELECT
  ...
FROM
  "Документ" d
WHERE
  ... /*index cond*/ AND
  CASE
    WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
      SELECT
        "Состояние"[1] IS TRUE
      FROM
        "ДокументРасширение"
      WHERE
        "@Документ" = d."@Документ"
    )
  END

O dată de la tabelul legat la noi niciunul dintre câmpuri nu este necesar pentru rezultat, atunci avem posibilitatea de a transforma JOIN într-o condiție pe o subinterogare.
Să lăsăm câmpurile indexate „în afara parantezelor CASE”, să adăugăm condiții simple de la înregistrare la blocul WHEN - iar acum interogarea „grea” este executată doar la trecerea la THEN.

Numele meu de familie este "Total"

Colectăm interogarea rezultată cu toate mecanismele descrise mai sus:

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
)
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("Лицо3", "ТипДокумента") = (19091, (TABLE T)) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
UNION ALL
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("ТипДокумента", "Сотрудник") = ((TABLE T), 19091) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
LIMIT 1;

Ajustarea [la] indici

Un ochi instruit a observat că condițiile indexate din subblocurile UNION sunt ușor diferite - asta pentru că avem deja indecși potriviți pe tabel. Și dacă nu ar exista, ar merita să le creați: Document(Person3, DocumentType) и Document (Tip document, angajat).
despre ordinea câmpurilor în condiții ROWDin punctul de vedere al planificatorului, desigur, poți scrie (A, B) = (constA, constB)și (B, A) = (constB, constA). Dar la înregistrare în ordinea câmpurilor din index, o astfel de solicitare este pur și simplu mai convenabilă pentru a depana mai târziu.
Ce este în plan?

[uita-te pe explic.tensor.ru]

Din pacate am avut ghinion si nu s-a gasit nimic in primul bloc UNION, asa ca al doilea tot a fost executat. Dar chiar și așa - numai 0.037 ms și 11 tampon!
Am accelerat cererea și am redus pomparea datelor în memorie de câteva mii de ori, folosind tehnici destul de simple - un rezultat bun cu puțin copy-paste. 🙂

Sursa: www.habr.com