ProHoster > Blog > Verwaltung > PostgreSQL-Antimuster: CTE x CTE

PostgreSQL-Antimuster: CTE x CTE

Aufgrund meiner Tätigkeit muss ich mich mit Situationen auseinandersetzen, in denen ein Entwickler eine Anfrage schreibt und denkt: „Die Basis ist smart, sie regelt alles selbst!«

In einigen Fällen (teils aus Unkenntnis der Fähigkeiten der Datenbank, teils aufgrund vorzeitiger Optimierungen) führt dieser Ansatz zum Auftreten von „Frankensteins“.

Zunächst gebe ich ein Beispiel für eine solche Anfrage:

-- для каждой ключевой пары находим ассоциированные значения полей
WITH RECURSIVE cte_bind AS (
  SELECT DISTINCT ON (key_a, key_b)
    key_a a
  , key_b b
  , fld1 bind_fld1
  , fld2 bind_fld2
  FROM
    tbl
)
-- находим min/max значений для каждого первого ключа
, cte_max AS (
  SELECT
    a
  , max(bind_fld1) bind_fld1
  , min(bind_fld2) bind_fld2
  FROM
    cte_bind
  GROUP BY
    a
)
-- связываем по первому ключу ключевые пары и min/max-значения
, cte_a_bind AS (
  SELECT
    cte_bind.a
  , cte_bind.b
  , cte_max.bind_fld1
  , cte_max.bind_fld2
  FROM
    cte_bind
  INNER JOIN
    cte_max
      ON cte_max.a = cte_bind.a
)
SELECT * FROM cte_a_bind;

Um die Qualität einer Anfrage inhaltlich zu bewerten, erstellen wir einen beliebigen Datensatz:

CREATE TABLE tbl AS
SELECT
  (random() * 1000)::integer key_a
, (random() * 1000)::integer key_b
, (random() * 10000)::integer fld1
, (random() * 10000)::integer fld2
FROM
  generate_series(1, 10000);
CREATE INDEX ON tbl(key_a, key_b);

Es stellt sich heraus, dass Das Auslesen der Daten dauerte weniger als ein Viertel der Zeit Abfrageausführung:

PostgreSQL-Antimuster: CTE x CTE[siehe EXPLAIN.tensor.ru]

Stück für Stück auseinander nehmen

Schauen wir uns die Anfrage genauer an und staunen:

  1. Warum steht hier WITH RECURSIVE, wenn es keine rekursiven CTEs gibt?
  2. Warum Min/Max-Werte in einem separaten CTE gruppieren, wenn sie dann sowieso an die Originalstichprobe gebunden sind?
    +25 % Zeit
  3. Warum am Ende ein unbedingtes „SELECT * FROM“ verwenden, um den vorherigen CTE zu wiederholen?
    +14 % Zeit

In diesem Fall hatten wir großes Glück, dass Hash Join für die Verbindung ausgewählt wurde und nicht Nested Loop, denn dann hätten wir nicht nur einen CTE-Scan-Durchlauf, sondern 10K erhalten!

ein wenig über CTE ScanHier müssen wir uns daran erinnern Der CTE-Scan ähnelt dem Seq-Scan - also keine Indizierung, sondern nur eine vollständige Suche, die erforderlich wäre 10K x 0.3ms = 3000ms für Zyklen von cte_max oder 1K x 1.5ms = 1500ms beim Durchlaufen von cte_bind!
Was wollten Sie eigentlich als Ergebnis erreichen? Ja, normalerweise ist das die Frage, die irgendwo in der fünften Minute der Analyse von „dreistöckigen“ Abfragen auftaucht.

Wir wollten für jedes eindeutige Schlüsselpaar eine Ausgabe erstellen Min/Max aus der Gruppe nach key_a.
Also nutzen wir es dafür Fensterfunktionen:

SELECT DISTINCT ON(key_a, key_b)
	key_a a
,	key_b b
,	max(fld1) OVER(w) bind_fld1
,	min(fld2) OVER(w) bind_fld2
FROM
	tbl
WINDOW
	w AS (PARTITION BY key_a);

PostgreSQL-Antimuster: CTE x CTE
[siehe EXPLAIN.tensor.ru]

Da das Auslesen der Daten bei beiden Optionen etwa gleich etwa 4–5 ms dauert, bedeutet das für uns einen Zeitgewinn -32% - das ist in seiner reinsten Form Last von der Basis-CPU entfernt, wenn eine solche Anfrage oft genug ausgeführt wird.

Im Allgemeinen sollten Sie die Basis nicht dazu zwingen, „das Runde zu tragen, das Quadrat zu rollen“.

Source: habr.com

Kommentar hinzufügen