PostgreSQL-Antiŝablonoj: malutilaj ALIGOJ kaj ORoj

Gardu vin kontraŭ operacioj, kiuj alportas bufrojn...
Uzante malgrandan demandon kiel ekzemplon, ni rigardu kelkajn universalajn alirojn al optimumigo de demandoj en PostgreSQL. Ĉu vi uzas ilin aŭ ne, dependas de vi, sed indas scii pri ili.

En kelkaj postaj versioj de PG la situacio povas ŝanĝiĝi kiam la planilo iĝas pli inteligenta, sed por 9.4/9.6 ĝi aspektas proksimume same, kiel en la ekzemploj ĉi tie.

Ni prenu tre realan peton:

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;

pri nomoj de tabeloj kaj kampojLa "rusaj" nomoj de kampoj kaj tabeloj povas esti traktataj malsame, sed ĉi tio estas demando de gusto. Ĉar la ĉi tie ĉe Tensor ne ekzistas eksterlandaj programistoj, kaj PostgreSQL permesas al ni doni nomojn eĉ en hieroglifoj, se ili enfermitaj inter citiloj, tiam ni preferas nomi objektojn malambigue kaj klare, por ke ne estu diferencoj.
Ni rigardu la rezultan planon:

[vidi ĉe explic.tensor.ru]

144ms kaj preskaŭ 53K bufroj - tio estas pli ol 400MB da datumoj! Kaj ni havos bonŝancon, se ĉiuj el ili estas en la kaŝmemoro ĝis la tempo de nia peto, alie ĝi daŭros multfoje pli longe kiam legado de disko.

La algoritmo estas plej grava!

Por iel optimumigi ajnan peton, vi unue devas kompreni, kion ĝi devas fari.
Ni lasu la evoluon de la datumbaza strukturo mem ekster la amplekso de ĉi tiu artikolo nuntempe, kaj konsentu, ke ni povas relative "malkoste" reverku la peton kaj/aŭ ruliĝu sur la bazon kelkajn el la aferoj, kiujn ni bezonas indicoj.

Do la peto:
— kontrolas la ekziston de almenaŭ iu dokumento
- en la kondiĉo, kiun ni bezonas kaj de certa tipo
- kie la aŭtoro aŭ prezentisto estas la dungito, kiun ni bezonas

ALIGI + LIMIGO 1

Sufiĉe ofte estas pli facile por programisto skribi demandon, kie granda nombro da tabeloj unue estas kunigitaj, kaj tiam nur unu registro restas de ĉi tiu tuta aro. Sed pli facila por la programisto ne signifas pli efika por la datumbazo.
En nia kazo estis nur 3 tabloj - kaj kia estas la efiko...

Ni unue forigu la konekton kun la tabelo "Dokumenta Tipo", kaj samtempe diru al la datumbazo tion nia tipo-rekordo estas unika (Ni scias ĉi tion, sed la planisto ankoraŭ ne havas ideon):

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

Jes, se la tabelo/CTE konsistas el ununura kampo de ununura registro, tiam en PG oni eĉ povas skribi tiel, anstataŭ

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

Maldiligenta taksado en PostgreSQL-demandoj

BitmapOr kontraŭ UNION

En iuj kazoj, Bitmap Heap Scan multe kostos al ni - ekzemple, en nia situacio, kiam sufiĉe multaj registroj plenumas la postulatan kondiĉon. Ni ricevis ĝin ĉar AŬ kondiĉo fariĝis BitmapOr- operacio en plano.
Ni revenu al la originala problemo - ni devas trovi rekordon respondan iu ajn de la kondiĉoj - tio estas, ne necesas serĉi ĉiujn 59K rekordojn sub ambaŭ kondiĉoj. Estas maniero ellabori unu kondiĉon, kaj iru al la dua nur kiam nenio troviĝis en la unua. La sekva dezajno helpos nin:

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

"Ekstera" LIMIT 1 certigas, ke la serĉo finiĝas kiam la unua rekordo estas trovita. Kaj se ĝi jam troviĝas en la unua bloko, la dua bloko ne estos ekzekutita (neniam ekzekutita koncerne).

"Kaŝi malfacilajn kondiĉojn sub CASE"

Estas ege maloportuna momento en la originala demando - kontroli la staton kontraŭ la rilata tabelo "DocumentExtension". Sendepende de la vero de aliaj kondiĉoj en la esprimo (ekzemple, d.“Forigita” NE ESTAS VERA), ĉi tiu konekto ĉiam estas efektivigita kaj "kostas rimedojn". Pli-malpli el ili estos elspezitaj - dependas de la grandeco de ĉi tiu tablo.
Sed vi povas modifi la demandon tiel ke la serĉo de rilata rekordo okazas nur kiam ĝi estas vere necesa:

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

Unufoje de la ligita tabelo al ni neniu el la kampoj estas bezonataj por la rezulto, tiam ni havas la ŝancon turni JOIN en kondiĉon en subdemando.
Ni lasu la indeksitajn kampojn "ekster la KAZAJ krampoj", aldonu simplajn kondiĉojn de la rekordo al la WHEN-bloko - kaj nun la "peza" demando estas efektivigita nur kiam oni pasas al THEN.

Mia familia nomo estas "Entute"

Ni kolektas la rezultan demandon kun ĉiuj mekanikoj priskribitaj supre:

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;

Alĝustigante [al] indeksoj

Trejnita okulo rimarkis, ke la indeksitaj kondiĉoj en la UNION-subblokoj estas iomete malsamaj - tio estas ĉar ni jam havas taŭgajn indeksojn sur la tablo. Kaj se ili ne ekzistus, indus krei: Dokumento (Person3, DocumentType) и Dokumento (Dokumento-Tipo, Dungito).
pri la ordo de kampoj en VICkondiĉojEl la vidpunkto de la planisto, kompreneble, vi povas skribi (A, B) = (konstA, konstB)kaj (B, A) = (konstB, konstA). Sed dum registrado en la ordo de la kampoj en la indekso, tia peto estas simple pli oportuna sencimigi poste.
Kio estas en la plano?

[vidi ĉe explic.tensor.ru]

Bedaŭrinde, ni estis malbonŝancaj kaj nenio estis trovita en la unua UNION-bloko, do la dua ankoraŭ estis ekzekutita. Sed eĉ tiel — nur 0.037ms kaj 11 bufroj!
Ni rapidigis la peton kaj reduktis datumpumpadon en memoro plurmil fojojn, uzante sufiĉe simplajn teknikojn - bona rezulto kun iom kopio-gluo. 🙂

fonto: www.habr.com