PostgreSQL Antipatterns: JOIN และ OR ที่เป็นอันตราย

ระวังการดำเนินการที่ทำให้เกิดบัฟเฟอร์...
ลองใช้แบบสอบถามขนาดเล็กเป็นตัวอย่าง มาดูแนวทางสากลบางประการในการเพิ่มประสิทธิภาพแบบสอบถามใน 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;

เกี่ยวกับชื่อตารางและฟิลด์ชื่อเขตข้อมูลและตาราง "รัสเซีย" สามารถปฏิบัติได้แตกต่างกัน แต่นี่เป็นเรื่องของรสนิยม เพราะว่า ที่นี่ที่เทนเซอร์ ไม่มีนักพัฒนาต่างชาติและ PostgreSQL อนุญาตให้เราตั้งชื่อได้แม้จะเป็นอักษรอียิปต์โบราณก็ตาม อยู่ในเครื่องหมายคำพูดจากนั้น เราต้องการตั้งชื่อวัตถุให้ชัดเจนและชัดเจนเพื่อไม่ให้เกิดความคลาดเคลื่อน
ลองดูแผนผลลัพธ์:

[ดูที่ expand.tensor.ru]

144ms และบัฟเฟอร์เกือบ 53K - นั่นคือข้อมูลมากกว่า 400MB! และเราจะโชคดีถ้าทั้งหมดอยู่ในแคชตามเวลาที่เราร้องขอ ไม่เช่นนั้นการอ่านจากดิสก์จะใช้เวลานานกว่านี้หลายเท่า

อัลกอริธึมเป็นสิ่งสำคัญที่สุด!

เพื่อเพิ่มประสิทธิภาพคำขอใดๆ คุณต้องเข้าใจก่อนว่าควรทำอย่างไร
ปล่อยให้การพัฒนาโครงสร้างฐานข้อมูลอยู่นอกขอบเขตของบทความนี้ไปก่อนและยอมรับว่าเราสามารถ "ถูก" ค่อนข้างได้ เขียนคำขอใหม่ และ/หรือกลิ้งลงบนฐานของบางอย่างที่เราต้องการ ดัชนี.

ดังนั้นคำขอ:
— ตรวจสอบการมีอยู่ของเอกสารอย่างน้อยบางส่วน
- ในสภาพที่เราต้องการและเป็นบางประเภท
- โดยที่ผู้เขียนหรือนักแสดงเป็นพนักงานที่เราต้องการ

เข้าร่วม + จำกัด 1

บ่อยครั้งนักพัฒนาจะเขียนคิวรีโดยที่มีการรวมตารางจำนวนมากในครั้งแรกได้ง่ายกว่า จากนั้นจึงเหลือเพียงเรกคอร์ดเดียวจากทั้งชุดนี้ แต่สิ่งที่ง่ายกว่าสำหรับนักพัฒนาไม่ได้หมายความว่าฐานข้อมูลจะมีประสิทธิภาพมากขึ้น
ในกรณีของเรามีเพียง 3 ตาราง - และผลกระทบคืออะไร...

ก่อนอื่นเรามากำจัดการเชื่อมต่อกับตาราง "Document Type" และในขณะเดียวกันก็บอกฐานข้อมูลว่า บันทึกประเภทของเรามีเอกลักษณ์เฉพาะตัว (เรารู้สิ่งนี้ แต่ผู้กำหนดเวลายังไม่มีความคิด):

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

ใช่ หากตาราง/CTE ประกอบด้วยฟิลด์เดียวจากระเบียนเดียว ใน PG คุณสามารถเขียนแบบนี้ได้ แทนที่จะเขียน

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

การประเมินแบบ Lazy ในการสืบค้น PostgreSQL

บิตแมปหรือเทียบกับ UNION

ในบางกรณี Bitmap Heap Scan จะทำให้เราต้องเสียค่าใช้จ่ายมาก - ตัวอย่างเช่น ในสถานการณ์ของเรา เมื่อมีบันทึกจำนวนมากตรงตามเงื่อนไขที่กำหนด เราได้รับมันเพราะว่า หรือเงื่อนไขกลายเป็น BitmapOr- การดำเนินงานตามแผน
กลับไปสู่ปัญหาเดิม - เราจำเป็นต้องค้นหาบันทึกที่เกี่ยวข้อง เพื่อใด ๆ จากเงื่อนไข - นั่นคือไม่จำเป็นต้องค้นหาบันทึก 59K ทั้งหมดภายใต้ทั้งสองเงื่อนไข มีวิธีแก้ไขเงื่อนไขหนึ่งข้อและ ไปครั้งที่สองก็ต่อเมื่อไม่พบสิ่งใดในครั้งแรก. การออกแบบต่อไปนี้จะช่วยเรา:

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

“ภายนอก” LIMIT 1 ช่วยให้มั่นใจว่าการค้นหาจะสิ้นสุดเมื่อพบบันทึกแรก และหากพบแล้วในบล็อกแรก บล็อกที่สองจะไม่ถูกดำเนินการ (ไม่เคยถูกประหารชีวิต ในความเคารพของ).

“ซ่อนเงื่อนไขที่ยากลำบากภายใต้ CASE”

มีช่วงเวลาที่ไม่สะดวกอย่างยิ่งในการสืบค้นดั้งเดิม - ตรวจสอบสถานะกับตาราง "DocumentExtension" ที่เกี่ยวข้อง โดยไม่คำนึงถึงความจริงของเงื่อนไขอื่น ๆ ในการแสดงออก (เช่น ง. “ถูกลบ” ไม่เป็นความจริง) การเชื่อมต่อนี้จะดำเนินการเสมอและ "ต้นทุนทรัพยากร" จะใช้เวลามากหรือน้อย - ขึ้นอยู่กับขนาดของตารางนี้
แต่คุณสามารถปรับเปลี่ยนแบบสอบถามเพื่อให้การค้นหาเรกคอร์ดที่เกี่ยวข้องเกิดขึ้นเฉพาะเมื่อจำเป็นจริงๆ เท่านั้น:

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 ให้เป็นเงื่อนไขในแบบสอบถามย่อย
ปล่อยให้ฟิลด์ที่จัดทำดัชนีไว้ "นอกวงเล็บ CASE" เพิ่มเงื่อนไขง่ายๆจากบันทึกลงในบล็อก WHEN - และตอนนี้แบบสอบถาม "หนัก" จะดำเนินการเฉพาะเมื่อส่งผ่านไปยัง THEN เท่านั้น

นามสกุลของฉันคือ "โทเทิล"

เรารวบรวมคำค้นหาผลลัพธ์พร้อมกับกลไกทั้งหมดที่อธิบายไว้ข้างต้น:

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;

การปรับดัชนี [เป็น]

สายตาที่ผ่านการฝึกอบรมสังเกตเห็นว่าเงื่อนไขที่จัดทำดัชนีในบล็อกย่อย UNION นั้นแตกต่างกันเล็กน้อย - นี่เป็นเพราะว่าเรามีดัชนีที่เหมาะสมอยู่บนโต๊ะอยู่แล้ว และถ้าไม่มีมันก็คุ้มค่าที่จะสร้าง: เอกสาร(Person3, DocumentType) и เอกสาร(ประเภทเอกสาร พนักงาน).
เกี่ยวกับลำดับของฟิลด์ในเงื่อนไข ROWจากมุมมองของผู้วางแผน แน่นอนว่าคุณสามารถเขียนได้ (ก, ข) = (คอนสตเอ, คอนสตบี)และ (B, A) = (constB, constA). แต่เมื่อมีการบันทึก ตามลำดับช่องในดัชนีคำขอดังกล่าวจะสะดวกกว่าในการแก้ไขข้อบกพร่องในภายหลัง
มีอะไรอยู่ในแผน?

[ดูที่ expand.tensor.ru]

น่าเสียดายที่เราโชคไม่ดีและไม่พบสิ่งใดในบล็อก UNION แรก ดังนั้นบล็อกที่สองจึงยังคงดำเนินการอยู่ แต่ถึงอย่างนั้นก็เท่านั้น 0.037ms และ 11 บัฟเฟอร์!
เราได้เร่งคำขอและลดการสูบข้อมูลในหน่วยความจำ หลายพันครั้งโดยใช้เทคนิคที่ค่อนข้างง่าย - ให้ผลลัพธ์ที่ดีด้วยการคัดลอกและวางเพียงเล็กน้อย 🙂

ที่มา: will.com