PostgreSQL Antipatterns: ການເຂົ້າຮ່ວມທີ່ເປັນອັນຕະລາຍ ແລະ ORs

ລະ​ວັງ​ການ​ດໍາ​ເນີນ​ງານ​ທີ່​ນໍາ​ເອົາ buffers ...
ການນໍາໃຊ້ການສອບຖາມຂະຫນາດນ້ອຍເປັນຕົວຢ່າງ, ໃຫ້ເບິ່ງວິທີການທົ່ວໄປບາງຢ່າງເພື່ອເພີ່ມປະສິດທິພາບການສອບຖາມໃນ PostgreSQL. ບໍ່ວ່າເຈົ້າຈະໃຊ້ພວກມັນຫຼືບໍ່ແມ່ນຂຶ້ນກັບເຈົ້າ, ແຕ່ມັນຄຸ້ມຄ່າທີ່ຈະຮູ້ກ່ຽວກັບພວກມັນ.

ໃນບາງຮຸ່ນຕໍ່ມາຂອງ PG ສະຖານະການອາດຈະປ່ຽນແປງຍ້ອນວ່າຕົວກໍານົດເວລາຈະສະຫລາດກວ່າ, ແຕ່ສໍາລັບ 9.4/9.6 ມັນເບິ່ງຄືວ່າຄ້າຍຄືກັນ, ໃນຕົວຢ່າງທີ່ນີ້.

ຂໍໃຫ້ເອົາຄໍາຮ້ອງຂໍທີ່ແທ້ຈິງຫຼາຍ:

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;

ກ່ຽວກັບຕາຕະລາງແລະຊື່ພາກສະຫນາມຊື່ "ພາສາລັດເຊຍ" ຂອງທົ່ງນາແລະຕາຕະລາງສາມາດປະຕິບັດແຕກຕ່າງກັນ, ແຕ່ນີ້ແມ່ນເລື່ອງຂອງລົດຊາດ. ເນື່ອງຈາກວ່າ ທີ່ນີ້ທີ່ Tensor ບໍ່ມີນັກພັດທະນາຕ່າງປະເທດ, ແລະ PostgreSQL ອະນຸຍາດໃຫ້ພວກເຮົາຕັ້ງຊື່ເຖິງແມ່ນວ່າໃນ hieroglyphs, ຖ້າພວກເຂົາ ຫຸ້ມຢູ່ໃນວົງຢືມຫຼັງຈາກນັ້ນ, ພວກເຮົາມັກຕັ້ງຊື່ວັດຖຸຢ່າງບໍ່ຊັດເຈນ ແລະຊັດເຈນເພື່ອບໍ່ໃຫ້ມີຄວາມແຕກຕ່າງ.
ໃຫ້​ເບິ່ງ​ໃນ​ແຜນ​ການ​ທີ່​ໄດ້​ຮັບ​:

[ເບິ່ງທີ່ອະທິບາຍ.tensor.ru]

144ms ແລະເກືອບ 53K buffers - ນັ້ນ​ແມ່ນ​ຫຼາຍ​ກ​່​ວາ 400MB ຂອງ​ຂໍ້​ມູນ​! ແລະພວກເຮົາຈະໂຊກດີຖ້າພວກເຂົາທັງຫມົດຢູ່ໃນແຄດໃນເວລາທີ່ຮ້ອງຂໍຂອງພວກເຮົາ, ຖ້າບໍ່ດັ່ງນັ້ນມັນຈະໃຊ້ເວລາຫຼາຍຄັ້ງເມື່ອອ່ານຈາກແຜ່ນ.

ສູດການຄິດໄລ່ແມ່ນສໍາຄັນທີ່ສຸດ!

ເພື່ອເພີ່ມປະສິດທິພາບການຮ້ອງຂໍໃດໆ, ກ່ອນອື່ນ ໝົດ ທ່ານຕ້ອງເຂົ້າໃຈສິ່ງທີ່ມັນຄວນເຮັດ.
ປ່ອຍໃຫ້ການພັດທະນາໂຄງສ້າງຖານຂໍ້ມູນຂອງຕົນເອງຢູ່ນອກຂອບເຂດຂອງບົດຄວາມນີ້ສໍາລັບໃນປັດຈຸບັນ, ແລະຕົກລົງເຫັນດີວ່າພວກເຮົາສາມາດຂ້ອນຂ້າງ "ລາຄາຖືກ" ຂຽນຄືນຄໍາຮ້ອງຂໍ ແລະ/ຫຼືມ້ວນໃສ່ພື້ນຖານບາງສິ່ງທີ່ພວກເຮົາຕ້ອງການ ດັດຊະນີ.

ດັ່ງ​ນັ້ນ​ຄໍາ​ຮ້ອງ​ສະ​ຫມັກ​:
— ກວດເບິ່ງການມີຢູ່ຂອງເອກະສານຢ່າງໜ້ອຍບາງອັນ
- ໃນເງື່ອນໄຂທີ່ພວກເຮົາຕ້ອງການແລະປະເພດໃດຫນຶ່ງ
- ບ່ອນທີ່ຜູ້ຂຽນຫຼືນັກສະແດງແມ່ນພະນັກງານທີ່ພວກເຮົາຕ້ອງການ

ເຂົ້າຮ່ວມ + ຈຳກັດ 1

ເລື້ອຍໆມັນງ່າຍກວ່າສໍາລັບນັກພັດທະນາທີ່ຈະຂຽນຄໍາຖາມທີ່ມີຈໍານວນຕາຕະລາງຈໍານວນຫລາຍທີ່ເຂົ້າຮ່ວມຄັ້ງທໍາອິດ, ແລະຫຼັງຈາກນັ້ນມີພຽງແຕ່ບັນທຶກດຽວຈາກຊຸດທັງຫມົດນີ້. ແຕ່ງ່າຍກວ່າສໍາລັບນັກພັດທະນາບໍ່ໄດ້ຫມາຍຄວາມວ່າມີປະສິດທິພາບຫຼາຍສໍາລັບຖານຂໍ້ມູນ.
ໃນກໍລະນີຂອງພວກເຮົາມີພຽງແຕ່ 3 ຕາຕະລາງ - ແລະຜົນກະທົບແມ່ນຫຍັງ ...

ທໍາອິດໃຫ້ພວກເຮົາກໍາຈັດການເຊື່ອມຕໍ່ກັບຕາຕະລາງ "ປະເພດເອກະສານ", ແລະໃນເວລາດຽວກັນບອກຖານຂໍ້ມູນວ່າ. ບັນທຶກປະເພດຂອງພວກເຮົາແມ່ນເປັນເອກະລັກ (ພວກເຮົາຮູ້ເລື່ອງນີ້, ແຕ່ຜູ້ກໍານົດເວລາຍັງບໍ່ທັນມີຄວາມຄິດ):

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

ແມ່ນແລ້ວ, ຖ້າຕາຕະລາງ / CTE ປະກອບດ້ວຍພາກສະຫນາມດຽວຂອງບັນທຶກດຽວ, ຫຼັງຈາກນັ້ນໃນ PG ທ່ານສາມາດຂຽນແບບນີ້, ແທນທີ່ຈະເປັນ.

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

ການປະເມີນຂີ້ກຽດໃນການສອບຖາມ PostgreSQL

BitmapOr ທຽບກັບ UNION

ໃນບາງກໍລະນີ, Bitmap Heap Scan ຈະເຮັດໃຫ້ພວກເຮົາມີຄ່າໃຊ້ຈ່າຍຫຼາຍ - ຕົວຢ່າງ, ໃນສະຖານະການຂອງພວກເຮົາ, ໃນເວລາທີ່ບັນທຶກຂ້ອນຂ້າງຫຼາຍຕອບສະຫນອງເງື່ອນໄຂທີ່ຕ້ອງການ. ພວກເຮົາໄດ້ຮັບມັນເພາະວ່າ OR ເງື່ອນໄຂກາຍເປັນ BitmapOr- ການ​ດໍາ​ເນີນ​ງານ​ໃນ​ແຜນ​ການ​.
ໃຫ້ກັບຄືນໄປຫາບັນຫາຕົ້ນສະບັບ - ພວກເຮົາຈໍາເປັນຕ້ອງຊອກຫາບັນທຶກທີ່ສອດຄ້ອງກັນ ຜູ້ໃດ ຈາກເງື່ອນໄຂ - ນັ້ນແມ່ນ, ບໍ່ຈໍາເປັນຕ້ອງຄົ້ນຫາບັນທຶກ 59K ທັງຫມົດພາຍໃຕ້ເງື່ອນໄຂທັງສອງ. ມີວິທີການເຮັດວຽກອອກເງື່ອນໄຂຫນຶ່ງ, ແລະ ໄປທີສອງພຽງແຕ່ໃນເວລາທີ່ບໍ່ມີຫຍັງພົບເຫັນຢູ່ໃນຄັ້ງທໍາອິດ. ການອອກແບບຕໍ່ໄປນີ້ຈະຊ່ວຍໃຫ້ພວກເຮົາ:

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

"ພາຍນອກ" LIMIT 1 ຮັບປະກັນວ່າການຄົ້ນຫາສິ້ນສຸດລົງເມື່ອບັນທຶກທໍາອິດຖືກພົບເຫັນ. ແລະຖ້າຫາກວ່າມັນໄດ້ຖືກພົບເຫັນຢູ່ໃນຕັນທໍາອິດ, ຕັນທີສອງຈະບໍ່ຖືກປະຕິບັດ (ບໍ່​ເຄີຍ​ປະ​ຕິ​ບັດ​ ກ່ຽວກັບ).

"ເຊື່ອງເງື່ອນໄຂທີ່ຫຍຸ້ງຍາກພາຍໃຕ້ກໍລະນີ"

ມີຊ່ວງເວລາທີ່ບໍ່ສະດວກທີ່ສຸດໃນການສອບຖາມຕົ້ນສະບັບ - ການກວດສອບສະຖານະຕໍ່ກັບຕາຕະລາງທີ່ກ່ຽວຂ້ອງ “DocumentExtension”. ໂດຍບໍ່ຄໍານຶງເຖິງຄວາມຈິງຂອງເງື່ອນໄຂອື່ນໆໃນການສະແດງອອກ (ຕົວຢ່າງ, d.“ລຶບແລ້ວ” ບໍ່ຖືກຕ້ອງ), ການເຊື່ອມຕໍ່ນີ້ຖືກປະຕິບັດສະເຫມີແລະ "ຊັບພະຍາກອນຄ່າໃຊ້ຈ່າຍ". ຫຼາຍຫຼືຫນ້ອຍຂອງພວກເຂົາຈະຖືກໃຊ້ - ແມ່ນຂຶ້ນກັບຂະຫນາດຂອງຕາຕະລາງນີ້.
ແຕ່ທ່ານສາມາດດັດແປງການສອບຖາມເພື່ອໃຫ້ການຄົ້ນຫາບັນທຶກທີ່ກ່ຽວຂ້ອງເກີດຂື້ນພຽງແຕ່ເມື່ອມີຄວາມຈໍາເປັນແທ້ໆ:

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

ເມື່ອຈາກຕາຕະລາງທີ່ເຊື່ອມຕໍ່ກັບພວກເຮົາ ບໍ່ມີຊ່ອງຂໍ້ມູນໃດທີ່ຕ້ອງການສໍາລັບຜົນໄດ້ຮັບ, ຫຼັງຈາກນັ້ນພວກເຮົາມີໂອກາດທີ່ຈະປ່ຽນ JOIN ເຂົ້າໄປໃນເງື່ອນໄຂໃນການສອບຖາມຍ່ອຍ.
ໃຫ້ອອກຈາກຊ່ອງທີ່ຖືກດັດສະນີ "ນອກວົງເລັບກໍລະນີ", ເພີ່ມເງື່ອນໄຂງ່າຍໆຈາກບັນທຶກໃສ່ເມື່ອບລັອກ - ແລະຕອນນີ້ການສອບຖາມ "ຫນັກ" ຖືກປະຕິບັດພຽງແຕ່ເມື່ອຜ່ານໄປຫາຫຼັງຈາກນັ້ນ.

ນາມສະກຸນຂອງຂ້ອຍແມ່ນ "ລວມ"

ພວກເຮົາເກັບກໍາການສອບຖາມຜົນໄດ້ຮັບກັບກົນໄກທັງຫມົດທີ່ອະທິບາຍຂ້າງເທິງນີ້:

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;

ປັບ [ກັບ] indexes

ຕາທີ່ໄດ້ຮັບການຝຶກອົບຮົມສັງເກດເຫັນວ່າເງື່ອນໄຂທີ່ຖືກດັດສະນີຢູ່ໃນບລັອກຍ່ອຍຂອງ UNION ແມ່ນແຕກຕ່າງກັນເລັກນ້ອຍ - ນີ້ແມ່ນຍ້ອນວ່າພວກເຮົາມີດັດສະນີທີ່ເຫມາະສົມຢູ່ໃນຕາຕະລາງແລ້ວ. ແລະຖ້າພວກເຂົາບໍ່ມີ, ມັນຈະຄຸ້ມຄ່າໃນການສ້າງ: ເອກະສານ(ບຸກຄົນ 3, ປະເພດເອກະສານ) и ເອກະສານ(ປະເພດເອກະສານ, ພະນັກງານ).
ກ່ຽວກັບລໍາດັບຂອງທົ່ງນາໃນເງື່ອນໄຂ ROWຈາກທັດສະນະຂອງຜູ້ວາງແຜນ, ແນ່ນອນ, ທ່ານສາມາດຂຽນ (A, B) = (constA, constB)ແລະ (B, A) = (constB, constA). ແຕ່ເວລາບັນທຶກ ໃນລໍາດັບຂອງທົ່ງນາໃນດັດຊະນີ, ການຮ້ອງຂໍດັ່ງກ່າວແມ່ນສະດວກກວ່າທີ່ຈະດີບັກໃນພາຍຫຼັງ.
ແມ່ນຫຍັງຢູ່ໃນແຜນການ?

[ເບິ່ງທີ່ອະທິບາຍ.tensor.ru]

ແຕ່ຫນ້າເສຍດາຍ, ພວກເຮົາໂຊກບໍ່ດີແລະບໍ່ມີຫຍັງຖືກພົບເຫັນຢູ່ໃນຕັນ UNION ທໍາອິດ, ດັ່ງນັ້ນອັນທີສອງຍັງຖືກປະຕິບັດ. ແຕ່ເຖິງແມ່ນວ່າ - ເທົ່ານັ້ນ 0.037ms ແລະ 11 buffers!
ພວກເຮົາໄດ້ເລັ່ງການຮ້ອງຂໍແລະຫຼຸດຜ່ອນການສູບຂໍ້ມູນໃນຫນ່ວຍຄວາມຈໍາ ຫຼາຍພັນເທື່ອ, ການນໍາໃຊ້ເຕັກນິກທີ່ງ່າຍດາຍພົບທົ່ວໄປ - ຜົນໄດ້ຮັບທີ່ດີກັບສໍາເນົາເລັກນ້ອຍ. 🙂

ແຫຼ່ງຂໍ້ມູນ: www.habr.com