SQL HowTo: tulis gelung sementara terus dalam pertanyaan, atau "Tiga hala asas"

Secara berkala, tugas mencari data berkaitan dengan satu set kunci timbul, sehingga kami mendapat jumlah bilangan rekod yang diperlukan.

Contoh yang paling "seperti hidup" adalah untuk dipaparkan 20 masalah tertua, disenaraikan dalam senarai pekerja (contohnya, dalam jabatan yang sama). Untuk pelbagai "papan pemuka" pengurusan dengan ringkasan ringkas tentang bidang kerja, topik yang sama diperlukan agak kerap.

Dalam artikel itu, kami akan mempertimbangkan pelaksanaan pada PostgreSQL versi "naif" untuk menyelesaikan masalah sedemikian, algoritma "lebih bijak" dan sangat kompleks "gelung" dalam SQL dengan keadaan keluar daripada data yang ditemui, yang boleh berguna untuk pembangunan umum dan untuk kegunaan dalam kes lain yang serupa.

Mari kita ambil set data ujian daripada artikel sebelum ini. Supaya rekod keluaran tidak "melompat" dari semasa ke semasa apabila nilai yang disusun sepadan, meluaskan indeks subjek dengan menambah kunci utama. Pada masa yang sama, ini akan memberikan keunikan serta-merta, dan menjamin kami keunikan susunan isihan:

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

Seperti yang didengar, begitulah yang tertulis

Mula-mula, mari kita lakarkan versi permintaan yang paling mudah, menghantar ID pelaku tatasusunan sebagai input:

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;

[lihat explain.tensor.ru]

Agak menyedihkan - kami memesan hanya 20 rekod, dan Imbasan Indeks mengembalikan kami 960 baris, yang kemudiannya juga terpaksa disusun ... Dan mari cuba kurangkan membaca.

unnest + ARRAY

Pertimbangan pertama yang akan membantu kita - jika kita perlukan jumlah 20 disusun rekod, ia cukup untuk dibaca tidak lebih daripada 20 diisih dalam susunan yang sama untuk setiap satu kunci. Baik, indeks yang sesuai (id_pemilik, tarikh_tugas, id) kami ada.

Mari kita gunakan mekanisme yang sama untuk mengekstrak dan "menjadi lajur" kemasukan jadual integral, seperti dalam artikel terakhir. Dan juga gunakan lilitan pada tatasusunan menggunakan fungsi tersebut 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; -- ... и тут - тоже

[lihat explain.tensor.ru]

Oh, ia sudah jauh lebih baik! 40% lebih pantas dan 4.5 kali lebih sedikit data terpaksa membaca.

Pewujudan rekod jadual melalui CTESaya akan ambil perhatian bahawa dalam beberapa kes percubaan untuk segera bekerja dengan medan rekod selepas mencarinya dalam subkueri, tanpa "membungkus" dalam CTE, boleh membawa kepada "pendaraban" InitPlan berkadar dengan bilangan medan yang sama ini:

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

Rekod yang sama telah "dicari" 4 kali... Sehingga PostgreSQL 11, tingkah laku ini berlaku dengan kerap, dan penyelesaiannya adalah untuk "membungkus" dalam CTE, yang merupakan sempadan tanpa syarat untuk pengoptimum dalam versi ini.

penumpuk rekursif

Dalam versi sebelumnya, secara keseluruhan, kami membaca 200 baris demi keperluan 20. Sudah tidak 960, tetapi lebih kurang - adakah mungkin?

Cuba kita gunakan ilmu yang kita perlukan jumlah xnumx rekod. Iaitu, kami akan mengulangi penolakan data sahaja sehingga jumlah yang kami perlukan dicapai.

Langkah 1: Senarai Mula

Jelas sekali, senarai "sasaran" kami bagi 20 entri harus bermula dengan entri "pertama" untuk salah satu kunci owner_id kami. Oleh itu, kita mula-mula mencari seperti itu "sangat pertama" untuk setiap kunci dan letakkannya dalam senarai, susun mengikut susunan yang kita mahu - (task_date, id).

Langkah 2: cari rekod "seterusnya".

Sekarang jika kita mengambil entri pertama dari senarai kita dan mula "langkah" lebih jauh ke bawah indeks dengan menyimpan owner_id-key, maka semua rekod yang ditemui hanyalah rekod seterusnya dalam pemilihan yang terhasil. Sudah tentu, hanya sehingga kita melintasi kunci yang digunakan entri kedua dalam senarai.

Jika ternyata kami "melintasi" entri kedua, maka entri baca terakhir harus ditambahkan pada senarai dan bukannya yang pertama (dengan id_pemilik yang sama), selepas itu senarai itu diisih semula.

Iaitu, kita sentiasa mendapat bahawa senarai itu tidak mempunyai lebih daripada satu entri untuk setiap kunci (jika entri sudah tamat, dan kita belum "menyilang", maka entri pertama akan hilang begitu saja daripada senarai dan tiada apa yang akan ditambahkan ), dan mereka sentiasa disusun dalam tertib menaik kekunci aplikasi (task_date, id).

Langkah 3: Menapis dan Memperluas Rekod

Di bahagian baris pemilihan rekursif kami, beberapa rekod rv diduakan - mula-mula kita dapati seperti "melintasi sempadan entri ke-2 senarai", dan kemudian kita menggantikan sebagai yang pertama daripada senarai. Jadi kejadian pertama harus ditapis.

Pertanyaan akhir yang mengerikan

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; -- берем только "непересекающие" записи

[lihat explain.tensor.ru]

Justeru, kami berdagang 50% data dibaca untuk 20% masa pelaksanaan. Iaitu, jika anda mempunyai sebab untuk mempercayai bahawa membaca boleh menjadi panjang (contohnya, data selalunya tiada dalam cache, dan anda perlu pergi ke cakera untuknya), maka dengan cara ini anda boleh bergantung pada membaca kurang.

Walau apa pun, masa pelaksanaan ternyata lebih baik daripada pilihan pertama "naif". Tetapi antara 3 pilihan ini yang manakah terpulang kepada anda.

Sumber: www.habr.com