SQL HowTo: sau ib lub sij hawm voj ncaj qha rau hauv cov lus nug, los yog "Elementary peb-kauj ruam"

Los ntawm lub sijhawm mus rau lub sijhawm, txoj haujlwm ntawm kev tshawb nrhiav cov ntaub ntawv cuam tshuam los ntawm ib pawg ntawm cov yuam sij tshwm sim, kom txog thaum peb mus txog tus lej tag nrho ntawm cov ntaub ntawv uas xav tau.

Piv txwv "lub neej tiag tiag" tshaj plaws yog los txiav txim siab 20 txoj haujlwm qub tshaj plaws, teev tseg nyob rau hauv daim ntawv teev cov neeg ua haujlwm (Piv txwv li, hauv ib lub chaw haujlwm). Rau ntau lub dashboard tswj hwm nrog cov ntsiab lus luv luv ntawm cov chaw ua haujlwm, feem ntau xav tau lub ntsiab lus zoo sib xws.

Hauv tsab xov xwm no, peb yuav xav txog kev siv qhov kev daws teeb meem "naive" rau qhov teeb meem no hauv PostgreSQL, uas yog "ntse dua" thiab yog ib qho algorithm nyuaj heev "loop" hauv SQL nrog rau qhov kev tawm mus raws li cov ntaub ntawv pom, uas tuaj yeem pab tau rau kev txhim kho dav dav thiab rau kev siv rau lwm yam xwm txheej zoo sib xws.

Cia peb muab cov ntaub ntawv sim los ntawm tsab xov xwm dhau losTxhawm rau tiv thaiv cov ntaub ntawv tso zis los ntawm "dhia" los ntawm ib lub sijhawm mus rau lwm qhov thaum cov nqi txheeb xyuas sib phim, Cia peb nthuav cov ntsiab lus los ntawm kev ntxiv tus yuam sij tseem ceebQhov no yuav ua rau nws tshwj xeeb tam sim ntawd thiab lav qhov kev txiav txim kom meej:

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

Nws sau raws li nws lub suab.

Ua ntej, cia peb kos duab qhov yooj yim tshaj plaws ntawm qhov kev thov, xa cov ID ntawm cov neeg ua yeeb yam array ua ib qho input parameter:

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;

[saib ntawm piav qhia.tensor.ru]

Nws tu siab me ntsis - peb tsuas yog xaj 20 daim ntawv xwb, thiab Index Scan xa rov qab rau peb. 960 kab, uas tom qab ntawd yuav tsum tau txheeb xyuas ... Cia peb sim nyeem tsawg dua.

tsis muaj + ARRAY

Qhov kev xav thawj zaug uas yuav pab peb yog tias peb xav tau tsuas yog 20 tau raug xaiv cov ntaub ntawv, ces nws txaus los nyeem tsis pub tshaj 20 qhov tau raug xaiv raws li qhov kev txiav txim rau txhua tus tus yuam sij. Zoo siab, cov ntsuas tsim nyog (tus tswv_id, hnub ua haujlwm_hnub tim, tus lej) peb muaj.

Cia peb siv tib lub tshuab rho tawm thiab "qhib rau hauv cov kem" kev nkag mus rau hauv lub rooj sib tham, zoo li nyob rau hauv kab lus kawgPeb kuj tseem yuav siv cov folding rau hauv ib qho array siv cov function 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; -- ... и тут - тоже

[saib ntawm piav qhia.tensor.ru]

Auj, qhov ntawd zoo dua! Sai dua 40% thiab tsawg dua 4.5 npaug ntawm cov ntaub ntawv Kuv yuav tsum tau nyeem nws.

Kev ua cov ntaub ntawv teev lus los ntawm CTEKuv xav kom koj paub txog qhov tseeb tias hauv qee kis sim ua haujlwm tam sim ntawd nrog cov ntaub ntawv sau tom qab nrhiav nws hauv cov lus nug me, yam tsis tau "qhwv" nws hauv CTE, tuaj yeem ua rau "kev sib npaug" ntawm InitPlan sib npaug rau tus lej ntawm cov teb no:

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

Cov ntaub ntawv tib yam tau "nrhiav" 4 zaug ... Txog thaum PostgreSQL 11, tus cwj pwm no tau ntsib tsis tu ncua, thiab qhov kev daws teeb meem yog "qhwv" nws hauv CTE, uas yog qhov txwv tsis pub dhau rau qhov optimizer hauv cov qauv no.

Cov khoom siv rov ua dua

Hauv cov version dhau los, peb nyeem tag nrho 200 kab rau qhov xav tau 20. Tsis yog 960 lawm, tab sis tsawg dua - puas ua tau?

Cia peb sim siv cov kev paub uas peb xav tau tag nrho 20 cov ntaub ntawv. Ntawd yog, peb yuav rov ua dua qhov kev nyeem cov ntaub ntawv tsuas yog kom txog thaum peb mus txog tus lej xav tau.

Kauj Ruam 1: Pib Daim Ntawv Teev Npe

Nws yog qhov tseeb tias peb daim ntawv teev npe "lub hom phiaj" ntawm 20 cov ntaub ntawv yuav tsum pib nrog cov ntaub ntawv "thawj zaug" rau ib qho ntawm peb cov yuam sij owner_id. Yog li, cia peb xub nrhiav cov ntawd "thawj zaug" rau txhua tus yuam sij thiab ntxiv rau daim ntawv teev npe, cais nws raws li qhov peb xav tau - (task_date, id).

Kauj Ruam 2: Nrhiav cov ntaub ntawv "tom ntej"

Tam sim no yog tias peb coj thawj daim ntawv los ntawm peb daim ntawv teev npe thiab pib "kauj ruam" ntxiv raws li qhov ntsuas nrog tus yuam sij owner_id khaws cia, ces tag nrho cov ntaub ntawv pom yog cov tom ntej hauv qhov kev xaiv tshwm sim. Tau kawg, tsuas yog kom txog thaum peb hla tus yuam sij uas tau siv qhov nkag thib ob hauv daim ntawv teev npe.

Yog tias nws hloov tawm tias peb "hla" qhov nkag thib ob, ces cov ntawv nyeem kawg yuav tsum tau ntxiv rau daim ntawv teev npe es tsis yog thawj daim (nrog tib tus tswv_id), tom qab ntawd peb rov xaiv cov npe dua.

Yog li ntawd, peb ib txwm xaus nrog qhov tseeb tias daim ntawv teev npe tsis muaj ntau tshaj ib qho nkag rau txhua tus yuam sij (yog tias cov ntawv nkag tas thiab peb tsis tau "hla" lawv, ces thawj qhov nkag yuav ploj ntawm daim ntawv teev npe thiab tsis muaj dab tsi yuav ntxiv), thiab lawv ib txwm raug xaiv raws li qhov kev txiav txim ntawm tus yuam sij ntawm daim ntawv thov (task_date, id).

Kauj Ruam 3: Lim thiab nthuav cov ntaub ntawv

Hauv qee cov kab ntawm peb qhov kev xaiv rov ua dua, qee cov ntaub ntawv rv Cov ntawv sau uas muaj ob daim—peb xub nrhiav cov ntawv sau zoo li "hla ciam teb ntawm cov ntawv sau thib 2 hauv daim ntawv teev npe," thiab tom qab ntawd hloov lawv ua cov ntawv sau thawj zaug hauv daim ntawv teev npe. Yog li, qhov tshwm sim thawj zaug yuav tsum tau lim tawm.

Cov lus nug kawg uas txaus ntshai

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

[saib ntawm piav qhia.tensor.ru]

Yog li ntawd peb pauv 50% ntawm cov ntaub ntawv nyeem rau 20% ntawm lub sijhawm ua tiavYog li ntawd, yog tias koj muaj laj thawj ntseeg tias kev nyeem ntawv yuav qeeb (piv txwv li, cov ntaub ntawv feem ntau tsis nyob hauv cache thiab yuav tsum tau rov qab los ntawm disk), ces txoj kev no tuaj yeem txo koj qhov kev cia siab rau kev nyeem ntawv.

Txawm li cas los xij, lub sijhawm ua tiav zoo dua li qhov kev xaiv "naive" thawj zaug. Tab sis qhov twg ntawm peb txoj kev xaiv no yog nyob ntawm koj.

Tau qhov twg los: www.hab.com