ProHoster > Blog > fitantanan-draharaha > SQL HowTo: manoratra tsipika fohy mivantana amin'ny fangatahana, na "dingana telo fototra"

SQL HowTo: manoratra tsipika fohy mivantana amin'ny fangatahana, na "dingana telo fototra"

Mipoitra tsindraindray ny asa fitadiavana angon-drakitra mifandraika amin'ny fampiasana fanalahidy iray. mandra-pahazoantsika ny fitambaran'ny isan'ny firaketana.

Ny ohatra "tena fiainana" indrindra dia ny aseho 20 olana tranainy indrindra, voatanisa ao amin'ny lisitry ny mpiasa (ohatra, ao anatin'ny fizarana iray). Ho an'ny "dashboard" fitantanana isan-karazany miaraka amin'ny famintinana fohy momba ny sehatry ny asa dia ilaina matetika ny lohahevitra mitovy.

SQL HowTo: manoratra tsipika fohy mivantana amin'ny fangatahana, na "dingana telo fototra"

Ato amin'ity lahatsoratra ity isika dia hijery ny fampiharana ao amin'ny PostgreSQL ny "naive" vahaolana ho an'ny olana toy izany, "tsara kokoa" sy be pitsiny algorithm. "loop" ao amin'ny SQL miaraka amin'ny fepetra fivoahana amin'ny angona hita, izay mety mahasoa na ho an'ny fampandrosoana ankapobeny na ho an'ny fampiasana amin'ny tranga hafa mitovy.

Andeha isika haka angona fitsapana avy amin'ny lahatsoratra teo aloha. Mba hisorohana ny firaketana aseho tsy "mitsambikina" tsindraindray rehefa mifanandrify ireo soatoavina voafantina, manitatra ny fanondroan'ny lohahevitra amin'ny fampidirana fanalahidy fototra. Amin'izay fotoana izay dia hanome azy ny maha-tokana azy avy hatrany izany ary hiantoka antsika fa tsy misy dikany ny filaharana:

CREATE INDEX ON task(owner_id, task_date, id);
-- Π° старый - ΡƒΠ΄Π°Π»ΠΈΠΌ
DROP INDEX task_owner_id_task_date_idx;

Araka ny re no voasoratra

Voalohany, andao hanao sketch ny dikan-teny tsotra indrindra amin'ny fangatahana, mandalo ny ID an'ny mpanakanto. array ho toy ny 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;

SQL HowTo: manoratra tsipika fohy mivantana amin'ny fangatahana, na "dingana telo fototra"
[jereo ao amin'ny explain.tensor.ru]

Nampalahelo kely - firaketana 20 ihany no nanafatra anay, fa naverin'ny Index Scan taminay izany 960 andalana, izay tsy maintsy nalahatra koa avy eo... Andao hiezaka hamaky kely kokoa.

unnest + ARRAY

Ny fiheverana voalohany hanampy antsika dia raha mila izany isika 20 ihany no nalahatra firaketana, dia vakio fotsiny tsy mihoatra ny 20 nalahatra mitovy filaharana ho an'ny tsirairay fanalahidy. tsara, fanondroana mety (tompon-tompo, daty_asa, id) manana.

Andao hampiasa fomba mitovy amin'ny fitrandrahana sy "fiparitahana amin'ny tsanganana" firaketana tabilao integral, toy ny amin'ny lahatsoratra farany. Azontsika atao ihany koa ny mampihatra ny folding amin'ny array amin'ny fampiasana ny fiasa 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; -- ... ΠΈ Ρ‚ΡƒΡ‚ - Ρ‚ΠΎΠΆΠ΅

SQL HowTo: manoratra tsipika fohy mivantana amin'ny fangatahana, na "dingana telo fototra"
[jereo ao amin'ny explain.tensor.ru]

Oh, tsara lavitra! 40% haingana kokoa ary 4.5 heny ny data Tsy maintsy namaky azy io aho.

Fanamafisana ny firaketana an-databatra amin'ny alΓ lan'ny CTEMamelΓ  ahy hisarihana ny sainao ho amin'izany amin'ny toe-javatra sasany Ny fiezahana hiasa avy hatrany amin'ny sahan'ny rakitra rehefa avy nikaroka azy tao amin'ny subquery, nefa tsy "manarona" azy amin'ny CTE, dia mety hitarika amin'ny "Ampitomboy" InitPlan mifanandrify amin'ny isan'ireo saha ireo ihany:

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

Io firaketana io ihany no "nijery" in-4 ... Hatramin'ny PostgreSQL 11, ity fihetsika ity dia mitranga tsy tapaka, ary ny vahaolana dia ny "manarona" azy amin'ny CTE, izay fetra tanteraka ho an'ny optimizer amin'ireo dikan-teny ireo.

Recursive accumulator

Ao amin'ny dikan-teny teo aloha, amin'ny fitambarany no vakintsika 200 andalana noho ny 20 takiana. Tsy 960, fa na dia kely kokoa - azo atao ve?

Andao hiezaka hampiasa ny fahalalana ilaintsika total 20 rakitsoratra. Izany hoe, hamerina ny famakiana angon-drakitra izahay mandra-pahatonganay amin'ny vola ilainay.

Dingana 1: lisitra fanombohana

Mazava ho azy fa ny lisitr'ireo rakitsoratra 20 dia tokony hanomboka amin'ny firaketana "voalohany" ho an'ny iray amin'ireo fanalahidin'ny tompony_id. Noho izany, aloha isika dia hahita toy izany "Voalohany indrindra" ho an'ny fanalahidy tsirairay ary ampio ao amin'ny lisitra, alaharo amin'ny filaharana tadiavintsika - (task_date, id).

SQL HowTo: manoratra tsipika fohy mivantana amin'ny fangatahana, na "dingana telo fototra"

Dingana 2: Tadiavo ny fidirana "manaraka".

Ankehitriny raha maka ny fidirana voalohany amin'ny lisitray isika ary manomboka "dingana" manaraka ny fanondroana mitahiry ny fanalahidin'ny tompony_id, dia ireo rakitsoratra rehetra hita dia ny manaraka tanteraka amin'ny fisafidianana vokatra. Mazava ho azy, ihany mandra-pitampitana ny lakile vody fidirana faharoa amin'ny lisitra.

Raha hita fa "namakivaky" ny rakitsoratra faharoa isika, dia ny fidirana farany novakiana dia tokony hampidirina ao anaty lisitra fa tsy ny voalohany (miaraka amin'ny owner_id mitovy), aorian'izay dia averinay indray ny lisitra.

SQL HowTo: manoratra tsipika fohy mivantana amin'ny fangatahana, na "dingana telo fototra"

Izany hoe, mahazo foana isika fa ny lisitra dia tsy manana fidirana mihoatra ny iray isaky ny fanalahidy (raha lany ny fidirana ary tsy "miampita", dia hanjavona tsotra izao ny fidirana voalohany amin'ny lisitra ary tsy hisy hampiana. ), ary izy ireo voalamina foana amin'ny filaharana miakatra amin'ny fanalahidin'ny fampiharana (task_date, id).

SQL HowTo: manoratra tsipika fohy mivantana amin'ny fangatahana, na "dingana telo fototra"

Dingana 3: sivana ary "manitarana" rakitsoratra

Ao amin'ny andalana sasany amin'ny fifantenana miverimberina, misy rakitsoratra sasany rv dia duplicated - voalohany dia hitantsika toy ny "miampita ny sisin-tanin'ny fidirana faha-2 amin'ny lisitra", ary avy eo dia manolo azy ho voalohany amin'ny lisitra. Noho izany dia mila sivana ny fisehoana voalohany.

Ny fanontaniana farany mampatahotra

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; -- Π±Π΅Ρ€Π΅ΠΌ Ρ‚ΠΎΠ»ΡŒΠΊΠΎ "Π½Π΅ΠΏΠ΅Ρ€Π΅ΡΠ΅ΠΊΠ°ΡŽΡ‰ΠΈΠ΅" записи

SQL HowTo: manoratra tsipika fohy mivantana amin'ny fangatahana, na "dingana telo fototra"
[jereo ao amin'ny explain.tensor.ru]

Araka izany, isika nivarotra 50% ny angon-drakitra mamaky ny 20% amin'ny fotoana famonoana. Izany hoe, raha manana antony hinoanao fa mety haharitra ela ny famakiana (ohatra, ny angon-drakitra matetika dia tsy ao anaty cache, ary tsy maintsy mandeha any amin'ny kapila ianao), dia amin'izany fomba izany dia afaka miantehitra kely amin'ny famakiana ianao. .

Na ahoana na ahoana, ny fotoana famonoana dia nivadika ho tsara kokoa noho ny safidy voalohany "naive". Fa iza amin'ireo safidy 3 ireo no azonao ampiasaina.

Source: www.habr.com

Add a comment