PostgreSQL antipatterns: "Faqat bitta bo'lishi kerak!"

SQLda siz "qanday" bajarilishi kerakligini emas, balki "nimaga" erishmoqchi ekanligingizni tasvirlaysiz. Shu sababli, SQL so'rovlarini "eshitilsa, qanday yoziladi" uslubida ishlab chiqish muammosi sharafli o'rinni egallaydi. SQLda shartlarni hisoblash xususiyatlari.

Bugun juda oddiy misollardan foydalanib, keling, bu foydalanish kontekstida nimaga olib kelishi mumkinligini ko'rib chiqaylik GROUP/DISTINCT и LIMIT ular bilan.

Endi, agar siz so'rovda yozgan bo'lsangiz "Avval ushbu belgilarni ulang, so'ngra barcha dublikatlarni tashlang, faqat bitta qolishi kerak har bir kalit uchun nusxa ko'chirish" - ulanish umuman kerak bo'lmasa ham, u aynan shunday ishlaydi.

Va ba'zan siz omadlisiz va u "shunchaki ishlaydi", ba'zida u ishlashga yoqimsiz ta'sir ko'rsatadi va ba'zida ishlab chiquvchi nuqtai nazaridan mutlaqo kutilmagan effektlarni beradi.

Xo'sh, ehtimol unchalik ajoyib emas, lekin ...

"Shirin juftlik": QO'SHILING + DISTINCT

SELECT DISTINCT
  X.*
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
WHERE
  Y.bool_condition;

Ular nimani xohlashlari aniq bo'lar edi bajarilgan shart bilan bog'liq bo'lgan Y yozuvlari mavjud bo'lgan X yozuvlarini tanlang. orqali so'rov yozdi JOIN - bir necha marta pk qiymatlarini oldi (Y-da qancha mos yozuvlar paydo bo'ldi). Qanday qilib olib tashlash mumkin? Albatta DISTINCT!

Bu, ayniqsa, har bir X-yozuv uchun bir necha yuzta tegishli Y-yozuvlar mavjud bo'lsa, va keyin ularning dublikatlari qahramonlik bilan o'chirilganida, bu "quvnoq".

Qanday tuzatish kerak? Boshlash uchun muammoni o'zgartirish mumkinligini tushunib oling “Y-da bajarilgan shart bilan bog‘liq bo‘lgan X yozuvlarini tanlang” - Axir, bizga Y-rekordning o'zidan hech narsa kerak emas.

Oʻrnatilgan EXISTS

SELECT
  *
FROM
  X
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  );

PostgreSQL-ning ba'zi versiyalari EXISTS-da birinchi paydo bo'lgan yozuvni topish kifoya, eskisi esa yo'qligini tushunadi. Shuning uchun men har doim ko'rsatishni afzal ko'raman LIMIT 1 ichida EXISTS.

YONAL QO'SHILISh

SELECT
  X.*
FROM
  X
, LATERAL (
    SELECT
      Y.*
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  ) Y
WHERE
  Y IS DISTINCT FROM NULL;

Xuddi shu parametr, agar kerak bo'lsa, topilgan bog'langan Y-yozuvdan ba'zi ma'lumotlarni darhol qaytarishga imkon beradi. Shunga o'xshash variant maqolada muhokama qilinadi "PostgreSQL antipatterns: noyob rekord JOINning o'rtasiga etadi".

“Nega koʻproq toʻlash kerak”: DISTINCT [ON] + LIMIT 1

Bunday so'rovlarni o'zgartirishning qo'shimcha afzalligi, agar ulardan faqat bittasi yoki bir nechtasi kerak bo'lsa, quyidagi holatda bo'lgani kabi, yozuvlarni qidirishni osongina cheklash qobiliyatidir:

SELECT DISTINCT ON(X.pk)
  *
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Endi biz so'rovni o'qiymiz va DBMS nima qilishni taklif qilishini tushunishga harakat qilamiz:

• belgilarni ulash
• X.pk tomonidan noyob
• qolgan yozuvlardan birini tanlang

Xo'sh, nima oldingiz? "Faqat bitta kirish" noyoblaridan - va agar biz noyob bo'lmaganlardan birini olsak, natija qandaydir tarzda o'zgaradimi?.. "Agar farq bo'lmasa, nima uchun ko'proq pul to'lash kerak?"

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда можно подсунуть подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Va aynan bir xil mavzu GROUP BY + LIMIT 1.

"Men so'rashim kerak": yashirin GROUP + LIMIT

Shunga o'xshash narsalar turlicha sodir bo'ladi bo'sh bo'lmagan tekshiruvlar so'rov davom etayotganda belgilar yoki CTElar:

...
CASE
  WHEN (
    SELECT
      count(*)
    FROM
      X
    LIMIT 1
  ) = 0 THEN ...

Agregat funktsiyalari (count/min/max/sum/...) aniq ko'rsatmalarsiz ham butun to'plamda muvaffaqiyatli bajariladi GROUP BY. Faqat bilan LIMIT ular juda do'stona emas.

Ishlab chiquvchi o'ylashi mumkin "Agar u erda yozuvlar bo'lsa, menga LIMITdan ko'p narsa kerak emas". Lekin bunday qilmang! Chunki baza uchun u:

• ular xohlagan narsani hisoblang barcha yozuvlarga ko'ra
• qancha so'rasa, shuncha qator bering

Maqsadli shartlarga qarab, quyidagi almashtirishlardan birini amalga oshirish maqsadga muvofiqdir:

• (count + LIMIT 1) = 0 haqida NOT EXISTS(LIMIT 1)
• (count + LIMIT 1) > 0 haqida EXISTS(LIMIT 1)
• count >= N haqida (SELECT count(*) FROM (... LIMIT N))

"Gramlarda qancha tortish kerak": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Sodda ishlab chiquvchi so'rov bajarilishini to'xtatishiga chin dildan ishonishi mumkin. biz duch kelgan birinchi turli qiymatlarning 1 dollarini topishimiz bilanoq.

Kelajakda bu yangi tugun tufayli ishlashi mumkin va ishlaydi Index Skip Scan, uni amalga oshirish hozirda ishlab chiqilmoqda, ammo hali yo'q.

Hozircha birinchi barcha yozuvlar olinadi, noyobdir va faqat ulardan so'ralgan summa qaytariladi. Agar biz shunga o'xshash narsani xohlasak, ayniqsa achinarli $ 1 = 4, va jadvalda yuz minglab yozuvlar mavjud ...

Bekorga xafa bo'lmaslik uchun keling, rekursiv so'rovdan foydalanamiz PostgreSQL Wiki-dan "DISTINCT kambag'allar uchun":

Manba: www.habr.com