PostgreSQL Antipatterns: Hit Dictionary auf Heavy JOIN

Wir setzen eine Reihe von Artikeln fort, die sich mit der Untersuchung unbekannter Möglichkeiten zur Verbesserung der Leistung von "scheinbar einfachen" PostgreSQL-Abfragen befassen:

• seltener Rekord erreicht die Mitte von JOIN
• Sisyphus verbinden Arrays
• schädlich JOIN und OR
• CTE CTE BEITRETEN

Glaube nicht, dass ich JOIN nicht so sehr mag ... :)

Aber oft ohne ist die Anfrage wesentlich produktiver als bei ihm. Aus diesem Grund werden wir heute versuchen, den ressourcenintensiven JOIN mithilfe eines Wörterbuchs vollständig zu beseitigen .

Ab PostgreSQL 12 können einige der unten beschriebenen Situationen etwas anders gespielt werden, da CTE standardmäßig nicht implementiert ist . Dieses Verhalten kann mit der Taste MATERIALIZED .

Viele "Fakten" zu einem begrenzten Wortschatz

Nehmen wir eine sehr reale Anwendung - Sie müssen die eingehenden Nachrichten oder aktiven Aufgaben mit Absendern auflisten:

 25.01 |  .. |    . 22.01 |  .. |    :   JOIN. 20.01 |  .. |   . 18.01 |  .. |    : JOIN    . 16.01 |  .. |   . 

In der abstrakten Welt sollten die Verfasser von Aufgaben gleichmäßig auf alle Mitarbeiter unserer Organisation verteilt sein. In der Realität kommen die Aufgaben jedoch in der Regel von einer relativ begrenzten Anzahl von Personen - "von Vorgesetzten" der Hierarchie nach oben oder "von Verbündeten" benachbarter Abteilungen (Analysten, Designer) Marketing ...).

Nehmen wir an, dass in unserer Organisation von 1000 Personen nur 20 Autoren (in der Regel sogar weniger) Aufgaben für jeden bestimmten Künstler festlegen und dieses Fachwissen verwenden , um die "traditionelle" Anfrage zu beschleunigen.


Wir zeigen die letzten 100 Aufgaben für einen bestimmten Künstler:

 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; 

[siehe EXPLAIN.TENSOR.RU]

Es stellt sich heraus, dass 1/3 der gesamten Zeit und 3/4 der Datenseiten gelesen wurden, um den Autor 100 Mal zu durchsuchen - für jede angezeigte Aufgabe. Aber wir wissen, dass es unter diesen hundert nur 20 verschiedene gibt - ist es möglich, dieses Wissen zu nutzen?

hstore Wörterbuch

Wir verwenden den hstore-Typ , um ein Schlüsselwert-Wörterbuch zu generieren:

 CREATE EXTENSION hstore 

Es reicht für uns, die ID des Autors und seinen Namen in das Wörterbuch aufzunehmen, damit wir später mit diesem Schlüssel extrahieren können:

 --    WITH T AS ( SELECT * FROM task WHERE owner_id = 777 ORDER BY task_date DESC LIMIT 100 ) --      , 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 )) ) --     SELECT * , (TABLE dict) -> author_id::text -- hstore -> key FROM T; 

[siehe EXPLAIN.TENSOR.RU]

Es dauerte 2 Mal weniger Zeit, um Informationen über Personen zu erhalten, und 7 Mal weniger, um Daten zu lesen ! Zusätzlich zur "Täuschung" haben uns diese Ergebnisse geholfen, die Massenextraktion von Datensätzen aus der Tabelle in einem einzigen Durchgang mit = ANY(ARRAY(...)) .

Tabelleneinträge: Serialisierung und Deserialisierung

Aber was ist, wenn wir nicht ein Textfeld, sondern einen ganzen Datensatz im Wörterbuch speichern müssen? In diesem Fall hilft uns die Fähigkeit von PostgreSQL , eine Tabelle als einen einzigen Wert zu schreiben :

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

Schauen wir uns an, was hier passiert ist:

1. Wir haben p als Alias ​​für den vollständigen Datensatz der Personentabelle verwendet und daraus ein Array zusammengestellt.
2. Dieses Array von Einträgen wurde in ein Array von Textzeichenfolgen (person [] :: text []) umgewandelt, um es als Array von Werten in das hstore-Wörterbuch aufzunehmen.
3. Nachdem wir den verknüpften Datensatz erhalten hatten, zogen wir ihn mit einem Schlüssel als Textzeichenfolge aus dem Wörterbuch .
4. Wir müssen den Text in den Wert des Typs der Personentabelle umwandeln (für jede Tabelle wird automatisch der gleichnamige Typ erstellt).
5. Mit (...).* Einen typisierten Datensatz in Spalten "implementieren".

json wörterbuch

Aber ein solcher Trick, wie wir ihn oben angewendet haben, funktioniert nicht, wenn es keinen entsprechenden Tabellentyp gibt, um ein "Auflösen" vorzunehmen. Genau die gleiche Situation tritt auf, und wenn wir als Datenquelle für die Serialisierung die CTE-Zeile und nicht die "echte" Tabelle verwenden .

In diesem Fall helfen uns die Funktionen für die Arbeit mit json :

 ... , p AS ( --   CTE SELECT * FROM person WHERE ... ) , dict AS ( SELECT json_object( --    json array_agg(id)::text[] , array_agg(row_to_json(p))::text[] --   json    ) FROM p ) SELECT * FROM T , LATERAL( SELECT * FROM json_to_record( ((TABLE dict) ->> author_id::text)::json --     json ) AS j(name text, birth_date date) --     ) j; 

Es ist zu beachten, dass wir bei der Beschreibung der Zielstruktur nicht alle Felder des Quellstrings auflisten können, sondern nur die, die wir wirklich benötigen. Wenn wir eine "native" Tabelle haben, ist es besser, die Funktion json_populate_record verwenden.

Wir haben noch einmal Zugriff auf das Wörterbuch, aber die Kosten für die json-de-Serialisierung sind ziemlich hoch. Daher ist es sinnvoll, diese Methode nur in einigen Fällen zu verwenden, wenn sich der „ehrliche“ CTE-Scan als schlechter herausstellt.

Leistung testen

Wir haben also zwei Möglichkeiten, Daten in ein Wörterbuch zu serialisieren - hstore / json_object . Darüber hinaus können die Arrays von Schlüsseln und Werten auf zwei Arten generiert werden, mit interner oder externer Konvertierung in Text: array_agg (i :: text) / array_agg (i) :: text [] .

Lassen Sie uns die Wirksamkeit verschiedener Arten der Serialisierung anhand eines rein synthetischen Beispiels überprüfen - 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; 

Unter PostgreSQL 11 dauert das Serialisieren in json bis zu einer Wörterbuchgröße von 2 ^ 12 Schlüsseln kürzer . Die Kombination von json_object und der "internen" Typkonvertierung von array_agg(i::text) ist am effizientesten.

Versuchen wir nun, den Wert jedes Schlüssels achtmal zu lesen. Wenn Sie nicht auf das Wörterbuch zugreifen, warum wird es dann benötigt?




Und ... bereits bei ungefähr 2 ^ 6 Schlüsseln verliert das Lesen aus dem JSON -Wörterbuch mehrmals an Bedeutung gegenüber dem Lesen aus dem Speicher, für JSONB geschieht dasselbe bei 2 ^ 9.

Abschließende Schlussfolgerungen:

• Wenn Sie einen JOIN mit wiederholt wiederholten Datensätzen erstellen müssen, ist es besser, den "Tabellenabgleich" zu verwenden.
• Wenn Ihr Wörterbuch voraussichtlich klein ist und Sie ein wenig davon lesen werden , können Sie json [b] verwenden.
• In allen anderen Fällen ist hstore + array_agg (i :: text) effizienter