🥇PostgreSQL antipatternlari: zararli JOIN va OR

Buferlarni olib keladigan operatsiyalardan ehtiyot bo'ling ...
Misol sifatida kichik so'rovdan foydalanib, PostgreSQL-da so'rovlarni optimallashtirishning ba'zi universal yondashuvlarini ko'rib chiqaylik. Ulardan foydalanasizmi yoki yo'qmi, sizga bog'liq, lekin ular haqida bilishga arziydi.

PG ning ba'zi keyingi versiyalarida reja tuzuvchining aqlli bo'lishi bilan vaziyat o'zgarishi mumkin, ammo 9.4/9.6 uchun u bu erdagi misollardagi kabi taxminan bir xil ko'rinadi.

Keling, juda haqiqiy so'rovni olaylik:

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;

jadval va maydon nomlari haqidaMaydonlar va jadvallarning "ruscha" nomlariga boshqacha munosabatda bo'lish mumkin, ammo bu ta'mga bog'liq. Chunki bu erda Tensorda chet el ishlab chiquvchilari yo'q va PostgreSQL bizga hatto ierogliflarda ham nom berishga imkon beradi, agar ular bo'lsa. qo'shtirnoq ichiga olingan, keyin biz hech qanday nomuvofiqliklar bo'lmasligi uchun ob'ektlarni aniq va aniq nomlashni afzal ko'ramiz.
Keling, olingan rejani ko'rib chiqaylik:

[express.tensor.ru saytiga qarang]

144ms va deyarli 53K buferlar - ya'ni 400MB dan ortiq ma'lumot! Va agar ularning barchasi bizning so'rovimiz vaqtida keshda bo'lsa, biz omadli bo'lamiz, aks holda diskdan o'qish ko'p marta ko'proq vaqt talab etadi.

Algoritm eng muhimi!

Har qanday so'rovni qandaydir tarzda optimallashtirish uchun avval u nima qilish kerakligini tushunishingiz kerak.
Keling, hozircha ma'lumotlar bazasi tuzilishini ishlab chiqishni ushbu maqola doirasidan tashqarida qoldiraylik va biz nisbatan "arzon" bo'lishimiz mumkinligiga rozi bo'lamiz. so'rovni qayta yozing va/yoki bizga kerak bo'lgan ba'zi narsalarni poydevorga aylantiring indekslar.

Shunday qilib, so'rov:
— hech bo'lmaganda qandaydir hujjat mavjudligini tekshiradi
- bizga kerak bo'lgan holatda va ma'lum bir turdagi
- muallif yoki ijrochi bizga kerak bo'lgan xodim bo'lgan joyda

QO'SHILMA + LIMIT 1

Ko'pincha ishlab chiquvchiga so'rovni yozish osonroq bo'ladi, unda birinchi navbatda ko'p sonli jadvallar birlashtiriladi va keyin bu to'plamdan faqat bitta yozuv qoladi. Ammo ishlab chiquvchi uchun osonroq ma'lumotlar bazasi uchun samaraliroq degani emas.
Bizning holatlarimizda faqat 3 ta stol bor edi - va bu qanday ta'sir qiladi ...

Keling, avvalo "Hujjat turi" jadvali bilan bog'lanishdan xalos bo'laylik va shu bilan birga ma'lumotlar bazasiga aytamizki bizning rekordimiz noyobdir (biz buni bilamiz, lekin rejalashtiruvchi hali buni bilmaydi):

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

Ha, agar jadval/CTE bitta yozuvning bitta maydonidan iborat bo'lsa, PG da siz hatto shunday yozishingiz mumkin, o'rniga

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

PostgreSQL so'rovlarida dangasa baholash

BitmapOr va UNION

Ba'zi hollarda Bitmap Heap Scan bizga juda qimmatga tushadi - masalan, bizning sharoitimizda, juda ko'p yozuvlar talab qilinadigan shartlarga javob beradigan bo'lsa. Biz buni oldik, chunki OR sharti BitmapOr ga aylandi- reja bo'yicha ishlash.
Keling, asl muammoga qaytaylik - biz tegishli yozuvni topishimiz kerak har qanday kishiga shartlardan - ya'ni ikkala shartda ham barcha 59K yozuvlarni qidirishning hojati yo'q. Bitta shartni ishlab chiqishning bir usuli bor va birinchisida hech narsa topilmaganda ikkinchisiga o'ting. Quyidagi dizayn bizga yordam beradi:

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

"Tashqi" LIMIT 1 birinchi yozuv topilganda qidiruv tugashini ta'minlaydi. Va agar u birinchi blokda allaqachon topilgan bo'lsa, ikkinchi blok bajarilmaydi (hech qachon ijro etilmagan rejada).

"CASE bo'yicha qiyin sharoitlarni yashirish"

Dastlabki so'rovda juda noqulay vaziyat mavjud - "DocumentExtension" tegishli jadvaliga nisbatan holatni tekshirish. Ifodadagi boshqa shartlarning haqiqatidan qat'i nazar (masalan, d.“Oʻchirildi” TOʻGʻRI EMAS), bu ulanish har doim bajariladi va "resurslarni sarflaydi". Ulardan ko'p yoki kamroq sarflanadi - bu jadvalning hajmiga bog'liq.
Ammo siz so'rovni o'zgartirishingiz mumkin, shunda tegishli yozuvni qidirish faqat zarur bo'lganda amalga oshiriladi:

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

Bir marta bog'langan jadvaldan bizga natija uchun maydonlarning hech biri kerak emas, keyin biz JOINni pastki so'rovdagi shartga aylantirish imkoniyatiga egamiz.
Indekslangan maydonlarni "CASE qavslari tashqarisida" qoldiraylik, yozuvdan WHEN blokiga oddiy shartlarni qo'shing - va endi "og'ir" so'rov faqat THEN ga o'tishda bajariladi.

Mening familiyasim "jami"

Olingan so'rovni yuqorida tavsiflangan barcha mexanizmlar bilan to'playmiz:

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;

Indekslarni [to] sozlash

O'qitilgan ko'z UNION subbloklarida indekslangan shartlar biroz boshqacha ekanligini payqadi - buning sababi, bizda allaqachon jadvalda mos indekslar mavjud. Va agar ular mavjud bo'lmasa, uni yaratishga arziydi: Hujjat (3-shaxs, hujjat turi) и Hujjat (Hujjat turi, xodim).
ROW sharoitida maydonlarning tartibi haqidaRejalashtiruvchi nuqtai nazardan, albatta, siz yozishingiz mumkin (A, B) = (constA, constB)va (B, A) = (constB, constA). Lekin yozib olishda indeksdagi maydonlar tartibida, bunday so'rov keyinchalik disk raskadrovka qilish uchun qulayroqdir.
Rejada nima bor?

[express.tensor.ru saytiga qarang]

Afsuski, omadimiz kelmadi va birinchi UNION blokida hech narsa topilmadi, shuning uchun ikkinchisi hali ham ijro etildi. Ammo shunga qaramay - faqat 0.037 ms va 11 bufer!
Biz so'rovni tezlashtirdik va xotirada ma'lumotlarni uzatishni kamaytirdik bir necha ming marta, juda oddiy usullardan foydalanish - ozgina nusxa ko'chirish bilan yaxshi natija. 🙂

Manba: www.habr.com

PostgreSQL antipatternlari: zararli JOIN va OR