🥇PostgreSQL-teenpatrone: skadelike JOINs en OF's

Pasop vir bedrywighede wat buffers meebring...
Deur 'n klein navraag as voorbeeld te gebruik, kom ons kyk na 'n paar universele benaderings om navrae in PostgreSQL te optimaliseer. Of jy dit gebruik of nie, is aan jou, maar dit is die moeite werd om van hulle te weet.

In sommige daaropvolgende weergawes van PG kan die situasie verander soos die skeduleerder slimmer word, maar vir 9.4/9.6 lyk dit ongeveer dieselfde, soos in die voorbeelde hier.

Kom ons neem 'n baie werklike versoek:

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;

oor tabel- en veldnameDie "Russiese" name van velde en tabelle kan anders behandel word, maar dit is 'n kwessie van smaak. Omdat die hier by Tensor daar is geen buitelandse ontwikkelaars nie, en PostgreSQL laat ons toe om name selfs in hiërogliewe te gee, as hulle in aanhalingstekens ingesluit, dan verkies ons om voorwerpe ondubbelsinnig en duidelik te benoem sodat daar geen verskille is nie.
Kom ons kyk na die gevolglike plan:

[kyk na explain.tensor.ru]

144ms en byna 53K buffers - dit wil sê meer as 400MB data! En ons sal gelukkig wees as almal van hulle in die kas is teen die tyd van ons versoek, anders sal dit baie keer langer neem as dit van skyf gelees word.

Die algoritme is die belangrikste!

Om enige versoek op een of ander manier te optimaliseer, moet jy eers verstaan wat dit moet doen.
Kom ons laat die ontwikkeling van die databasisstruktuur self vir eers buite die bestek van hierdie artikel, en stem saam dat ons relatief "goedkoop" kan herskryf die versoek en/of rol op die basis van die dinge wat ons nodig het Indekse.

Dus die versoek:
— kontroleer die bestaan van ten minste een of ander dokument
- in die toestand wat ons benodig en van 'n sekere soort
- waar die skrywer of kunstenaar die werknemer is wat ons benodig

SLUIT AAN + LIMIET 1

Dikwels is dit makliker vir 'n ontwikkelaar om 'n navraag te skryf waar 'n groot aantal tabelle eers saamgevoeg word, en dan bly net een rekord van hierdie hele stel oor. Maar makliker vir die ontwikkelaar beteken nie meer doeltreffend vir die databasis nie.
In ons geval was daar net 3 tafels - en wat is die effek...

Kom ons raak eers ontslae van die verbinding met die "Dokumenttipe"-tabel, en vertel terselfdertyd vir die databasis dat ons tipe rekord is uniek (ons weet dit, maar die skeduleerder het nog geen idee nie):

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

Ja, as die tabel/CTE uit 'n enkele veld van 'n enkele rekord bestaan, dan kan jy in PG selfs so skryf, in plaas van

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

Luie evaluering in PostgreSQL-navrae

BitmapOr vs UNION

In sommige gevalle sal Bitmap Heap Scan ons baie kos - byvoorbeeld in ons situasie, wanneer heelwat rekords aan die vereiste voorwaarde voldoen. Ons het dit gekry omdat OF toestand verander in BitmapOr- werking in plan.
Kom ons keer terug na die oorspronklike probleem - ons moet 'n rekord vind wat ooreenstem aan enige van die voorwaardes - dit wil sê, dit is nie nodig om vir alle 59K-rekords onder beide toestande te soek nie. Daar is 'n manier om een voorwaarde uit te werk, en gaan net na die tweede wanneer niks in die eerste gevind is nie. Die volgende ontwerp sal ons help:

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

“Eksterne” LIMIET 1 verseker dat die soektog eindig wanneer die eerste rekord gevind word. En as dit reeds in die eerste blok gevind word, sal die tweede blok nie uitgevoer word nie (nooit tereggestel nie ten opsigte van).

"Versteek moeilike toestande onder CASE"

Daar is 'n uiters ongerieflike oomblik in die oorspronklike navraag - kontroleer die status teen die verwante tabel "DocumentExtension". Ongeag die waarheid van ander toestande in die uitdrukking (bv. d.“Geskrap” IS NIE WAAR NIE), word hierdie verbinding altyd uitgevoer en "kos hulpbronne". Min of meer daarvan sal bestee word - hang af van die grootte van hierdie tafel.
Maar jy kan die navraag verander sodat die soektog na 'n verwante rekord slegs plaasvind wanneer dit regtig nodig is:

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

Een keer van die gekoppelde tabel na ons geen van die velde is nodig vir die resultaat nie, dan het ons die geleentheid om JOIN in 'n toestand te verander op 'n subnavraag.
Kom ons laat die geïndekseerde velde "buite die CASE-hakies", voeg eenvoudige voorwaardes van die rekord by die WHEN-blok - en nou word die "swaar" navraag slegs uitgevoer wanneer na THEN oorgedra word.

My van is "Totaal"

Ons versamel die gevolglike navraag met al die meganika hierbo beskryf:

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;

Aanpassing [na] indekse

'n Geoefende oog het opgemerk dat die geïndekseerde toestande in die UNION-subblokke effens verskil - dit is omdat ons reeds geskikte indekse op die tafel het. En as hulle nie bestaan het nie, sou dit die moeite werd wees om te skep: Dokument (Persoon3, Dokumenttipe) и Dokument (Dokumenttipe, Werknemer).
oor die volgorde van velde in RY toestandeUit die beplanner se oogpunt kan jy natuurlik skryf (A, B) = (konstA, konstB)En (B, A) = (konstB, konstA). Maar wanneer die opname in die volgorde van die velde in die indeks, is so 'n versoek eenvoudig meer gerieflik om later te ontfout.
Wat is in die plan?

[kyk na explain.tensor.ru]

Ongelukkig was ons ongelukkig en niks is in die eerste UNION-blok gevind nie, so die tweede een is steeds tereggestel. Maar selfs so - net 0.037ms en 11 buffers!
Ons het die versoek bespoedig en die pomp van data in die geheue verminder 'n paar duisend keer, met redelik eenvoudige tegnieke - 'n goeie resultaat met 'n bietjie copy-paste. 🙂

Bron: will.com

PostgreSQL-teenpatrone: skadelike aansluitings en OF's