🥇PostgreSQL-antipatronen: CTE x CTE

Door mijn werk krijg ik te maken met situaties waarin een ontwikkelaar een verzoek schrijft en denkt “De basis is slim, hij kan alles zelf aan!«

In sommige gevallen (deels door onwetendheid over de mogelijkheden van de database, deels door voortijdige optimalisaties) leidt deze aanpak tot het verschijnen van “Frankensteins”.

Eerst zal ik een voorbeeld geven van een dergelijk verzoek:

-- для каждой ключевой пары находим ассоциированные значения полей
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;

Laten we, om de kwaliteit van een verzoek inhoudelijk te evalueren, een willekeurige dataset maken:

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);

Het blijkt dat het lezen van de gegevens kostte minder dan een kwart van de tijd uitvoering van zoekopdracht:

[kijk naar explain.tensor.ru]

Stuk voor stuk uit elkaar halen

Laten we het verzoek eens nader bekijken en verbaasd zijn:

Waarom staat hier WITH RECURSIVE als er geen recursieve CTE's zijn?
Waarom min/max-waarden in een aparte CTE groeperen als ze dan toch aan het originele monster zijn gekoppeld?
+25% tijd
Waarom aan het einde een onvoorwaardelijke 'SELECT * FROM' gebruiken om de vorige CTE te herhalen?
+14% tijd

In dit geval hadden we het geluk dat voor de verbinding Hash Join werd gekozen, en niet Nested Loop, want dan hadden we niet slechts één CTE Scan-pas ontvangen, maar 10K!

iets over CTE-scanHier moeten we dat onthouden CTE-scan is vergelijkbaar met Seq-scan - dat wil zeggen, geen indexering, maar alleen een volledige zoekopdracht, waarvoor dit nodig is 10K x 0.3 ms = 3000ms voor cycli van cte_max of 1K x 1.5 ms = 1500ms bij het herhalen door cte_bind!
Wat wilde je eigenlijk als resultaat bereiken? Ja, meestal is dit de vraag die ergens in de vijfde minuut naar voren komt bij het analyseren van zoekopdrachten met 'drie verdiepingen'.

We wilden uitvoer uitvoeren voor elk uniek sleutelpaar min/max van groeperen op key_a.
Dus laten we het hiervoor gebruiken raam functies:

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);

[kijk naar explain.tensor.ru]

Omdat het lezen van gegevens in beide opties ongeveer 4-5 ms duurt, winnen we al onze tijd -32% - dit is in zijn puurste vorm belasting verwijderd van de basis-CPU, als een dergelijk verzoek vaak genoeg wordt uitgevoerd.

Over het algemeen moet je de basis niet dwingen om “de ronde te dragen, de vierkante te rollen.”

Bron: www.habr.com

PostgreSQL-antipatronen: CTE x CTE

Stuk voor stuk uit elkaar halen

Voeg een reactie Reactie annuleren