PostgreSQL Antipatterns: Wir treffen den JOIN mit einem Wörterbuch

Wir setzen die Reihe von Artikeln fort, die sich mit der Erforschung wenig bekannter Methoden zur Leistungssteigerung „scheinbar einfacher“ Abfragen in PostgreSQL beschäftigen:

• seltene Einträge erreichen die Mitte des JOINs
• Sisyphus JOIN von Arrays
• schädliche JOIN und OR
• CTE JOIN CTE

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

Aber oft wird die Anfrage ohne ihn deutlich leistungsfähiger als mit ihm. Daher versuchen wir heute, ganz auf den ressourcenintensiven JOIN zu verzichten – mit Hilfe eines Wörterbuchs.

Ab PostgreSQL 12 kann ein Teil der unten beschriebenen Situationen etwas anders auftreten, da CTE standardmäßig nicht materialisiert werden. Dieses Verhalten kann durch Angabe des Schlüssels zurückgesetzt werden, MATERIALIZED.

Viele „Fakten“ über ein eingeschränktes Wörterbuch

Lassen Sie uns eine ganz reale Anwendungsaufgabe betrachten – wir müssen eine Liste von eingehenden Nachrichten oder aktiven Aufgaben mit Absendern erstellen:

25.01 | Ivanov I.I. | Beschreiben Sie den neuen Algorithmus.
22.01 | Ivanov I.I. | Schreiben Sie einen Artikel auf Habr: Leben ohne JOIN.
20.01 | Petrov P.P. | Helfen Sie bei der Optimierung der Anfrage.
18.01 | Ivanov I.I. | Schreiben Sie einen Artikel auf Habr: JOIN unter Berücksichtigung der Datenverteilung.
16.01 | Petrov P.P. | Helfen Sie bei der Optimierung der Anfrage.

In einer abstrakten Welt sollten die Autoren der Aufgaben gleichmäßig auf alle Mitarbeiter unserer Organisation verteilt sein, aber in der Realität kommen die Aufgaben in der Regel von einer ziemlich begrenzten Anzahl von Personen – „von der oberen Führung“ oder „von den Kollegen“ aus benachbarten Abteilungen (Analysten, Designer, Marketing, …).

Nehmen wir an, dass in unserer Organisation von 1000 Personen nur 20 Autoren (in der Regel sogar weniger) Aufgaben an jeden einzelnen Ausführer stellen und nutzen wir dieses Fachwissenzum Beschleunigen der „traditionellen“ Anfrage.

Skript-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 gegebener Verteilung
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);

Wir zeigen 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;

[auf explain.tensor.ru anschauen]

Es stellt sich heraus, dass 1/3 der gesamten Zeit und 3/4 der Lesevorgänge Datenblätter wurden nur erstellt, um 100 Mal nach dem Autor zu suchen — für jede ausgegebene Aufgabe. Aber wir wissen, dass unter diesen hunderten es insgesamt 20 verschiedene — könnte man dieses Wissen nicht nutzen?

hstore-Wörterbuch

Lassen Sie uns hstore-Typ zur Erstellung eines Schlüssel-Wert-Wörterbuchs:

CREATE EXTENSION hstore

Im Wörterbuch reicht es, die ID des Autors und seinen Namen zu speichern, um später anhand dieses Schlüssels abrufen zu können:

-- Zielabfrage erstellen
WITH T AS (
  SELECT
    *
  FROM
    task
  WHERE
    owner_id = 777
  ORDER BY
    task_date DESC
  LIMIT 100
)
-- Wörterbuch für eindeutige Werte erstellen
, 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
    ))
)
-- Verknüpfte Werte aus dem Wörterbuch abrufen
SELECT
  *
, (TABLE dict) -> author_id::text -- hstore -> key
FROM
  T;

[auf explain.tensor.ru anschauen]

Für den Erhalt von Informationen über Personen wurde die Zeit um das 2-fache und die gelesenen Daten um das 7-fache reduziert! Neben der „Wörterbuchbildung“ haben uns diese Ergebnisse auch durch massives Abrufen von Datensätzen aus der Tabelle in einem einzigen Durchgang mit Hilfe von = ANY(ARRAY(...)).

Tabellenaufzeichnungen: Serialisierung und Deserialisierung

Was ist jedoch zu tun, wenn wir im Wörterbuch nicht nur ein Textfeld, sondern einen gesamten Datensatz speichern müssen? In diesem Fall hilft uns die Fähigkeit von PostgreSQL mit Datensätzen in der Tabelle als einem einzigen 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;

Lass uns untersuchen, was hier überhaupt passiert ist:

1. Wir haben p als Alias für den vollständigen Datensatz der Tabelle person genommen. p als Alias für den vollständigen Datensatz der Tabelle person und haben aus ihnen ein Array erstellt.
2. Dieses Array von Einträgen wurde umgewandelt in ein Array von Textstrings (person[]::text[]), um es in das hstore-Wörterbuch als Array von Werten einzufügen.
3. Beim Abrufen des verbundenen Eintrags haben wir ihn aus dem Wörterbuch über den Schlüssel als Textstring entnommen.
4. Den Text müssen wir in einen Tabellenwert umwandeln für die person-Tabelle (für jede Tabelle wird automatisch ein gleichnamiger Typ erstellt).
5. Wir haben den typisierten Eintrag in Spalten mit Hilfe von (...).*.

json-Wörterbuch

Aber so ein Trick, wie wir ihn oben angewendet haben, funktioniert nicht, wenn es keinen entsprechenden Tabellentyp gibt, um eine «Umwandlung» vorzunehmen. Genau die gleiche Situation tritt auf, wenn wir versuchen, als Datenquelle für die Serialisierung eine CTE-Zeile und nicht eine „echte“ Tabelle zu verwenden..

In diesem Fall helfen uns Funktionen zur Arbeit mit json:

...
, p AS ( -- das ist bereits CTE
  SELECT
    *
  FROM
    person
  WHERE
    ...
)
, dict AS (
  SELECT
    json_object( -- jetzt ist das bereits 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 entnommen
      ) AS j(name text, birth_date date) -- benötigte Struktur ausgefüllt
  ) j;

Es ist zu beachten, dass wir bei der Beschreibung der Zielstruktur nicht alle Felder der ursprünglichen Zeile auflisten 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.

zu nutzen. Der Zugriff auf das Wörterbuch erfolgt nach wie vor einmalig, aber die Kosten für json-[de]serialisierung sind ziemlich hoch,, deshalb ist es sinnvoll, diese Methode nur in bestimmten Fällen zu verwenden, wenn ein „ehrlicher“ CTE-Scan schlechter abschneidet.

Wir testen die Leistung.

Also 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[].

Schauen wir uns die Effizienz verschiedener Arten der Serialisierung an einem rein synthetischen Beispiel an — wir serialisieren unterschiedliche Mengen 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.

MIT T ALS (
  WÄHLEN
    *
  , (
      WÄHLEN
        regexp_replace(ea[array_length(ea, 1)], '^Ausführungszeit: (d+.d+) ms$', '1')::real et
      AUS
        (
          WÄHLEN
            array_agg(el) ea
          AUS
            dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$
              explain analyze
              MIT dict ALS (
                WÄHLEN
                  hstore(
                    array_agg(i::text)
                  , array_agg(i::text)
                  )
                AUS
                  generate_series(1, $$ || (1 << v) || $$) i
              )
              TABELLE dict
            $$) T(el text)
        ) T
    ) et
  AUS
    generate_series(0, 19) v
  ,   LATERAL generate_series(1, 7) i
  BESTELLEN NACH
    1, 2
)
WÄHLEN
  v
, avg(et)::numeric(32,3)
AUS
  T
GROUP BY
  1
BESTELLEN NACH
  1;

Auf PostgreSQL 11 etwa bis zur Größe eines Wörterbuchs mit 2^12 Schlüsseln Die Serialisierung in JSON benötigt weniger Zeit. Dabei ist die Kombination von json_object und „interner“ Typumwandlung am effektivsten array_agg(i::text).

Jetzt versuchen wir, den Wert jedes Schlüssels 8 Mal zu lesen — denn wenn man nicht auf das Wörterbuch zugreift, wozu wird es dann benötigt?

Bewertungsskript: Lesen aus dem Wörterbuch

MIT T ALS (
  WÄHLEN
    *
  , (
      WÄHLEN
        regexp_replace(ea[array_length(ea, 1)], '^Ausführungszeit: (d+.d+) ms$', '1')::real et
      AUS
        (
          WÄHLEN
            array_agg(el) ea
          AUS
            dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$
              explain analyze
              MIT dict ALS (
                WÄHLEN
                  json_object(
                    array_agg(i::text)
                  , array_agg(i::text)
                  )
                AUS
                  generate_series(1, $$ || (1 << v) || $$) i
              )
              WÄHLEN
                (TABELLE dict) -> (i % ($$ || (1 << v) || $$) + 1)::text
              AUS
                generate_series(1, $$ || (1 << (v + 3)) || $$) i
            $$) T(el text)
        ) T
    ) et
  AUS
    generate_series(0, 19) v
  , LATERAL generate_series(1, 7) i
  BESTELLEN NACH
    1, 2
)
WÄHLEN
  v
, avg(et)::numeric(32,3)
AUS
  T
GROUP BY
  1
BESTELLEN NACH
  1;

Und... ungefähr Bei 2^6 Schlüsseln beginnt das Lesen aus dem JSON-Wörterbuch deutlich hinter dem Lesen aus hstore zurückzubleiben, für jsonb ist das Gleiche bei 2^9 der Fall.

Zusammenfassende Schlussfolgerungen:

• Wenn Sie JOIN mit mehrfach wiederholten Datensätzen — ist es besser, die „Wörterbuchbildung“ der Tabelle zu verwenden
• Wenn Ihr Wörterbuch erwartungsgemäß klein ist und Sie nur wenig daraus lesen werden — kann json[b] verwendet werden
• In allen anderen Fällen hstore + array_agg(i::text) wird effektiver sein

Quelle: habr.com