โปรโฮสต์ > บล็อก > การบริหาร > รูปแบบการป้องกัน PostgreSQL: CTE x CTE

รูปแบบการป้องกัน PostgreSQL: CTE x CTE

เนื่องจากสายงานของฉัน ฉันต้องรับมือกับสถานการณ์ที่นักพัฒนาเขียนคำขอและคิดว่า “ฐานนั้นฉลาด มันสามารถจัดการทุกอย่างได้ด้วยตัวเอง!«

ในบางกรณี (ส่วนหนึ่งมาจากความไม่รู้ความสามารถของฐานข้อมูล ส่วนหนึ่งมาจากการปรับให้เหมาะสมก่อนเวลาอันควร) แนวทางนี้นำไปสู่การปรากฏตัวของ "แฟรงเกนสไตน์"

ก่อนอื่น ฉันจะยกตัวอย่างคำขอดังกล่าว:

-- для каждой ключевой пары находим ассоциированные значения полей
WITH RECURSIVE cte_bind AS (
  SELECT DISTINCT ON (key_a, key_b)
    key_a a
  , key_b b
  , fld1 bind_fld1
  , fld2 bind_fld2
  FROM
    tbl
)
-- находим min/max значений для каждого первого ключа
, cte_max AS (
  SELECT
    a
  , max(bind_fld1) bind_fld1
  , min(bind_fld2) bind_fld2
  FROM
    cte_bind
  GROUP BY
    a
)
-- связываем по первому ключу ключевые пары и min/max-значения
, cte_a_bind AS (
  SELECT
    cte_bind.a
  , cte_bind.b
  , cte_max.bind_fld1
  , cte_max.bind_fld2
  FROM
    cte_bind
  INNER JOIN
    cte_max
      ON cte_max.a = cte_bind.a
)
SELECT * FROM cte_a_bind;

ในการประเมินคุณภาพของคำขออย่างมีนัยสำคัญ เรามาสร้างชุดข้อมูลที่กำหนดเองกัน:

CREATE TABLE tbl AS
SELECT
  (random() * 1000)::integer key_a
, (random() * 1000)::integer key_b
, (random() * 10000)::integer fld1
, (random() * 10000)::integer fld2
FROM
  generate_series(1, 10000);
CREATE INDEX ON tbl(key_a, key_b);

ปรากฎว่า การอ่านข้อมูลใช้เวลาน้อยกว่าหนึ่งในสี่ของเวลา การดำเนินการค้นหา:

รูปแบบการป้องกัน PostgreSQL: CTE x CTE[ดูที่ expand.tensor.ru]

โดยแยกมันออกเป็นชิ้นๆ

มาดูคำขอให้ละเอียดยิ่งขึ้นและสับสน:

  1. เหตุใดจึงมี WITH RECURSIVE ที่นี่หากไม่มี CTE แบบเรียกซ้ำ
  2. เหตุใดจึงจัดกลุ่มค่าต่ำสุด/สูงสุดใน CTE แยกกัน หากค่าเหล่านั้นเชื่อมโยงกับตัวอย่างดั้งเดิมอยู่แล้ว
    +25% เวลา
  3. เหตุใดจึงต้องใช้ 'SELECT * FROM' ที่ไม่มีเงื่อนไขในตอนท้ายเพื่อทำซ้ำ CTE ก่อนหน้า
    +14% เวลา

ในกรณีนี้ เราโชคดีมากที่เลือก Hash Join สำหรับการเชื่อมต่อ ไม่ใช่ Nested Loop เพราะตอนนั้นเราจะได้รับไม่เพียงแค่ CTE Scan pass เท่านั้น แต่ได้รับ 10!

เล็กน้อยเกี่ยวกับ CTE Scanที่นี่เราต้องจำไว้ว่า CTE Scan คล้ายกับ Seq Scan - นั่นคือไม่มีการจัดทำดัชนี แต่เป็นเพียงการค้นหาที่สมบูรณ์ซึ่งจะต้องมี 10K x 0.3ms = 3000ms สำหรับรอบโดย cte_max หรือ 1K x 1.5ms = 1500ms เมื่อวนซ้ำด้วย cte_bind!
จริงๆ แล้วคุณอยากได้ผลลัพธ์อะไร? ใช่ โดยปกติแล้ว นี่เป็นคำถามที่เกิดขึ้นในนาทีที่ 5 ของการวิเคราะห์ข้อความค้นหา "สามเรื่อง"

เราต้องการส่งออกคู่คีย์ที่ไม่ซ้ำกันแต่ละคู่ ต่ำสุด/สูงสุดจากกลุ่มโดย key_a.
ลองใช้มันเพื่อสิ่งนี้ ฟังก์ชั่นหน้าต่าง:

SELECT DISTINCT ON(key_a, key_b)
	key_a a
,	key_b b
,	max(fld1) OVER(w) bind_fld1
,	min(fld2) OVER(w) bind_fld2
FROM
	tbl
WINDOW
	w AS (PARTITION BY key_a);

รูปแบบการป้องกัน PostgreSQL: CTE x CTE
[ดูที่ expand.tensor.ru]

เนื่องจากการอ่านข้อมูลในทั้งสองตัวเลือกใช้เวลาประมาณ 4-5 มิลลิวินาทีเท่ากัน ดังนั้นเวลาทั้งหมดของเราจึงเพิ่มขึ้น -32% - นี่คือรูปแบบที่บริสุทธิ์ที่สุด โหลดถูกลบออกจาก CPU พื้นฐานหากคำขอดังกล่าวได้รับการดำเนินการบ่อยเพียงพอ

โดยทั่วไปคุณไม่ควรฝืนฐานให้ “ยกอันกลม กลิ้งอันสี่เหลี่ยม”

ที่มา: will.com

เพิ่มความคิดเห็น