SQL HowTo: เขียน while-loop โดยตรงในแบบสอบถาม หรือ "Elementary three-way"

งานค้นหาข้อมูลที่เกี่ยวข้องโดยชุดของคีย์เป็นระยะ ๆ จนกว่าเราจะได้จำนวนระเบียนทั้งหมดที่ต้องการ.

ตัวอย่างที่ "เหมือนจริง" ที่สุดคือการแสดง 20 ปัญหาที่เก่าแก่ที่สุด, รายการ ในรายชื่อพนักงาน (เช่น ภายในแผนกเดียวกัน) สำหรับ "แดชบอร์ด" การจัดการต่างๆ ที่มีบทสรุปโดยย่อของพื้นที่งาน หัวข้อที่คล้ายกันนั้นค่อนข้างบ่อย

ในบทความเราจะพิจารณาการใช้งานใน PostgreSQL ของการแก้ปัญหาดังกล่าวในเวอร์ชัน "ไร้เดียงสา" ซึ่งเป็นอัลกอริทึมที่ "ฉลาดกว่า" และซับซ้อนมาก "วนซ้ำ" ใน SQL โดยมีเงื่อนไขการออกจากข้อมูลที่พบซึ่งมีประโยชน์ทั้งสำหรับการพัฒนาทั่วไปและสำหรับใช้ในกรณีอื่นๆ ที่คล้ายคลึงกัน

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

CREATE INDEX ON task(owner_id, task_date, id);
-- а старый - удалим
DROP INDEX task_owner_id_task_date_idx;

ได้ยินอย่างไรก็เขียนอย่างนั้น

ขั้นแรก เรามาร่างคำขอเวอร์ชันที่ง่ายที่สุด โดยส่ง ID ของนักแสดง อาร์เรย์เป็นอินพุต:

SELECT
  *
FROM
  task
WHERE
  owner_id = ANY('{1,2,4,8,16,32,64,128,256,512}'::integer[])
ORDER BY
  task_date, id
LIMIT 20;

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

น่าเศร้าเล็กน้อย - เราสั่งซื้อเพียง 20 รายการและการสแกนดัชนีส่งคืนให้เรา 960 เส้นซึ่งก็ต้องเรียงลำดับด้วย ... และลองอ่านให้น้อยลง

ไม่ซ้อนกัน + ARRAY

การพิจารณาครั้งแรกที่จะช่วยเรา - หากเราต้องการ รวม 20 เรียง บันทึกก็พออ่านได้ ไม่เกิน 20 เรียงตามลำดับเดียวกันสำหรับแต่ละรายการ สำคัญ. ดี, ดัชนีที่เหมาะสม (owner_id, task_date, id) เรามี

ลองใช้กลไกเดียวกันในการแยกและ "เปลี่ยนเป็นคอลัมน์" รายการตารางที่สมบูรณ์เช่นเดียวกับใน บทความล่าสุด. และใช้ convolution กับอาร์เรย์โดยใช้ฟังก์ชัน ARRAY():

WITH T AS (
  SELECT
    unnest(ARRAY(
      SELECT
        t
      FROM
        task t
      WHERE
        owner_id = unnest
      ORDER BY
        task_date, id
      LIMIT 20 -- ограничиваем тут...
    )) r
  FROM
    unnest('{1,2,4,8,16,32,64,128,256,512}'::integer[])
)
SELECT
  (r).*
FROM
  T
ORDER BY
  (r).task_date, (r).id
LIMIT 20; -- ... и тут - тоже

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

โอ้ มันดีขึ้นมากแล้ว! เร็วขึ้น 40% และข้อมูลน้อยลง 4.5 เท่า ต้องอ่าน

การทำให้เป็นจริงของบันทึกตารางผ่าน CTEฉันจะทราบว่า ในบางกรณี ความพยายามที่จะทำงานกับฟิลด์เรกคอร์ดทันทีหลังจากค้นหาในเคียวรีย่อย โดยไม่ "ตัด" ใน CTE อาจนำไปสู่ "การคูณ" InitPlan เป็นสัดส่วนกับจำนวนฟิลด์เดียวกันเหล่านี้:

SELECT
  ((
    SELECT
      t
    FROM
      task t
    WHERE
      owner_id = 1
    ORDER BY
      task_date, id
    LIMIT 1
  ).*);

Result  (cost=4.77..4.78 rows=1 width=16) (actual time=0.063..0.063 rows=1 loops=1)
  Buffers: shared hit=16
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.42..1.19 rows=1 width=48) (actual time=0.031..0.032 rows=1 loops=1)
          Buffers: shared hit=4
          ->  Index Scan using task_owner_id_task_date_id_idx on task t  (cost=0.42..387.57 rows=500 width=48) (actual time=0.030..0.030 rows=1 loops=1)
                Index Cond: (owner_id = 1)
                Buffers: shared hit=4
  InitPlan 2 (returns $1)
    ->  Limit  (cost=0.42..1.19 rows=1 width=48) (actual time=0.008..0.009 rows=1 loops=1)
          Buffers: shared hit=4
          ->  Index Scan using task_owner_id_task_date_id_idx on task t_1  (cost=0.42..387.57 rows=500 width=48) (actual time=0.008..0.008 rows=1 loops=1)
                Index Cond: (owner_id = 1)
                Buffers: shared hit=4
  InitPlan 3 (returns $2)
    ->  Limit  (cost=0.42..1.19 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=1)
          Buffers: shared hit=4
          ->  Index Scan using task_owner_id_task_date_id_idx on task t_2  (cost=0.42..387.57 rows=500 width=48) (actual time=0.008..0.008 rows=1 loops=1)
                Index Cond: (owner_id = 1)
                Buffers: shared hit=4"
  InitPlan 4 (returns $3)
    ->  Limit  (cost=0.42..1.19 rows=1 width=48) (actual time=0.009..0.009 rows=1 loops=1)
          Buffers: shared hit=4
          ->  Index Scan using task_owner_id_task_date_id_idx on task t_3  (cost=0.42..387.57 rows=500 width=48) (actual time=0.009..0.009 rows=1 loops=1)
                Index Cond: (owner_id = 1)
                Buffers: shared hit=4

เรกคอร์ดเดียวกันถูก "ค้นหา" 4 ครั้ง... จนกระทั่ง PostgreSQL 11 ลักษณะการทำงานนี้เกิดขึ้นเป็นประจำ และวิธีแก้ไขคือ "รวม" ใน CTE ซึ่งเป็นขอบเขตที่ไม่มีเงื่อนไขสำหรับเครื่องมือเพิ่มประสิทธิภาพในเวอร์ชันเหล่านี้

ตัวสะสมแบบเรียกซ้ำ

ในฉบับที่แล้วเราอ่านทั้งหมด 200 เส้น เพื่อความจำเป็น 20 ไม่ใช่ 960 แต่น้อยกว่านั้น - เป็นไปได้ไหม

ลองใช้ความรู้ที่เราต้องการ รวม 20 บันทึก นั่นคือเราจะทำซ้ำการลบข้อมูลจนกว่าจะถึงจำนวนที่เราต้องการเท่านั้น

ขั้นตอนที่ 1: รายการเริ่มต้น

เห็นได้ชัดว่ารายการ "เป้าหมาย" 20 รายการของเราควรเริ่มต้นด้วยรายการ "แรก" สำหรับหนึ่งในคีย์ Owner_id ของเรา ดังนั้นเราจึงพบสิ่งนี้ก่อน "แรกสุด" สำหรับแต่ละคีย์ และใส่ไว้ในรายการเรียงลำดับตามที่เราต้องการ - (task_date, id)

ขั้นตอนที่ 2: ค้นหาบันทึก "ถัดไป"

ตอนนี้ถ้าเราใช้รายการแรกจากรายการของเราและเริ่มต้น "ก้าว" ลงไปอีกดัชนี ด้วยการบันทึก Owner_id-key จากนั้นระเบียนที่พบทั้งหมดจะเป็นเพียงรายการถัดไปในการเลือกผลลัพธ์ แน่นอนเท่านั้น จนกว่าเราจะข้ามคีย์ที่ใช้ รายการที่สองในรายการ

หากปรากฎว่าเรา "ข้าม" รายการที่สอง ควรเพิ่มรายการที่อ่านล่าสุดลงในรายการแทนรายการแรก (ด้วย owner_id เดียวกัน) หลังจากนั้นรายการจะถูกจัดเรียงอีกครั้ง

นั่นคือเรามักจะเข้าใจว่ารายการมีไม่เกินหนึ่งรายการสำหรับแต่ละคีย์ (หากรายการจบลงและเราไม่ได้ "ข้าม" รายการแรกจะหายไปจากรายการและจะไม่มีอะไรเพิ่ม ), และพวกเขา จัดเรียงเสมอ ตามลำดับจากน้อยไปมากของคีย์แอปพลิเคชัน (task_date, id)

ขั้นตอนที่ 3: การกรองและการขยายบันทึก

ในส่วนของแถวของการเลือกแบบเรียกซ้ำของเรา บางระเบียน rv ซ้ำกัน - ก่อนอื่นเราจะพบเช่น "ข้ามขอบของรายการที่ 2 ของรายการ" จากนั้นเราจะแทนที่ด้วยรายการที่ 1 ดังนั้นควรกรองเหตุการณ์แรกออก

คำถามสุดท้ายแย่มาก

WITH RECURSIVE T AS (
  -- #1 : заносим в список "первые" записи по каждому из ключей набора
  WITH wrap AS ( -- "материализуем" record'ы, чтобы обращение к полям не вызывало умножения InitPlan/SubPlan
    WITH T AS (
      SELECT
        (
          SELECT
            r
          FROM
            task r
          WHERE
            owner_id = unnest
          ORDER BY
            task_date, id
          LIMIT 1
        ) r
      FROM
        unnest('{1,2,4,8,16,32,64,128,256,512}'::integer[])
    )
    SELECT
      array_agg(r ORDER BY (r).task_date, (r).id) list -- сортируем список в нужном порядке
    FROM
      T
  )
  SELECT
    list
  , list[1] rv
  , FALSE not_cross
  , 0 size
  FROM
    wrap
UNION ALL
  -- #2 : вычитываем записи 1-го по порядку ключа, пока не перешагнем через запись 2-го
  SELECT
    CASE
      -- если ничего не найдено для ключа 1-й записи
      WHEN X._r IS NOT DISTINCT FROM NULL THEN
        T.list[2:] -- убираем ее из списка
      -- если мы НЕ пересекли прикладной ключ 2-й записи
      WHEN X.not_cross THEN
        T.list -- просто протягиваем тот же список без модификаций
      -- если в списке уже нет 2-й записи
      WHEN T.list[2] IS NULL THEN
        -- просто возвращаем пустой список
        '{}'
      -- пересортировываем словарь, убирая 1-ю запись и добавляя последнюю из найденных
      ELSE (
        SELECT
          coalesce(T.list[2] || array_agg(r ORDER BY (r).task_date, (r).id), '{}')
        FROM
          unnest(T.list[3:] || X._r) r
      )
    END
  , X._r
  , X.not_cross
  , T.size + X.not_cross::integer
  FROM
    T
  , LATERAL(
      WITH wrap AS ( -- "материализуем" record
        SELECT
          CASE
            -- если все-таки "перешагнули" через 2-ю запись
            WHEN NOT T.not_cross
              -- то нужная запись - первая из спписка
              THEN T.list[1]
            ELSE ( -- если не пересекли, то ключ остался как в предыдущей записи - отталкиваемся от нее
              SELECT
                _r
              FROM
                task _r
              WHERE
                owner_id = (rv).owner_id AND
                (task_date, id) > ((rv).task_date, (rv).id)
              ORDER BY
                task_date, id
              LIMIT 1
            )
          END _r
      )
      SELECT
        _r
      , CASE
          -- если 2-й записи уже нет в списке, но мы хоть что-то нашли
          WHEN list[2] IS NULL AND _r IS DISTINCT FROM NULL THEN
            TRUE
          ELSE -- ничего не нашли или "перешагнули"
            coalesce(((_r).task_date, (_r).id) < ((list[2]).task_date, (list[2]).id), FALSE)
        END not_cross
      FROM
        wrap
    ) X
  WHERE
    T.size < 20 AND -- ограничиваем тут количество
    T.list IS DISTINCT FROM '{}' -- или пока список не кончился
)
-- #3 : "разворачиваем" записи - порядок гарантирован по построению
SELECT
  (rv).*
FROM
  T
WHERE
  not_cross; -- берем только "непересекающие" записи

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

ดังนั้นเราจึง การซื้อขายข้อมูล 50% อ่านเป็นเวลาดำเนินการ 20%. นั่นคือถ้าคุณมีเหตุผลที่เชื่อได้ว่าการอ่านอาจใช้เวลานาน (เช่น ข้อมูลมักไม่อยู่ในแคช และคุณต้องไปที่ดิสก์เพื่ออ่านข้อมูลนั้น) ด้วยวิธีนี้ คุณสามารถพึ่งพาการอ่านน้อยลง

ไม่ว่าในกรณีใดเวลาดำเนินการจะดีกว่าในตัวเลือกแรกที่ "ไร้เดียงสา" แต่ตัวเลือกใดใน 3 ตัวเลือกนี้ขึ้นอยู่กับคุณ

ที่มา: will.com