🥇PostgreSQL antipatternlari: SQLda holatni baholash

SQL C++ emas, JavaScript ham emas. Shuning uchun mantiqiy ifodalarni baholash boshqacha va bu umuman bir xil emas:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

PostgreSQL so'rovini bajarish rejasini optimallashtirishda ekvivalent shartlarni o'zboshimchalik bilan "qayta tartibga solish" mumkin, alohida yozuvlar uchun ulardan hech birini hisoblamang, qo'llaniladigan indeks holatiga murojaat qiling ... Qisqasi, eng oson yo'li - siz boshqara olmaydi ular bo'lish tartibi (va ular umuman hisoblab chiqiladimi) teng sharoitlar.

Shuning uchun, agar siz hali ham ustuvorlikni boshqarishni istasangiz, tizimli ravishda qilishingiz kerak bu shartlarni tengsiz qiling shartli bilan ifodalar и operatorlar.

Ma'lumotlar va ular bilan ishlash asosdir VLSI kompleksimiz, shuning uchun ular bo'yicha operatsiyalar nafaqat to'g'ri, balki samarali bajarilishi biz uchun juda muhimdir. Keling, iboralarni hisoblashda xatolarga yo'l qo'yilishi mumkin bo'lgan va ularning samaradorligini oshirishga arziydigan aniq misollarni ko'rib chiqaylik.

№ 0: RTFM

Boshlanmoqda hujjatlardan misol:

Baholash tartibi muhim bo'lsa, uni konstruktsiya bilan tuzatish mumkin CASE. Masalan, jumlada nolga bo'linmaslikning bu usuli WHERE ishonchsiz:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Xavfsiz variant:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Amaldagi qurilish CASE ifodani optimallashtirishdan himoya qiladi, shuning uchun uni faqat kerak bo'lganda ishlatish kerak.

№1: ishga tushirish holati

BEGIN
  IF cond(NEW.fld) AND EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;

Hamma narsa yaxshi ko'rinadi, lekin ... Hech kim investitsiya qilinganini va'da qilmaydi SELECT birinchi shart noto'g'ri bo'lsa, bajarilmaydi. bilan tuzating o'rnatilgan IF:

BEGIN
  IF cond(NEW.fld) THEN
    IF EXISTS(SELECT ...) THEN
      ...
    END IF;
  END IF;
  RETURN NEW;
END;

Endi diqqat bilan qaraylik - tetik funktsiyasining butun tanasi "o'ralgan" bo'lib chiqdi IF. Va bu shuni anglatadiki, bizni ushbu holatni foydalanish tartibidan olib tashlashga hech narsa to'sqinlik qilmaydi WHEN- shartlar:

BEGIN
  IF EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;
...
CREATE TRIGGER ...
  WHEN cond(NEW.fld);

Ushbu yondashuv, agar shart noto'g'ri bo'lsa, server resurslarini kafolat bilan saqlashga imkon beradi.

№2: YOKI/VA zanjiri

SELECT ... WHERE EXISTS(... A) OR EXISTS(... B)

Aks holda, ikkalasini ham olish mumkin EXISTS to'g'ri bo'ladi, lekin ikkalasi ham bajariladi.

Ammo agar biz ulardan biri "to'g'ri" ekanligini aniq bilsak, tez-tez (yoki "noto'g'ri" - uchun AND-zanjirlar) - ikkinchisi yana bir bor bajarilmasligi uchun qandaydir tarzda "uning ustuvorligini oshirish" mumkinmi?

Ma'lum bo'lishicha, bu mumkin - algoritmik yondashuv maqola mavzusiga yaqin PostgreSQL antipatternlari: Nodir kirish JOINning o'rtasiga etadi.

Keling, ikkala shartni ham "CASE ostida turaylik":

SELECT ...
WHERE
  CASE
    WHEN EXISTS(... A) THEN TRUE
    WHEN EXISTS(... B) THEN TRUE
  END

Bu holatda biz aniqlamadik ELSE-qiymat, ya'ni ikkala shart ham noto'g'ri bo'lsa CASE qaytadi NULL, deb talqin etiladi FALSE в WHERE- sharoitlar.

Ushbu misolni boshqa yo'l bilan birlashtirish mumkin - ta'mga va rangga:

SELECT ...
WHERE
  CASE
    WHEN NOT EXISTS(... A) THEN EXISTS(... B)
    ELSE TRUE
  END

№3: shartlarni qanday yozmaslik kerak

Biz ikki kun davomida ushbu qo'zg'atuvchining "g'alati" ishga tushishining sabablarini tahlil qildik - keling, nima uchun ekanligini bilib olaylik.

Manba:

IF( NEW."Документ_" is null or NEW."Документ_" = (select '"Комплект"'::regclass::oid) or NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)
     AND (   OLD."ДокументНашаОрганизация" <> NEW."ДокументНашаОрганизация"
          OR OLD."Удален" <> NEW."Удален"
          OR OLD."Дата" <> NEW."Дата"
          OR OLD."Время" <> NEW."Время"
          OR OLD."ЛицоСоздал" <> NEW."ЛицоСоздал" ) ) THEN ...

Muammo №1: Tengsizlik NULLni hisobga olmaydi

Faraz qilaylik, hamma narsa OLD- maydonlar muhim edi NULL. Nima bo'ladi?

SELECT NULL <> 1 OR NULL <> 2;
-- NULL

Va shartlarni ishlab chiqish nuqtai nazaridan NULL ekvivalent FALSE, yuqorida aytib o'tilganidek.

qaror: operatordan foydalaning IS DISTINCT FROM от ROW-operator, bir vaqtning o'zida butun yozuvlarni taqqoslash:

SELECT (NULL, NULL) IS DISTINCT FROM (1, 2);
-- TRUE

2-muammo: bir xil funktsiyani turlicha amalga oshirish

Keling, solishtiring:

NEW."Документ_" = (select '"Комплект"'::regclass::oid)
NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)

Nima uchun qo'shimcha investitsiyalar mavjud SELECT? Funktsiya to_regclass? Nega boshqacha...

Keling, tuzatamiz:

NEW."Документ_" = '"Комплект"'::regclass::oid
NEW."Документ_" = '"ДокументПоЗарплате"'::regclass::oid

№3 muammo: bool ustunligi

Keling, manbani formatlaymiz:

{... IS NULL} OR
{... Комплект} OR
{... ДокументПоЗарплате} AND
( {... неравенства} )

Voy ... Aslida, birinchi ikkita shartning birortasi haqiqatga to'g'ri kelgan taqdirda, butun shartga aylanadi. TRUE, tengsizliklarni hisobga olmaganda. Va bu biz xohlagan narsa emas.

Keling, tuzatamiz:

(
  {... IS NULL} OR
  {... Комплект} OR
  {... ДокументПоЗарплате}
) AND
( {... неравенства} )

Muammo №4 (kichik): bitta maydon uchun murakkab YOKI shart

Aslida, 3-raqamda muammolarga duch keldik, chunki uchta shart bor edi. Ammo ularning o'rniga siz mexanizmdan foydalanib, bittasi bilan olishingiz mumkin coalesce ... IN:

coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"')

Biz ham shundaymiz NULL "ushlash" va murakkab OR Qavslar bilan ovora bo'lishingiz shart emas.

jami

Keling, nima borligini aniqlaymiz:

IF (
  coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"') AND
  (
    OLD."ДокументНашаОрганизация"
  , OLD."Удален"
  , OLD."Дата"
  , OLD."Время"
  , OLD."ЛицоСоздал"
  ) IS DISTINCT FROM (
    NEW."ДокументНашаОрганизация"
  , NEW."Удален"
  , NEW."Дата"
  , NEW."Время"
  , NEW."ЛицоСоздал"
  )
) THEN ...

Va bu trigger funktsiyasidan faqat foydalanish mumkinligini hisobga olsak UPDATEmavjudligi sababli tetik OLD/NEW yuqori darajadagi holatda, keyin bu holat odatda olib tashlanishi mumkin WHEN-1-sonda ko'rsatilganidek, vaziyat ...

Manba: www.habr.com

PostgreSQL antipatternlari: SQLda vaziyatni baholash