🥇 Antimodelët PostgreSQL: CTE x CTE

Për shkak të natyrës së punës sime, më duhet të përballem me situata kur një zhvillues shkruan një kërkesë dhe mendon "Baza është e zgjuar, mund të trajtojë gjithçka vetë!«

Në disa raste (pjesërisht nga mosnjohja e aftësive të bazës së të dhënave, pjesërisht nga optimizimet e parakohshme), kjo qasje çon në shfaqjen e "Frankensteins".

Së pari, unë do të jap një shembull të një kërkese të tillë:

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

Për të vlerësuar në mënyrë thelbësore cilësinë e një kërkese, le të krijojmë disa grupe të dhënash arbitrare:

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

Rezulton se leximi i të dhënave mori më pak se një të katërtën e kohës ekzekutimi i pyetjes:

[shikoni në shpjegojnë.tensor.ru]

Duke e ndarë atë pjesë-pjesë

Le ta shohim më nga afër kërkesën dhe të habitemi:

Pse është WITH RECURSIVE këtu nëse nuk ka CTE rekursive?
Pse të grupohen vlerat min/maksimumi në një CTE të veçantë nëse ato janë të lidhura gjithsesi me kampionin origjinal?
+25% kohë
Pse të përdorni një 'SELECT * FROM' të pakushtëzuar në fund për të përsëritur CTE-në e mëparshme?
+14% kohë

Në këtë rast, ne ishim shumë me fat që u zgjodh Hash Join për lidhjen dhe jo Nested Loop, sepse atëherë do të kishim marrë jo vetëm një leje CTE Scan, por 10K!

pak rreth CTE ScanKëtu duhet ta kujtojmë atë Skanimi CTE është i ngjashëm me Skanimin Seq - domethënë, pa indeksim, por vetëm një kërkim i plotë, i cili do të kërkonte 10K x 0.3ms = 3000ms për cikle nga cte_max ose 1K x 1.5ms = 1500ms kur lakohet nga cte_bind!
Në fakt, çfarë keni dashur të merrni si rezultat? Po, zakonisht kjo është pyetja që lind diku në minutën e 5-të të analizimit të pyetjeve "tre-katëshe".

Ne donim të dilnim për çdo çift unik çelësash min/maks nga grupi sipas çelësit_a.
Pra, le ta përdorim atë për këtë funksionet e dritares:

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

[shikoni në shpjegojnë.tensor.ru]

Meqenëse leximi i të dhënave në të dy opsionet kërkon të njëjtat rreth 4-5 ms, atëherë fitojmë të gjithë kohën tonë -32% - kjo është në formën e saj më të pastër ngarkesa e hequr nga procesori bazë, nëse një kërkesë e tillë ekzekutohet mjaft shpesh.

Në përgjithësi, nuk duhet ta detyroni bazën të "mbajë një të rrumbullakët, të rrotullojë atë katrore".

Burimi: www.habr.com

Antimodelët PostgreSQL: CTE x CTE

Duke e ndarë atë pjesë-pjesë

Shto një koment Cancel përgjigje