🥇PostgreSQL Antipatterns: GABUNGAN lan OR sing mbebayani

Waspada karo operasi sing nggawa buffer ...
Nggunakake pitakon cilik minangka conto, ayo goleki sawetara pendekatan universal kanggo ngoptimalake pitakon ing PostgreSQL. Apa sampeyan nggunakake utawa ora, sampeyan kudu ngerti, nanging sampeyan kudu ngerti.

Ing sawetara versi sakteruse saka PG kahanan bisa ngganti minangka panjadwal dadi luwih pinter, nanging kanggo 9.4 / 9.6 katon kira-kira padha, kaya ing conto kene.

Ayo njaluk panjaluk sing 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;

babagan jeneng tabel lan lapanganJeneng kolom lan tabel "Rusia" bisa dianggep beda, nanging iki minangka masalah rasa. Amarga ing kene ing Tensor ora ana pangembang asing, lan PostgreSQL ngidini kita menehi jeneng sanajan ing hieroglif, yen padha katutup ing kuotasi, banjur kita luwih seneng menehi jeneng obyek kanthi jelas lan jelas supaya ora ana bedane.
Ayo katon ing rencana asil:

[deleng ing explain.tensor.ru]

144ms lan meh 53K buffer - yaiku, luwih saka 400MB data! Lan kita bakal begja yen kabeh mau ana ing cache nalika panyuwunan kita, yen ora bakal njupuk kaping pirang-pirang nalika diwaca saka disk.

Algoritma paling penting!

Supaya bisa ngoptimalake panjaluk apa wae, sampeyan kudu ngerti apa sing kudu ditindakake.
Ayo ninggalake pangembangan struktur database dhewe ing njaba ruang lingkup artikel iki kanggo saiki, lan setuju yen kita bisa relatif "murah" nulis maneh panjalukan lan / utawa muter menyang basa sawetara saka iku kita kudu indeks.

Dadi panyuwunan:
- mriksa ana ing paling sawetara document
- ing kondisi sing kita butuhake lan saka jinis tartamtu
- ing ngendi penulis utawa pemain minangka karyawan sing kita butuhake

NGGABUNGA + LIMIT 1

Cukup asring luwih gampang kanggo pangembang kanggo nulis pitakon ing ngendi akeh tabel pisanan digabung, banjur mung siji rekaman tetep saka kabeh set iki. Nanging luwih gampang kanggo pangembang ora ateges luwih efisien kanggo database.
Ing kasus kita mung ana 3 tabel - lan apa efeke ...

Ayo dhisik nyingkirake sambungan karo tabel "Jinis Dokumen", lan ing wektu sing padha ngandhani database sing rekaman jinis kita unik (kita ngerti iki, nanging panjadwal durung ngerti):

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

Ya, yen tabel / CTE kasusun saka kolom siji saka rekaman siji, banjur ing PG sampeyan malah bisa nulis kaya iki, tinimbang

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

Evaluasi males ing pitakon PostgreSQL

BitmapOr vs UNION

Ing sawetara kasus, Bitmap Heap Scan bakal larang regane - contone, ing kahanan kita, nalika akeh cathetan sing cocog karo kondisi sing dibutuhake. Kita entuk amarga Kondisi UTAWA dadi BitmapOr- operasi ing rencana.
Ayo bali menyang masalah asli - kita kudu nemokake rekaman sing cocog sapa wae saka kahanan - sing, ana ora perlu kanggo nelusuri kabeh 59K cathetan ing loro kondisi. Ana cara kanggo bisa metu siji kondisi, lan pindhah menyang kaloro mung nalika ora ketemu ing pisanan. Desain ing ngisor iki bakal mbantu kita:

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

LIMIT "Eksternal" 1 mesthekake yen panelusuran rampung nalika rekaman pisanan ditemokake. Lan yen wis ditemokake ing blok pisanan, blok kapindho ora bakal dieksekusi (ora tau dieksekusi kahurmatan marang).

"Ndhelikake kahanan sing angel ing CASE"

Ana momen sing ora trep ing pitakon asli - mriksa status ing tabel sing gegandhengan "DocumentExtension". Preduli saka bebener kahanan liyane ing ekspresi (contone, d.“Dibusak” ORA BENER), sambungan iki tansah kaleksanan lan "biaya sumber daya". Luwih utawa kurang saka wong-wong mau bakal ngginakaken - gumantung ing ukuran meja iki.
Nanging sampeyan bisa ngowahi pitakon supaya telusuran rekaman sing ana gandhengane mung kedadeyan yen pancen perlu:

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

Sawise saka tabel disambung kanggo kita ora ana lapangan sing dibutuhake kanggo asil, banjur kita duwe kesempatan kanggo nguripake JOIN menyang kondisi ing subquery a.
Ayo ninggalake kolom sing diindeks "ing njaba kurung CASE", tambahake kahanan sing prasaja saka rekaman menyang blok WHEN - lan saiki pitakon "abot" mung dieksekusi nalika ngliwati THEN.

Jeneng mburiku "Total"

Kita ngumpulake pitakon asil karo kabeh mekanika sing diterangake ing ndhuwur:

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;

Nyetel [kanggo] indeks

Mripat sing dilatih ngerteni manawa kahanan sing diindeks ing subblok UNION rada beda - iki amarga kita wis duwe indeks sing cocog ing meja. Lan yen ora ana, mesthine kudu digawe: Dokumen(Person3, DocumentType) и Dokumen (Tipe Dokumen, Karyawan).
babagan urutan kolom ing kahanan ROWSaka sudut pandang perencana, mesthine sampeyan bisa nulis (A, B) = (constA, constB)lan (B, A) = (constB, constA). Nanging nalika ngrekam ing urutan kolom ing indeks, panjalukan kuwi mung luwih trep kanggo debug mengko.
Apa ing rencana?

[deleng ing explain.tensor.ru]

Sayange, kita ora beruntung lan ora ana sing ditemokake ing blok UNION pisanan, mula sing nomer loro isih dieksekusi. Nanging sanajan - mung 0.037ms lan 11 buffer!
Kita wis nyepetake panjaluk kasebut lan nyuda pamompa data ing memori kaping pirang-pirang ewu, nggunakake Techniques cukup prasaja - asil apik karo sethitik copy-paste. 🙂

Source: www.habr.com

Antipattern PostgreSQL: GABUNGAN lan OR sing mbebayani