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

თურმე მონაცემების კითხვას დროის მეოთხედზე ნაკლები დრო დასჭირდა შეკითხვის შესრულება:

[ნახეთ description.tensor.ru]

ნაწილ-ნაწილ იშლება

მოდით, უფრო ახლოს მივხედოთ თხოვნას და დავფიქრდეთ:

1. რატომ არის WITH RECURSIVE აქ, თუ არ არის რეკურსიული CTE?
2. რატომ დააჯგუფეთ მინ/მაქს მნიშვნელობები ცალკე CTE-ში, თუ ისინი მაინც მიბმულია ორიგინალურ ნიმუშზე?
   +25% დრო
3. რატომ გამოვიყენოთ უპირობო "SELECT * FROM" ბოლოს წინა CTE-ის გასამეორებლად?
   +14% დრო

ამ შემთხვევაში, ჩვენ ძალიან გაგვიმართლა, რომ Hash Join არჩეული იქნა კავშირისთვის და არა Nested Loop, რადგან მაშინ ჩვენ მივიღებდით არა მხოლოდ ერთ CTE Scan საშვს, არამედ 10K!

ცოტა რამ CTE სკანირების შესახებაი ეს უნდა გვახსოვდეს CTE სკანირება 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);

[ნახეთ description.tensor.ru]

ვინაიდან ორივე ვარიანტში მონაცემების წაკითხვას იგივე სჭირდება დაახლოებით 4-5 ms, მაშინ მთელი ჩვენი დრო იზრდება -32% - ეს არის მისი სუფთა სახით დატვირთვა ამოღებულია ბაზის CPU-დანთუ ასეთი მოთხოვნა საკმარისად ხშირად შესრულდება.

ზოგადად, თქვენ არ უნდა აიძულოთ საფუძველი "ატაროს მრგვალი, გააფართოვოს კვადრატი".

წყარო: www.habr.com