ProHoster > Блог > басқарма > PostgreSQL антипаттерндері: CTE x CTE

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

Солай екен деректерді оқу уақыттың төрттен бірінен аз уақытты алды сұраудың орындалуы:

PostgreSQL антипаттерндері: CTE x CTE[express.tensor.ru сайтынан қарау]

Оны бөлшектеп алу

Өтінішті мұқият қарастырып, таң қалдырайық:

  1. Неліктен WITH RECURSIVE мұнда рекурсивті CTE болмаса?
  2. Неліктен мин/макс мәндерін бөлек CTE-де топтастыру керек, егер олар бастапқы үлгіге бәрібір байланысты болса?
    +25% уақыт
  3. Неліктен алдыңғы CTE қайталау үшін соңында шартсыз «SELECT * FROM» пайдалану керек?
    +14% уақыт

Бұл жағдайда қосылым үшін кірістірілген цикл емес, Hash Join таңдалғаны бізге өте бақытты болды, өйткені ол кезде біз бір ғана CTE Scan рұқсатын ғана емес, 10K алатын едік!

CTE Scan туралы аздапБұл жерде біз мұны есте сақтауымыз керек CTE Scan Seq Scan-ға ұқсас - яғни индекстеу жоқ, тек толық іздеу қажет, ол қажет 10K x 0.3 мс = 3000ms cte_max бойынша циклдар үшін немесе 1K x 1.5 мс = 1500ms cte_bind арқылы цикл жасағанда!
Шын мәнінде, нәтижесінде сіз не алғыңыз келді? Иә, әдетте бұл сұрақ «үш қабатты» сұрауларды талдаудың 5-ші минутында пайда болады.

Біз әрбір бірегей кілт жұбын шығарғымыз келді 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);

PostgreSQL антипаттерндері: CTE x CTE
[express.tensor.ru сайтынан қарау]

Екі нұсқадағы деректерді оқу шамамен 4-5 мс уақытты алатындықтан, біздің барлық уақытымыз ұтады -32% - бұл ең таза түрінде жүктеме негізгі процессордан жойылды, егер мұндай сұрау жеткілікті жиі орындалса.

Тұтастай алғанда, негізді «дөңгелек алып жүруге, шаршыны айналдыруға» мәжбүрлемеу керек.

Ақпарат көзі: www.habr.com

пікір қалдыру