ProHoster > blog > administratie > SQL HowTo: schrijf een while-loop direct in de query, of "Elementaire three-way"

SQL HowTo: schrijf een while-loop direct in de query, of "Elementaire three-way"

Periodiek ontstaat de taak om naar gerelateerde gegevens te zoeken met een reeks sleutels, totdat we het vereiste totale aantal records hebben.

Het meest "levensechte" voorbeeld is om te laten zien 20 oudste problemen, genoteerd op de lijst van medewerkers (bijvoorbeeld binnen dezelfde afdeling). Voor verschillende managementdashboards met korte samenvattingen van werkgebieden is vaak een soortgelijk onderwerp vereist.

SQL HowTo: schrijf een while-loop direct in de query, of "Elementaire three-way"

In het artikel gaan we in op de implementatie op PostgreSQL van een "naΓ―eve" versie van het oplossen van een dergelijk probleem, een "slimmer" en zeer complex algoritme "lus" in SQL met een exit-voorwaarde van de gevonden gegevens, wat zowel nuttig kan zijn voor algemene ontwikkeling als voor gebruik in andere soortgelijke gevallen.

Laten we een testdataset nemen van vorig artikel. Zodat de uitvoerrecords niet van tijd tot tijd "springen" wanneer de gesorteerde waarden overeenkomen, breid de onderwerpindex uit door een primaire sleutel toe te voegen. Tegelijkertijd geeft dit het meteen uniciteit en garandeert het ons de uniciteit van de sorteervolgorde:

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

Zoals het wordt gehoord, zo wordt het geschreven

Laten we eerst de eenvoudigste versie van het verzoek schetsen, waarbij we de ID's van de artiesten doorgeven array als invoer:

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: schrijf een while-loop direct in de query, of "Elementaire three-way"
[kijk naar explain.tensor.ru]

Een beetje triest - we hebben slechts 20 platen besteld en Index Scan heeft ons teruggestuurd 960 regels, die toen ook gesorteerd moest worden ... En laten we proberen minder te lezen.

unnesten + ARRAY

De eerste overweging die ons zal helpen - als we dat nodig hebben totaal 20 gesorteerd records, het is genoeg om te lezen niet meer dan 20 gesorteerd in dezelfde volgorde voor elk sleutel. Goed, geschikte index (owner_id, task_date, id) die we hebben.

Laten we hetzelfde mechanisme gebruiken voor het extraheren en "veranderen in kolommen" integrale tabelinvoer, als in laatste artikel. En pas de convolutie ook toe op een array met behulp van de functie 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: schrijf een while-loop direct in de query, of "Elementaire three-way"
[kijk naar explain.tensor.ru]

Oh, het is al veel beter! 40% sneller en 4.5 keer minder data moest lezen.

Materialisatie van tabelrecords via CTEIk zal dat noteren in sommige gevallen een poging om direct met de recordvelden te werken na ernaar te hebben gezocht in een subquery, zonder deze in een CTE te "wikkelen", kan leiden tot "vermenigvuldiging" InitPlan evenredig met het aantal van deze zelfde velden:

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

Hetzelfde record werd 4 keer "doorzocht" ... Tot PostgreSQL 11 kwam dit gedrag regelmatig voor en de oplossing is om in een CTE te "verpakken", wat een onvoorwaardelijke grens is voor de optimizer in deze versies.

recursieve accumulator

In de vorige versie lazen we in totaal 200 regels omwille van de nodige 20. Al geen 960, maar nog minder - is het mogelijk?

Laten we proberen de kennis te gebruiken die we nodig hebben totaal 20 verslagen. Dat wil zeggen, we zullen de gegevensaftrekking alleen herhalen totdat de hoeveelheid die we nodig hebben is bereikt.

Stap 1: Startlijst

Het is duidelijk dat onze "doellijst" van 20 items moet beginnen met de "eerste" items voor een van onze owner_id-sleutels. Daarom vinden we eerst zoiets "allereerste" voor elk van de sleutels en plaats het in de lijst, sorteer het in de volgorde die we willen - (task_date, id).

SQL HowTo: schrijf een while-loop direct in de query, of "Elementaire three-way"

Stap 2: vind de "volgende" records

Als we nu het eerste item uit onze lijst nemen en beginnen "stap" verder naar beneden in de index met het opslaan van de owner_id-key, dan zijn alle gevonden records gewoon de volgende in de resulterende selectie. Natuurlijk alleen totdat we de toegepaste sleutel kruisen tweede vermelding in de lijst.

Als blijkt dat we de tweede ingang hebben "gekruist", dan het laatst gelezen item moet aan de lijst worden toegevoegd in plaats van het eerste (met dezelfde owner_id), waarna de lijst weer gesorteerd wordt.

SQL HowTo: schrijf een while-loop direct in de query, of "Elementaire three-way"

Dat wil zeggen, we krijgen altijd dat de lijst niet meer dan één item heeft voor elk van de toetsen (als de items voorbij zijn en we zijn niet "gekruist", dan verdwijnt het eerste item gewoon uit de lijst en wordt er niets toegevoegd ), en zij altijd gesorteerd in oplopende volgorde van de applicatiesleutel (task_date, id).

SQL HowTo: schrijf een while-loop direct in de query, of "Elementaire three-way"

Stap 3: Records filteren en uitbreiden

In het deel van de rijen van onze recursieve selectie, enkele records rv worden gedupliceerd - eerst vinden we bijvoorbeeld "overschrijding van de grens van de 2e invoer van de lijst", en dan vervangen we als de 1e van de lijst. En dus moet het eerste voorkomen eruit worden gefilterd.

Vreselijke laatste vraag

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: schrijf een while-loop direct in de query, of "Elementaire three-way"
[kijk naar explain.tensor.ru]

Dus, wij verhandelde 50% data reads voor 20% uitvoeringstijd. Dat wil zeggen, als u reden hebt om aan te nemen dat het lezen lang kan duren (gegevens staan ​​bijvoorbeeld vaak niet in de cache en u moet daarvoor naar de schijf gaan), dan kunt u er op deze manier op vertrouwen dat u minder leest.

In ieder geval bleek de uitvoeringstijd beter dan bij de "naΓ―eve" eerste optie. Maar welke van deze 3 opties u gebruikt, is aan u.

Bron: www.habr.com

Voeg een reactie