ProHoster > Blog > administracja > Antywzorce PostgreSQL: CTE x CTE

Antywzorce PostgreSQL: CTE x CTE

Ze względu na moją pracę muszę radzić sobie z sytuacjami, gdy programista pisze żądanie i myśli: „Baza jest inteligentna, ze wszystkim poradzi sobie sama!«

W niektórych przypadkach (częściowo z nieznajomości możliwości bazy danych, częściowo z przedwczesnych optymalizacji) takie podejście prowadzi do pojawienia się „Frankensteinów”.

Najpierw podam przykład takiego żądania:

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

Aby merytorycznie ocenić jakość żądania, utwórzmy dowolny zbiór 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);

Okazało się, że odczytanie danych zajęło mniej niż jedną czwartą czasu wykonanie zapytania:

Antywzorce PostgreSQL: CTE x CTE[patrz na explain.tensor.ru]

Rozebrać to kawałek po kawałku

Przyjrzyjmy się bliżej prośbie i bądźmy zaskoczeni:

  1. Dlaczego tutaj jest Z REKURSEM, jeśli nie ma rekurencyjnych CTE?
  2. Po co grupować wartości min/max w oddzielnym CTE, jeśli i tak są one następnie powiązane z oryginalną próbką?
    +25% czasu
  3. Po co używać bezwarunkowego „WYBIERZ * Z” na końcu, aby powtórzyć poprzedni CTE?
    +14% czasu

W tym przypadku mieliśmy dużo szczęścia, że ​​do połączenia wybrano Hash Join, a nie Nested Loop, bo wtedy otrzymalibyśmy nie tylko jedno przejście CTE Scan, ale 10K!

trochę o skanowaniu CTETutaj musimy o tym pamiętać Skanowanie CTE jest podobne do skanowania sekwencyjnego - czyli żadnego indeksowania, a jedynie pełne przeszukanie, co by wymagało 10 tys. x 0.3 ms = 3000ms dla cykli według cte_max lub 1 tys. x 1.5 ms = 1500ms podczas zapętlania przez cte_bind!
Właściwie, co chciałeś w rezultacie uzyskać? Tak, zwykle jest to pytanie, które pojawia się gdzieś w piątej minucie analizy zapytań „trzypiętrowych”.

Chcieliśmy wyprowadzić dane wyjściowe dla każdej unikalnej pary kluczy min/max z grupy według klucza_a.
Więc wykorzystajmy to do tego funkcje okna:

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

Antywzorce PostgreSQL: CTE x CTE
[patrz na explain.tensor.ru]

Ponieważ odczyt danych w obu opcjach zajmuje tyle samo około 4-5ms, to cały nasz zysk czasu -32% - to jest w najczystszej formie obciążenie usunięte z podstawowego procesora, jeśli takie żądanie jest wykonywane wystarczająco często.

Ogólnie rzecz biorąc, nie powinieneś zmuszać bazy do „noszenia okrągłej, rzucania kwadratowej”.

Źródło: www.habr.com

Dodaj komentarz