پرو هوسٽر > بلاگ > انتظاميه > 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[explanation.tensor.ru تي ڏسو]

ان کي ٽڪرا ٽڪرا ٽڪرا ٽڪرا ڪرڻ

اچو ته درخواست تي هڪ ويجهي نظر رکون ۽ حيران ٿي وڃو:

  1. WITH RECURSIVE هتي ڇو آهي جيڪڏهن هتي ريٽرسيو CTEs نه آهن؟
  2. ڇو گروپ منٽ/وڌ کان وڌ قدر الڳ CTE ۾ جيڪڏهن اهي پوءِ به اصل نموني سان جڙيل آهن؟
    +25٪ وقت
  3. پوئين CTE کي ورجائڻ لاءِ آخر ۾ غير مشروط 'SELECT * FROM' ڇو استعمال ڪيو وڃي؟
    +14٪ وقت

ان صورت ۾، اسان ڏاڍا خوش قسمت هئاسين ته Hash Join ڪنيڪشن لاءِ چونڊيو ويو، ۽ Nested Loop نه، ڇاڪاڻ ته پوءِ اسان کي نه رڳو هڪ CTE اسڪين پاس ملي ها، پر 10K!

CTE اسڪين بابت ٿورڙوهتي اسان کي اهو ياد رکڻ گهرجي CTE اسڪين Seq Scan سان ملندڙ جلندڙ آهي - اهو آهي، ڪا به انڊيڪس نه، پر صرف هڪ مڪمل ڳولا، جنهن جي ضرورت هوندي 10K x 0.3ms = 3000MS cte_max پاران سائيڪلن لاءِ يا 1K x 1.5ms = 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
[explanation.tensor.ru تي ڏسو]

جيئن ته ٻنهي اختيارن ۾ ڊيٽا پڙهڻ لڳ ڀڳ 4-5ms لڳن ٿا، پوء اسان جو سڄو وقت فائدو -32٪ - اهو ان جي خالص روپ ۾ آهي بيس سي پي يو تان لوڊ هٽايو ويو، جيڪڏهن اهڙي درخواست تي عمل ڪيو وڃي ته اڪثر ڪافي.

عام طور تي، توهان کي بنيادي طور تي مجبور نه ڪرڻ گهرجي ته "گول هڪ کڻو، هڪ چورس رول ڪريو."

جو ذريعو: www.habr.com

تبصرو شامل ڪريو