PostgreSQL Antipatterns: CTE x CTE

W pracy często spotykamy się z sytuacjami, kiedy programista pisze zapytanie i myśli: „baza jest mądra, sama sobie poradzi!"«

W niektórych przypadkach (częściowo z powodu braku znajomości możliwości bazy danych, częściowo z powodu przedwczesnych optymalizacji) takie podejście prowadzi do powstawania „frankensteinów”.

Na początek podam przykład takiego zapytania:

-- dla każdej pary kluczowych znajdujemy związane wartości pól
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
)
-- znajdujemy min/max wartości dla każdego pierwszego klucza
, cte_max AS (
  SELECT
    a
  , max(bind_fld1) bind_fld1
  , min(bind_fld2) bind_fld2
  FROM
    cte_bind
  GROUP BY
    a
)
-- łączymy według pierwszego klucza pary kluczowe i min/max-wartości
, 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;

Aby konkretnie ocenić jakość zapytania, stwórzmy pewien losowy zestaw danych:

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

Okazuje się, że samo odczytanie danych zajęło mniej niż jedną czwartą całego czasu wykonania zapytania:

[zobacz na explain.tensor.ru]

Rozbieramy na czynniki pierwsze

Uważnie przyjrzymy się zapytaniu i zaskoczymy się:

1. Po co tu WITH RECURSIVE, skoro nie ma żadnych rekurencyjnych CTE?
2. Po co grupować wartości min/max w osobnym CTE, skoro potem i tak są one powiązane z oryginalnym wyborem?
   +25% czasu
3. Po co na końcu ponownie odczytywać z poprzedniego CTE przez bezwarunkowy ‘SELECT * FROM’?
   +14% czasu

W tym przypadku mieliśmy też sporo szczęścia, że dla połączenia wybrano Hash Join, a nie Nested Loop, ponieważ w przeciwnym razie mielibyśmy nie jeden, a 10K przejść CTE Scan!

trochę o CTE ScanNależy pamiętać, że CTE Scan jest odpowiednikiem Seq Scan — to znaczy żadnej indeksacji, a jedynie pełne przeszukiwanie, które wymagałoby 10K x 0.3ms = 3000ms przy cyklach po cte_max lub 1K x 1.5ms = 1500ms przy cyklach po cte_bind!
Właściwie, co chcieliśmy uzyskać jako rezultat? Aha, zwykle właśnie takie pytanie przychodzi na myśl gdzieś w piątej minucie analizy „trójpiętrowych” zapytań.

Chcieliśmy dla każdej unikalnej pary kluczowej wyprowadzić min/max z grupy według key_a.
Użyjmy więc do tego funkcji okiennych:

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

[zobacz na explain.tensor.ru]

Ponieważ odczyt danych w obu wariantach zajmuje podobnie około 4-5 ms, zyskujemy na czasie -32% — to czysty zysk obciążenie zdjęte z CPU bazy, jeśli takie zapytanie jest wykonywane wystarczająco często.

Ogólnie rzecz biorąc, nie należy zmuszać bazy do robienia rzeczy, które nie są dla niej odpowiednie.

Źródło: habr.com