SQL HowTo: Schreiben einer While-Schleife direkt in die Abfrage, oder „Elementare Drei-Wege-Verknüpfung“

Gelegentlich tritt die Aufgabe auf, zusammenhängende Daten nach einer Reihe von Schlüsseln zu suchen, bis wir die benötigte Gesamtanzahl von Datensätzen erreicht haben..

Ein besonders „lebendiger“ Beispiel — die Darstellung von den 20 ältesten Aufgaben,die auf der Liste der Mitarbeiter stehen, (zum Beispiel innerhalb einer Abteilung). Für verschiedene Management „Dashboards“ mit kurzen Zusammenfassungen über Arbeitsbereiche ist ein ähnliches Thema häufig erforderlich. In diesem Artikel besprechen wir die Umsetzung einer „naiven“ Lösung für diese Aufgabe in PostgreSQL, eine „intelligentere“ und eine ganz komplexe Algorithmus

„Schleife“ in SQL mit der Bedingung zum Ausstieg aus den gefundenen Daten, die sowohl für das allgemeine Wissen als auch für die Anwendung in anderen ähnlichen Fällen nützlich sein kann.Lass uns einen Testdatensatz aus

nehmen. Damit die ausgegebenen Datensätze nicht „springen“ von Mal zu Mal bei Übereinstimmungen der sortierten Werte, dem vorherigen Artikelerweitern wir den thematischen Index durch Hinzufügen des Primärschlüssels. Das verleiht ihm sofort Einzigartigkeit und garantiert uns eine eindeutige Reihenfolge der Sortierung:CREATE INDEX ON task(owner_id, task_date, id); -- und entfernen wir den alten DROP INDEX task_owner_id_task_date_idx;

Wie es sich anhört, so wird es geschrieben.

Zunächst skizzieren wir die einfachste Variante der Abfrage, indem wir die IDs der Bearbeiter

als Array als Eingabeparameter übergeben. 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;:

Ein wenig traurig — wir hatten nur 20 Datensätze bestellt, und der Index-Scan hat uns

[auf explain.tensor.ru anschauen]

960 Zeilen zurückgegeben, die wir dann noch sortieren mussten… Lass uns versuchen, weniger zu lesen.unnest + ARRAY.

Der erste Gedanke, der uns helfen wird — wenn wir nur

20 sortierte Datensätze benötigen, reicht es aus, nicht mehr als 20 sortierte in derselben Reihenfolge für jeden Schlüssel zu lesen. Zum Glück gibt es einen geeigneten Index (owner_id, task_date, id) bei uns. Wir werden dasselbe Extraktions- und „Column-Switching“-Mechanismus verwenden, der für den gesamten Datensatz der Tabelle geeignet ist, was auch in

der Fall ist. Ebenso wenden wir die Aggregation in ein Array mit der Funktion ARRAY() an.WITH T AS ( SELECT unnest(ARRAY( SELECT t FROM task t WHERE owner_id = unnest ORDER BY task_date, id LIMIT 20 -- hier begrenzen wir ... )) 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; -- ... und hier ebenfalls im vorherigen ArtikelOh, das ist schon viel besser! 40% schneller, und wir mussten 4.5 Mal weniger Daten:

lesen.

[auf explain.tensor.ru anschauen]

Materialisierung von Tabellendatensätzen durch CTE. Ich möchte darauf hinweisen, dass es in einigen Fällen

wichtig sein kann.Ich möchte darauf hinweisen, dass es in einigen Fällen Der Versuch, sofort mit den Feldern des Eintrags nach seiner Suche in einer Unterabfrage zu arbeiten, ohne sie in CTE zu "verpacken", kann zu der "Multiplikation" des InitPlans verhältnismäßig zur Anzahl dieser Felder führen:

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

Ergebnis  (Kosten=4.77..4.78 Zeilen=1 Breite=16) (tatsächliche Zeit=0.063..0.063 Zeilen=1 Schleifen=1)
  Puffer: geteilte Treffer=16
  InitPlan 1 (gibt $0 zurück)
    ->  Limit  (Kosten=0.42..1.19 Zeilen=1 Breite=48) (tatsächliche Zeit=0.031..0.032 Zeilen=1 Schleifen=1)
          Puffer: geteilte Treffer=4
          ->  Index-Scan mit task_owner_id_task_date_id_idx auf task t  (Kosten=0.42..387.57 Zeilen=500 Breite=48) (tatsächliche Zeit=0.030..0.030 Zeilen=1 Schleifen=1)
                Index-Bedingung: (owner_id = 1)
                Puffer: geteilte Treffer=4
  InitPlan 2 (gibt $1 zurück)
    ->  Limit  (Kosten=0.42..1.19 Zeilen=1 Breite=48) (tatsächliche Zeit=0.008..0.009 Zeilen=1 Schleifen=1)
          Puffer: geteilte Treffer=4
          ->  Index-Scan mit task_owner_id_task_date_id_idx auf task t_1  (Kosten=0.42..387.57 Zeilen=500 Breite=48) (tatsächliche Zeit=0.008..0.008 Zeilen=1 Schleifen=1)
                Index-Bedingung: (owner_id = 1)
                Puffer: geteilte Treffer=4
  InitPlan 3 (gibt $2 zurück)
    ->  Limit  (Kosten=0.42..1.19 Zeilen=1 Breite=48) (tatsächliche Zeit=0.008..0.008 Zeilen=1 Schleifen=1)
          Puffer: geteilte Treffer=4
          ->  Index-Scan mit task_owner_id_task_date_id_idx auf task t_2  (Kosten=0.42..387.57 Zeilen=500 Breite=48) (tatsächliche Zeit=0.008..0.008 Zeilen=1 Schleifen=1)
                Index-Bedingung: (owner_id = 1)
                Puffer: geteilte Treffer=4"
  InitPlan 4 (gibt $3 zurück)
    ->  Limit  (Kosten=0.42..1.19 Zeilen=1 Breite=48) (tatsächliche Zeit=0.009..0.009 Zeilen=1 Schleifen=1)
          Puffer: geteilte Treffer=4
          ->  Index-Scan mit task_owner_id_task_date_id_idx auf task t_3  (Kosten=0.42..387.57 Zeilen=500 Breite=48) (tatsächliche Zeit=0.009..0.009 Zeilen=1 Schleifen=1)
                Index-Bedingung: (owner_id = 1)
                Puffer: geteilte Treffer=4

Der gleiche Eintrag wurde 4 Mal "gesucht"… Bis einschließlich PostgreSQL 11 kommt dieses Verhalten regelmäßig vor, und die Lösung besteht darin, ihn in CTE zu "verpacken", was in diesen Versionen eine uneingeschränkte Grenze für den Optimierer darstellt.

Rekursiver Akkumulator

In der vorherigen Version haben wir insgesamt 200 Zeilen für die benötigten 20. Es sind nicht mehr 960, aber noch weniger – geht das?

Lassen Sie uns versuchen, das Wissen zu nutzen, dass wir insgesamt 20 Einträge benötigen. Das heißt, wir werden die Datenauslese nur bis zur Erreichung der benötigten Anzahl iterieren.

Schritt 1: Ausgangsliste

Offensichtlich sollte unsere „Ziel“-Liste von 20 Einträgen mit den „ersten“ Einträgen eines unserer owner_id-Schlüssel beginnen. Lassen Sie uns daher zunächst solche finden, „die allerersten“ für jeden Schlüssel und sie in die Liste aufnehmen, sortiert in der Reihenfolge, die wir wünschen – (task_date, id).

Schritt 2: Finden der „nächsten“ Einträge

Wenn wir jetzt den ersten Eintrag aus unserer Liste nehmen und anfangen, weiter im Index zu „schreiten“ unter Beibehaltung des owner_id-Schlüssels, dann sind alle gefundenen Einträge genau die nächsten in der Ergebnismenge. Natürlich nur solange wir den Anwendungs-Schlüssel nicht überschreiten zweite Eintragung in der Liste.

Wenn es so aussieht, als ob wir die zweite Eintragung "überschritten" haben, dann sollte der zuletzt gelesene Eintrag anstelle des ersten in die Liste eingefügt werden (mit der gleichen owner_id), danach wird die Liste erneut sortiert.

Das bedeutet, dass in der Liste immer nicht mehr als ein Eintrag für jeden der Schlüssel vorhanden ist (wenn die Einträge aufgebraucht sind und wir nicht "überschritten" haben, verschwindet einfach der erste Eintrag aus der Liste und es wird nichts hinzugefügt), wobei sie immer sortiert sind in aufsteigender Reihenfolge des Anwendungs-Schlüssels (task_date, id).

Schritt 3: Wir filtern und "entfalten" die Einträge

In einigen Zeilen unserer rekursiven Auswahl werden einige Einträge rv dupliziert — zuerst finden wir solche, die "die Grenze des 2. Eintrags der Liste überschreiten", und dann setzen wir sie als den 1. aus der Liste ein. Das erste Erscheinen muss herausgefiltert werden.

Schrecklicher endgültiger Query

MIT REKURSIVEN T AS (
  -- #1 : Wir fügen die "ersten" Einträge für jeden der Schlüssel des Satzes in die Liste ein
  MIT wrap AS ( -- "materialisieren" wir die Datensätze, damit der Zugriff auf die Felder keine Multiplikation von InitPlan/SubPlan verursacht
    MIT 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 -- wir sortieren die Liste in der gewünschten Reihenfolge
    FROM
      T
  )
  SELECT
    list
  , list[1] rv
  , FALSE not_cross
  , 0 size
  FROM
    wrap
UNION ALL
  -- #2 : Wir lesen die Einträge des ersten Schlüssels, bis wir den zweiten überschreiten
  SELECT
    CASE
      -- wenn für den ersten Schlüssel nichts gefunden wurde
      WHEN X._r IS NOT DISTINCT FROM NULL THEN
        T.list[2:] -- wir entfernen ihn aus der Liste
      -- wenn wir den Anwendungs-Schlüssel des zweiten Eintrags NICHT überschritten haben
      WHEN X.not_cross THEN
        T.list -- wir ziehen einfach die gleiche Liste ohne Modifikationen durch
      -- wenn in der Liste der zweite Eintrag nicht mehr vorhanden ist
      WHEN T.list[2] IS NULL THEN
        -- wir geben einfach eine leere Liste zurück
        '{}'
      -- wir sortieren das Wörterbuch neu, indem wir den ersten Eintrag entfernen und den letzten der gefundenen hinzufügen
      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(
      MIT wrap AS ( -- "materialisieren" wir den Eintrag
        SELECT
          CASE
            -- wenn wir doch über den zweiten Eintrag "geschritten" sind
            WHEN NOT T.not_cross
              -- dann ist der benötigte Eintrag der erste aus der Liste
              THEN T.list[1]
            ELSE ( -- wenn wir nicht überschritten haben, bleibt der Schlüssel wie im vorherigen Eintrag - wir verlassen uns darauf
              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
          -- wenn der zweite Eintrag nicht mehr in der Liste ist, wir aber trotzdem etwas gefunden haben
          WHEN list[2] IS NULL AND _r IS DISTINCT FROM NULL THEN
            TRUE
          ELSE -- wir haben nichts gefunden oder "überschritten"
            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 -- hier begrenzen wir die Anzahl
    T.list IS DISTINCT FROM '{}' -- oder solange die Liste nicht leer ist
)
-- #3 : Wir "entfalten" die Einträge - die Reihenfolge ist durch den Aufbau garantiert
SELECT
  (rv).* 
FROM
  T
WHERE
  not_cross; -- wir nehmen nur die "nicht überschreitenden" Einträge

[auf explain.tensor.ru anschauen]

So haben wir 50 % der Datenlesungen gegen 20 % der Ausführungszeit getauscht.Das bedeutet, wenn Sie Grund zur Annahme haben, dass das Lesen langwierig sein könnte (zum Beispiel, wenn die Daten häufig nicht im Cache sind und von der Festplatte abgerufen werden müssen), können Sie in dieser Weise weniger vom Lesen abhängen.

Auf jeden Fall war die Ausführungszeit besser als im "naiven" ersten Ansatz. Welchen dieser 3 Ansätze Sie wählen, bleibt Ihnen überlassen.

Quelle: habr.com