Dieser Artikel ist eine Fortsetzung der Geschichte über Neuheiten in PostgreSQL 12. Wir haben SQL / JSON (JSONPath-Patch) bereits im Artikel
„Was sie beim Einfrieren von Features 2019 eingefroren haben“ analysiert. Teil I. JSONPath “ , jetzt ist CTE an der Reihe.
Cte
CTE ist Common Table Expression - Common Table Expression, sie werden auch als WITH-Konstrukte bezeichnet. Tatsächlich werden temporäre Tabellen erstellt, die jedoch nur für eine Abfrage und nicht für die Sitzung vorhanden sind. Sie können innerhalb dieser Anfrage abgerufen werden. Eine solche Anfrage ist gut gelesen, verständlich und bei Bedarf leicht zu ändern. Dies ist eine sehr beliebte Sache, und sie ist seit langer Zeit in PostgreSQL.
Aber Annehmlichkeiten können teuer sein. Die Probleme hängen mit der Materialisierung des Ausdrucks nach AS innerhalb des WITH ... AS () -Konstrukts zusammen. Es wird auch als interner Ausdruck bezeichnet und berechnet, bevor Sie mit der Berechnung des Restes beginnen. Es kann nicht in eine Abfrage der obersten Ebene eingebettet werden (kein Inlining). Bei der Planung dieses Ausdrucks wird der Rest der Abfrage nicht berücksichtigt. Dieses Verhalten wird als Hindernis für die Optimierung oder das Fechten bezeichnet. Darüber hinaus erfordert die Materialisierung selbst work_mem. Und wenn die Stichprobe groß ist, beginnen die Probleme (zum Beispiel gibt es einen
Bericht von Ivan Frolkov auf der PGConf 2019).
Das Verstecken mit dem Optimierer, den wir unten analysieren werden, ist im Allgemeinen kein Fehler, sondern eine Funktion. Natürlich gibt es Situationen, in denen die vorläufige Berechnung eines Teils eines Ausdrucks beispielsweise unnötige wiederholte Operationen in rekursiven Abfragen eliminiert. Auf der anderen Seite verwendeten viele Entwickler CTE als Ansicht, ohne über die Barriere nachzudenken, und infolgedessen wurden Anforderungen von CTE nicht nur langsamer als äquivalente (aber komplexere) Anforderungen mit Unterabfragen ausgeführt, sondern auch um Größenordnungen langsamer. Nach dem Abwägen der Vor- und Nachteile hat die Community einen entscheidenden Schritt getan: Sie hat das Standardverhalten geändert.
Wir werden die Arbeit von CTE auf einer solchen Platte beobachten:
CREATE TABLE xytable AS SELECT x, x AS y FROM generate_series(1,10000000) AS x; CREATE INDEX ON xytable(x,y);
Table "public.xytable" Column | Type | Collation | Nullable | Default --------------+---------+------------------+----------------+--------- x | integer | | | y | integer | | | Indexes: "xytable_x_y_idx" btree (x, y)
Beginnen wir mit einer einfachen Anfrage:
SELECT * FROM xytable WHERE x=2 AND y>1; QUERY PLAN
Alles wird sofort berücksichtigt, nur der Index wird verwendet.
Eine Abfrage mit einer Unterabfrage, die dasselbe berechnet, jedoch mit einer etwas komplizierteren Syntax:
SELECT * FROM (SELECT * FROM xytable WHERE y>1) AS t WHERE x=2; QUERY PLAN
Alles ist in Ordnung, sehr schnelle Indexberechnung.
Und jetzt noch eine logisch äquivalente Anfrage, aber mit CTE:
WITH yy AS ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2; QUERY PLAN
Eine solche Verzögerung ist bereits mit bloßem Auge sichtbar. Sie werden keinen Kaffee trinken, aber es bleibt genügend Zeit, um in die Post zu schauen (wenn wir die 11. Version oder früher haben).
Und Folgendes ist passiert: Bei Unterabfragen hat der Optimierer sofort erkannt, dass die Bedingungen x = 2 und y> 1 zu einem Filter kombiniert und nach Index durchsucht werden können. Im Fall von CTE hat der Optimierer keine Wahl: Er muss sich zuerst mit der Bedingung innerhalb des WITH ... AS-Konstrukts befassen, das Ergebnis materialisieren und erst dann daran arbeiten.
Und hier geht es nicht darum, dass für die Materialisierung Ressourcen erforderlich sind: Wenn die Bedingung y <3 ist, müssen nicht Millionen von Datensätzen materialisiert werden, sondern nur 2. Die ungeheure Zeit für eine einfache Abfrage wird für die sequentielle Suche aufgewendet, da der Optimierer die Indexsuche nicht verwenden kann, da Damit der zusammengesetzte Index auf x und erst dann auf y basiert und er nichts über eine Abfrage mit der Bedingung x = 2 weiß, bis er die interne CTE-Bedingung erfüllt. Es ist jenseits der Barriere.
Vor PostgreSQL 12 war die Standardeinstellung Materialisierung, jetzt fehlt sie. Wir starten die gleiche Anfrage basierend auf der neuen Version. Barriere sieht der Optimierer sozusagen sofort die gesamte Anfrage:
WITH yy AS ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2;
QUERY PLAN ------------------------------------------ Index Only Scan using xytable_x_y_idx1 on xytable (cost=0.43..8.46 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=1) Index Cond: ((x = 2) AND (y > 1)) Heap Fetches: 1 Planning Time: 0.067 ms Execution Time: 0.029 ms (5 rows)
Der Optimierer lernte sofort, die Bedingungen in der optimalen Reihenfolge zu kombinieren - wie es bei den Unterabfragen der Fall war.
Die Standardeinstellungen sind jedoch Standardeinstellungen, und für die vollständige Übernahme der Situation gibt es in Version 12 eine kontrollierte, kontrollierte Materialisierung von CTE:
WITH cte_name AS [NOT] MATERIALIZED
Lassen Sie uns materialisieren:
EXPLAIN ANALYZE WITH yy AS MATERIALIZED ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2;
QUERY PLAN --------------------------- CTE Scan on yy (cost=356423.68..581401.19 rows=49995 width=8) (actual time=661.038..3603.292 rows=1 loops=1) Filter: (x = 2) Rows Removed by Filter: 9999998 CTE yy -> Bitmap Heap Scan on cte (cost=187188.18..356423.68 rows=9999000 width=8) (actual time=661.032..2102.040 rows=9999999 loops=1) Recheck Cond: (y > 1) Heap Blocks: exact=44248 -> Bitmap Index Scan on xytable_x_y_idx1 (cost=0.00..184688.43 rows=9999000 width=0) (actual time=655.519..655.519 rows=9999999 loops=1) Index Cond: (y > 1) Planning Time: 0.086 ms Execution Time: 3612.840 ms (11 rows)
Alles ist wie in 11 und davor können Sie E-Mails im Standby-Modus der Abfrageergebnisse anzeigen. Wir verbieten Materialisierung, entfernen die Barriere:
EXPLAIN ANALYZE WITH yy AS NOT MATERIALIZED ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2; QUERY PLAN
Wieder keine Pause: es zählt sofort.
Verbleibende Nuancen. Aber wichtige Nuancen.
CTE tritt standardmäßig auf, wenn mehrmals darauf zugegriffen wird.
Auf den ersten Blick ist die Materialisierung in solchen Fällen eine vernünftige Entscheidung: Warum zweimal dasselbe berechnen? In der Praxis führt dies oft zu dem, was wir oben beobachtet haben. Um die Ablehnung der Materialisierung zu erzwingen, muss der Optimierer explizit bestellt werden: NICHT MATERIALISIERT.
Wir führen ohne NICHT MATERIALISIERTE Anfrage mit double WHERE aus:
WITH yy AS ( SELECT * FROM xytable WHERE y > 1) SELECT ( SELECT count(*) FROM yy WHERE x=2), ( SELECT count(*) FROM yy WHERE x=2);
QUERY PLAN --------------------------------------------------------------------------- Result (actual time=3922.274..3922.275 rows=1 loops=1) CTE yy -> Seq Scan on xytable (actual time=0.023..1295.262 rows=9999999 loops=1) Filter: (y > 1) Rows Removed by Filter: 1 InitPlan 2 (returns $1) -> Aggregate (actual time=3109.687..3109.687 rows=1 loops=1) -> CTE Scan on yy (actual time=0.027..3109.682 rows=1 loops=1) Filter: (x = 2) Rows Removed by Filter: 9999998 InitPlan 3 (returns $2) -> Aggregate (actual time=812.580..812.580 rows=1 loops=1) -> CTE Scan on yy yy_1 (actual time=0.016..812.575 rows=1 loops=1) Filter: (x = 2) Rows Removed by Filter: 9999998 Planning Time: 0.136 ms Execution Time: 3939.848 ms (17 rows)
Und jetzt schreiben wir ausdrücklich ein Materialisierungsverbot:
WITH yy AS NOT MATERIALIZED ( SELECT * FROM xytable WHERE y > 1) SELECT ( SELECT count(*) FROM yy WHERE x=2), ( SELECT count(*) FROM yy WHERE x=2);
QUERY PLAN --------------------------------------------------------------------------- Result (actual time=0.035..0.035 rows=1 loops=1) InitPlan 1 (returns $0) -> Aggregate (actual time=0.024..0.024 rows=1 loops=1) -> Index Only Scan using xytable_x_y_idx on xytable (actual time=0.019..0.020 rows=1 loops=1) Index Cond: ((x = 2) AND (y > 1)) Heap Fetches: 1 InitPlan 2 (returns $1) -> Aggregate (actual time=0.006..0.006 rows=1 loops=1) -> Index Only Scan using xytable_x_y_idx on xytable cte_1 (actual time=0.004..0.005 rows=1 loops=1) Index Cond: ((x = 2) AND (y > 1)) Heap Fetches: 1 Planning Time: 0.253 ms Execution Time: 0.075 ms (13 rows)
Das Schreiben von CTEs wird immer ausgeführt, und CTEs, auf die nicht verwiesen wird, werden nie ausgeführt.
Dies geht aus dem Plan hervor: not_executed ist nicht enthalten. Dies gilt für frühere Versionen, es ist jedoch zu beachten, dass das Konstrukt (NOT) MATERIALIZED für den ausführbaren Ausdruck in Version 12 gilt.
EXPLAIN (COSTS OFF) WITH yy AS ( SELECT * FROM xytable WHERE y > 1), not_executed AS ( SELECT * FROM xytable), always_executed AS ( INSERT INTO xytable VALUES(2,2) RETURNING *) SELECT FROM yy WHERE x=2;
QUERY PLAN ----------------------------- CTE Scan on yy Filter: (x = 2) CTE yy -> Seq Scan on cte Filter: (y > 1) CTE always_executed -> Insert on cte cte_1 -> Result (5 rows)
Und noch eine Regel:
rekursive Abfragen mit WITH werden immer materialisiert.
Es ist immer und nicht standardmäßig. Wenn wir den Optimierer bestellen: NICHT MATERIALISIERT, tritt kein Fehler auf, und die Materialisierung bleibt bestehen. Dies ist eine gemeinschaftsbewusste Entscheidung.
Wir werden das anschauliche Beispiel Hausaufgaben betrachten. Das ist alles für heute.
In diesem Teil des Berichts über das Neue in CTE werden Beispiele und Fragmente aus dem Bericht „Postgres 12 in Etudes“ verwendet, den Oleg Bartunov am 9. April dieses Jahres bei Saint Highload ++ in St. Petersburg las.In der nächsten Serie -
KNN .