PostgreSQL հակապատկերներ՝ CTE x CTE

Իմ աշխատանքի գծի պատճառով ես պետք է զբաղվեմ իրավիճակներով, երբ ծրագրավորողը հարցում է գրում և մտածում.Հիմքը խելացի է, այն կարող է ինքնուրույն կարգավորել ամեն ինչ:«

Որոշ դեպքերում (մասամբ տվյալների բազայի հնարավորությունների անտեղյակությունից, մասամբ՝ վաղաժամ օպտիմալացումներից) այս մոտեցումը հանգեցնում է «Ֆրանկենշտեյնների» ի հայտ գալուն։

Նախ, ես նման խնդրանքի օրինակ բերեմ.

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

Հարցման որակը էականորեն գնահատելու համար եկեք ստեղծենք որոշ կամայական տվյալների հավաքածու.

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

Պարզվում է, որ տվյալների ընթերցումը տևել է ժամանակի մեկ քառորդից պակաս հարցման կատարում.

[նայեք բացատրություն.tensor.ru-ին]

Մաս առ մաս բաժանելով

Եկեք մանրամասն նայենք խնդրանքին և շփոթենք.

1. Ինչո՞ւ է WITH RECURSIVE-ն այստեղ, եթե ռեկուրսիվ CTE-ներ չկան:
2. Ինչու՞ խմբավորել min/max արժեքները առանձին CTE-ում, եթե դրանք, այնուամենայնիվ, կապված են բնօրինակ նմուշի հետ:
   +25% ժամանակ
3. Ինչու՞ վերջում օգտագործել անվերապահ «SELECT * FROM»՝ նախորդ CTE-ը կրկնելու համար:
   +14% ժամանակ

Այս դեպքում, մենք շատ բախտավոր էինք, որ միացման համար ընտրվեց Hash Join-ը, և ոչ թե Nested Loop-ը, քանի որ այդ դեպքում մենք կստանայինք ոչ միայն մեկ CTE Scan անցագիր, այլ 10K:

մի քիչ CTE սկանավորման մասինԱյստեղ մենք պետք է հիշենք դա CTE Scan-ը նման է Seq Scan-ին - այսինքն, ոչ մի ինդեքսավորում, այլ միայն ամբողջական որոնում, որը կպահանջի 10K x 0.3ms = 3000ms cte_max-ով ցիկլերի համար կամ 1K x 1.5ms = 1500ms cte_bind-ով պտտվելիս!
Իրականում ի՞նչ էիք ուզում ստանալ արդյունքում։ Այո, սովորաբար սա այն հարցն է, որը ծագում է ինչ-որ տեղ «եռահարկ» հարցումները վերլուծելու 5-րդ րոպեին:

Մենք ուզում էինք արդյունք տալ յուրաքանչյուր եզակի բանալիների զույգի համար min/max խմբից ըստ key_a-ի.
Այսպիսով, եկեք այն օգտագործենք դրա համար պատուհանի գործառույթները:

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

[նայեք բացատրություն.tensor.ru-ին]

Քանի որ երկու տարբերակներում տվյալների ընթերցումը տևում է նույնը մոտավորապես 4-5 մս, ապա մեր ամբողջ ժամանակը շահում է -32% - սա իր մաքուր ձևով է բեռը հեռացվել է բազային պրոցեսորից, եթե նման հարցումը կատարվում է բավական հաճախ։

Ընդհանրապես, չպետք է ստիպել հիմքին «կլորը տանել, քառակուսին գլորել»։

Source: www.habr.com