🥇PostgreSQL Antipatterns: Wir setzen dem schweren JOIN mit einem Wörterbuch zu

Wir setzen die Artikelreihe fort, die sich mit wenig bekannten Methoden zur Verbesserung der Leistung "scheinbar einfacher" Abfragen in PostgreSQL beschäftigt:

Denken Sie nicht, dass ich JOIN so sehr nicht mag… 🙂

Aber oft ist eine Abfrage ohne JOIN deutlich leistungsfähiger als mit ihm. Daher wollen wir heute versuchen, überhaupt den ressourcenintensiven JOIN loszuwerden – mithilfe eines Wörterbuchs.

PostgreSQL Antipatterns: wir schlagen mit dem Wörterbuch auf bei schweren JOINs.

Seit PostgreSQL 12 können einige der unten beschriebenen Situationen aufgrund von der Nichtmaterialisierung von CTE standardmäßiganders auftreten. Dieses Verhalten kann durch die Angabe des Schlüssels wieder auf das früher gewohnte Verhalten zurückgeführt werden. MATERIALIZED.

Viele "Fakten" zu einem begrenzten Wörterbuch

Lassen Sie uns eine durchaus realistische praktische Aufgabe nehmen – wir müssen eine Liste erstellen eingehender Nachrichten oder aktiver Aufgaben mit Absendern:

25.01 | Ivanov I.I. | Bereiten Sie die Beschreibung des neuen Algorithmus vor.
22.01 | Ivanov I.I. | Schreiben Sie einen Artikel auf Habr: Leben ohne JOIN.
20.01 | Petrov P.P. | Helfen Sie, die Abfrage zu optimieren.
18.01 | Ivanov I.I. | Schreiben Sie einen Artikel auf Habr: JOIN unter Berücksichtigung der Datendistribution.
16.01 | Petrov P.P. | Helfen Sie, die Abfrage zu optimieren.

In einer abstrakten Welt sollten die Autoren der Aufgaben gleichmäßig unter allen Mitarbeitern unserer Organisation verteilt sein, aber in der Realität kommen Aufgaben in der Regel von einer ziemlich begrenzten Anzahl von Personen – „von der Leitung“ nach oben in der Hierarchie oder „von Kollegen“ aus benachbarten Abteilungen (Analytiker, Designer, Marketing, …).

Nehmen wir an, in unserer Organisation gibt es von 1000 Personen nur 20 Autoren (normalerweise sogar weniger), die Aufgaben an jeden bestimmten Ausführenden stellen und nutzen wir dieses Fachwissen, um die "traditionelle" Abfrage zu beschleunigen.

Script-Generator

-- Mitarbeiter
CREATE TABLE person AS
SELECT
  id
, repeat(chr(ascii('a') + (id % 26)), (id % 32) + 1) "name"
, '2000-01-01'::date - (random() * 1e4)::integer birth_date
FROM
  generate_series(1, 1000) id;

ALTER TABLE person ADD PRIMARY KEY(id);

-- Aufgaben mit angegebenem Verteilen
CREATE TABLE task AS
WITH aid AS (
  SELECT
    id
  , array_agg((random() * 999)::integer + 1) aids
  FROM
    generate_series(1, 1000) id
  , generate_series(1, 20)
  GROUP BY
    1
)
SELECT
  *
FROM
  (
    SELECT
      id
    , '2020-01-01'::date - (random() * 1e3)::integer task_date
    , (random() * 999)::integer + 1 owner_id
    FROM
      generate_series(1, 100000) id
  ) T
, LATERAL(
    SELECT
      aids[(random() * (array_length(aids, 1) - 1))::integer + 1] author_id
    FROM
      aid
    WHERE
      id = T.owner_id
    LIMIT 1
  ) a;

ALTER TABLE task ADD PRIMARY KEY(id);
CREATE INDEX ON task(owner_id, task_date);
CREATE INDEX ON task(author_id);

Zeigen wir die letzten 100 Aufgaben für einen bestimmten Ausführenden:

SELECT
  task.*
, person.name
FROM
  task
LEFT JOIN
  person
    ON person.id = task.author_id
WHERE
  owner_id = 777
ORDER BY
  task_date DESC
LIMIT 100;

PostgreSQL Antipatterns: wir schlagen mit dem Wörterbuch auf bei schweren JOINs.
[sehen Sie sich explain.tensor.ru an]

Es stellt sich heraus, dass 1/3 der gesamten Zeit und 3/4 der Lesevorgänge wurden nur dafür aufgewendet, 100 Mal nach dem Autor zu suchen – für jede ausgegebene Aufgabe. Aber wir wissen, dass unter diesen Hundert nur 20 verschiedene – können wir dieses Wissen nutzen?

hstore-Wörterbuch

Lassen Sie uns hstore-Typ zum Erzeugen eines "Wörterbuchs" Schlüssel-Wert:

CREATE EXTENSION hstore

Im Wörterbuch müssen wir nur die ID des Autors und seinen Namen speichern, um später nach diesem Schlüssel extrahieren zu können:

-- Zielabfrage generieren
WITH T AS (
  SELECT
    *
  FROM
    task
  WHERE
    owner_id = 777
  ORDER BY
    task_date DESC
  LIMIT 100
)
-- Wörterbuch für einzigartige Werte generieren
, dict AS (
  SELECT
    hstore( -- hstore(keys::text[], values::text[])
      array_agg(id)::text[]
    , array_agg(name)::text[]
    )
  FROM
    person
  WHERE
    id = ANY(ARRAY(
      SELECT DISTINCT
        author_id
      FROM
        T
    ))
)
-- Zugeordnete Werte des Wörterbuchs abrufen
SELECT
  *
, (TABLE dict) -> author_id::text -- hstore -> key
FROM
  T;

PostgreSQL Antipatterns: wir schlagen mit dem Wörterbuch auf bei schweren JOINs.
[sehen Sie sich explain.tensor.ru an]

Für Informationen über Personen wurde zwei Mal weniger Zeit und sieben Mal weniger Daten gelesen! Neben dem "Wörterbüchermachen" konnten wir diese Ergebnisse auch durch massive Extraktionen von Datensätzen aus der Tabelle in einem einzigen Durchgang mithilfe von = ANY(ARRAY(...)).

Datensatz: Serialisierung und Deserialisierung

Aber was tun, wenn wir im Wörterbuch nicht nur ein Textfeld, sondern einen ganzen Datensatz speichern müssen? In diesem Fall hilft uns die Fähigkeit von PostgreSQL mit einem Tabellendatensatz als einem einzelnen Wert zu arbeiten:

...
, dict AS (
  SELECT
    hstore(
      array_agg(id)::text[]
    , array_agg(p)::text[] -- Magie #1
    )
  FROM
    person p
  WHERE
    ...
)
SELECT
  *
, (((TABLE dict) -> author_id::text)::person).* -- Magie #2
FROM
  T;

Lassen Sie uns untersuchen, was hier tatsächlich passiert ist:

Wir haben genommen p als Alias für den vollständigen Datensatz der Tabelle person und aus diesen einen Array zusammengestellt.
Dieses Array von Datensätzen wurde in ein Array von Textstrings (person[]::text[]) umgewandelt, um es im hstore-Wörterbuch als Array von Werten aufzunehmen.
Bei der Ausgabe des zugehörigen Datensatzes haben wir ihn über den Schlüssel aus dem Wörterbuch abgerufen als Textstring.
Wir müssen den Text in einen Typ der Tabelle verwandeln person (für jede Tabelle wird automatisch ein gleichnamiger Typ erstellt).
Wir haben den typisierten Datensatz mit Hilfe von (...).*.

json-Wörterbuch"

Aber dieser Trick, wie wir ihn oben angewendet haben, wird nicht funktionieren, wenn es keinen entsprechenden Tabellentyp gibt, um eine "Cast"-Umwandlung vorzunehmen. Eine ganz ähnliche Situation tritt auf, wenn wir versuchen, eine CTE-Zeile anstelle einer "echten" Tabelle als Datenquelle für die Serialisierung zu verwenden..

In diesem Fall können uns Funktionen zur Arbeit mit JSON helfen.:

...
, p AS ( -- das ist schon ein CTE
  SELECT
    *
  FROM
    person
  WHERE
    ...
)
, dict AS (
  SELECT
    json_object( -- jetzt ist das schon JSON
      array_agg(id)::text[]
    , array_agg(row_to_json(p))::text[] -- und innerhalb JSON für jede Zeile
    )
  FROM
    p
)
SELECT
  *
FROM
  T
, LATERAL(
    SELECT
      *
    FROM
      json_to_record(
        ((TABLE dict) ->> author_id::text)::json -- aus dem Wörterbuch als JSON extrahiert
      ) AS j(name text, birth_date date) -- die benötigte Struktur ausgefüllt
  ) j;

Es ist zu beachten, dass wir bei der Beschreibung der Zielstruktur nicht alle Felder der ursprünglichen Zeile aufzählen müssen, sondern nur die, die wir tatsächlich benötigen. Wenn wir jedoch eine "native" Tabelle haben, ist es besser, die Funktion json_populate_record.

Der Zugriff auf das Wörterbuch erfolgt nach wie vor einmalig, aber die Kosten für die JSON-[de]Serialisierung sind erheblich., deshalb sollte man diesen Ansatz nur in bestimmten Fällen verwenden, wenn ein "ehrlicher" CTE-Scan schlechter abschneidet.

Leistung testen

So haben wir zwei Möglichkeiten zur Serialisierung von Daten in ein Wörterbuch erhalten - hstore / json_object.Darüber hinaus können die Arrays von Schlüsseln und Werten ebenfalls auf zwei Arten erzeugt werden, mit interner oder externer Umwandlung in Text: array_agg(i::text) / array_agg(i)::text[].

Überprüfen wir die Effizienz verschiedener Arten der Serialisierung anhand eines rein synthetischen Beispiels - wir serialisieren eine unterschiedliche Anzahl von Schlüsseln.:

WITH dict AS (
  SELECT
    hstore(
      array_agg(i::text)
    , array_agg(i::text)
    )
  FROM
    generate_series(1, ...) i
)
TABLE dict;

Bewertungsskript: Serialisierung

WITH T AS (
  SELECT
    *
  , (
      SELECT
        regexp_replace(ea[array_length(ea, 1)], '^Execution Time: (d+.d+) ms$', '1')::real et
      FROM
        (
          SELECT
            array_agg(el) ea
          FROM
            dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$
              explain analyze
              WITH dict AS (
                SELECT
                  hstore(
                    array_agg(i::text)
                  , array_agg(i::text)
                  )
                FROM
                  generate_series(1, $$ || (1 << v) || $$) i
              )
              TABLE dict
            $$) T(el text)
        ) T
    ) et
  FROM
    generate_series(0, 19) v
  ,   LATERAL generate_series(1, 7) i
  ORDER BY
    1, 2
)
SELECT
  v
, avg(et)::numeric(32,3)
FROM
  T
GROUP BY
  1
ORDER BY
  1;

PostgreSQL Antipatterns: wir schlagen mit dem Wörterbuch auf bei schweren JOINs.

In PostgreSQL 11 benötigt die Serialisierung in JSON bis zu einer Wörterbuchgröße von 2^12 Schlüsseln weniger Zeit.Dabei ist die effektivste Kombination json_object und "interne" Typumwandlung array_agg(i::text)..

Lassen Sie uns nun versuchen, den Wert jedes Schlüssels achtmal zu lesen - denn wenn auf das Wörterbuch nicht zugegriffen wird, wozu ist es dann gut?

Bewertungsskript: Lesen aus dem Wörterbuch

WITH T AS (
  SELECT
    *
  , (
      SELECT
        regexp_replace(ea[array_length(ea, 1)], '^Execution Time: (d+.d+) ms$', '1')::real et
      FROM
        (
          SELECT
            array_agg(el) ea
          FROM
            dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$
              explain analyze
              WITH dict AS (
                SELECT
                  json_object(
                    array_agg(i::text)
                  , array_agg(i::text)
                  )
                FROM
                  generate_series(1, $$ || (1 << v) || $$) i
              )
              SELECT
                (TABLE dict) -> (i % ($$ || (1 << v) || $$) + 1)::text
              FROM
                generate_series(1, $$ || (1 << (v + 3)) || $$) i
            $$) T(el text)
        ) T
    ) et
  FROM
    generate_series(0, 19) v
  , LATERAL generate_series(1, 7) i
  ORDER BY
    1, 2
)
SELECT
  v
, avg(et)::numeric(32,3)
FROM
  T
GROUP BY
  1
ORDER BY
  1;

PostgreSQL Antipatterns: wir schlagen mit dem Wörterbuch auf bei schweren JOINs.

Und… bereits ungefähr bei 2^6 Schlüsseln beginnt das Lesen aus dem JSON-Wörterbuch deutlich schlechter abzuschneiden im Vergleich zum Lesen aus hstore, für jsonb passiert dasselbe bei 2^9.

Fazit:

wenn man einen JOIN mit mehrfach wiederholten Datensätzen erstellen muss, ist es besser, die Tabelle zu "wörterbuchisieren".

Wenn Ihr Wörterbuch erwartungsgemäß klein ist und Sie nur wenig daraus lesen werden, können Sie json[b] verwenden.

In allen anderen Fällen wird hstore + array_agg(i::text) effizienter sein.

Quelle: habr.com