Antipatterns PostgreSQL: Gabung sareng OR ngabahayakeun

Waspada operasi anu mawa panyangga...
Ngagunakeun query leutik sabagé conto, hayu urang nempo sababaraha pendekatan universal pikeun optimizing queries di PostgreSQL. Naha anjeun nganggo aranjeunna atanapi henteu, terserah anjeun, tapi anjeun kedah terang ngeunaan aranjeunna.

Dina sababaraha vérsi saterusna PG kaayaan bisa robah jadi scheduler jadi smarter, tapi pikeun 9.4 / 9.6 Sigana mah sarua, sakumaha dina conto di dieu.

Hayu urang nyandak hiji pamundut pisan nyata:

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;

ngeunaan ngaran méja jeung widangNgaran "Rusia" tina widang jeung tabel bisa diolah béda, tapi ieu masalah rasa. Kusabab éta didieu di Tensor teu aya pamekar asing, sareng PostgreSQL ngamungkinkeun urang masihan nami bahkan dina hiéroglif, upami aranjeunna diapit ku kutipan, mangka urang leuwih milih ngaran objék unambiguously tur jelas ku kituna teu aya discrepancies.
Hayu urang nempo rencana hasilna:

[tingali dina explain.tensor.ru]

144ms sarta ampir 53K buffers - nyaeta, leuwih ti 400MB data! Sareng urang bakal untung upami sadayana aya dina cache dina waktos pamundut urang, upami henteu, éta bakal nyandak sababaraha kali langkung lami nalika dibaca tina disk.

Algoritma anu paling penting!

Pikeun kumaha waé ngaoptimalkeun pamundut naon waé, anjeun kedah ngartos heula naon anu kedah dilakukeun.
Hayu urang ninggalkeun ngembangkeun struktur database sorangan di luar ruang lingkup artikel ieu pikeun ayeuna, sarta satuju yén urang bisa rélatif "mirah" nulis ulang pamundut jeung / atawa gulung kana dasar sababaraha hal urang kudu indéks.

Jadi paménta:
- pariksa ayana sahenteuna sababaraha dokumén
- dina kaayaan anu urang butuhkeun sareng tina jinis anu tangtu
- dimana pangarang atanapi palaku mangrupikeun karyawan anu urang peryogikeun

JOIN + LIMIT 1

Rada sering eta leuwih gampang pikeun pamekar a nulis query dimana angka nu gede ngarupakeun tabel munggaran ngagabung, lajeng ngan hiji catetan tetep tina sakabéh set ieu. Tapi gampang pikeun pamekar henteu hartosna langkung efisien pikeun pangkalan data.
Dina kasus urang ngan aya 3 tabel - sareng naon pangaruhna ...

Hayu urang mimiti nyingkirkeun sambungan sareng tabel "Tipe Dokumén", sareng dina waktos anu sami nyarioskeun pangkalan data éta. catetan tipe urang téh unik (urang terang ieu, tapi scheduler teu acan gaduh ide):

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

Leres, upami tabel / CTE diwangun ku lapangan tunggal tina rékaman tunggal, maka di PG anjeun malah tiasa nyerat sapertos kieu, tibatan

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

Puguh evaluasi dina PostgreSQL queries

BitmapAtawa vs UNION

Dina sababaraha kasus, Bitmap Heap Scan bakal ngarugikeun urang pisan - contona, dina kaayaan urang, nalika seueur rékaman nyumponan kaayaan anu diperyogikeun. Urang meunang eta sabab kaayaan OR robah jadi BitmapOr- operasi dina rencana.
Hayu urang balik deui ka masalah aslina - urang kudu manggihan rékaman pakait saha wae ti kaayaan - nyaeta, teu perlu neangan sagala 59K rékaman dina duanana kaayaan. Aya cara pikeun dianggo kaluar hiji kaayaan, jeung balik ka kadua ngan lamun euweuh kapanggih dina kahiji. Desain handap bakal nulungan urang:

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

"Éksternal" LIMIT 1 mastikeun yén panéangan réngsé nalika catetan munggaran kapanggih. Sareng upami éta parantos dipendakan dina blok kahiji, blok kadua moal dieksekusi (pernah dieksekusi dina hormat).

"Nyumputkeun kaayaan susah dina CASE"

Aya hiji momen pisan pikaresepeun dina pamundut aslina - mariksa status ngalawan tabel patali "DocumentExtension". Henteu paduli kabeneran kaayaan sanés dina éksprési (contona, d.“Dihapus” TEU BENER), sambungan ieu salawasna dieksekusi jeung "biaya sumberdaya". Leuwih atawa kurang di antarana bakal spent - gumantung kana ukuran tabel ieu.
Tapi anjeun tiasa ngarobih pamundut supados milarian rékaman anu aya hubunganana ngan ukur nalika éta leres-leres diperyogikeun:

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

Sakali ti tabel numbu ka urang euweuh widang anu diperlukeun pikeun hasilna, mangka urang boga kasempetan pikeun ngahurungkeun JOIN kana kaayaan dina subquery a.
Hayu urang tinggalkeun widang anu indéks "di luar kurung CASE", tambahkeun kaayaan saderhana tina catetan ka blok WHEN - sareng ayeuna pamundut "beurat" ngan ukur dilaksanakeun nalika ngalangkungan THEN.

Ngaran tukang kuring "Total"

Kami ngumpulkeun pamundut anu hasilna sareng sadaya mékanika anu dijelaskeun di luhur:

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;

Nyaluyukeun [ka] indéks

Panon anu dilatih ningali yén kaayaan anu diindeks dina subblok UNION rada béda - ieu kusabab urang parantos gaduh indéks anu cocog dina méja. Sareng upami aranjeunna henteu aya, éta patut didamel: Dokumén(Person3, DocumentType) и Dokumén(DocumentType, Pagawé).
ngeunaan urutan widang dina kaayaan ROWTina sudut pandang anu ngarencanakeun, tangtosna, anjeun tiasa nyerat (A, B) = (constA, constB)jeung (B, A) = (constB, constA). Tapi nalika ngarekam dina urutan widang dina indéks, pamundut kitu téh saukur leuwih merenah pikeun debug engké.
Naon dina rencana?

[tingali dina explain.tensor.ru]

Hanjakal, kami sial jeung euweuh kapanggih dina blok UNION kahiji, jadi nu kadua masih dieksekusi. Tapi sanajan kitu - hijina 0.037ms jeung 11 panyangga!
Kami parantos nyepetkeun pamundut sareng ngirangan ngompa data dina mémori sababaraha rébu kali, ngagunakeun téknik anu cukup saderhana - hasil anu saé kalayan salin-témpél sakedik. 🙂

sumber: www.habr.com