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. ولې دلته RECURSIVE دی که چیرې تکراري CTEs شتون نلري؟
2. ولې په جلا CTE کې د ګروپ min/max ارزښتونه که دوی بیا په هرصورت اصلي نمونې سره تړلي وي؟
   +25٪ وخت
3. ولې د پخوانۍ CTE تکرار لپاره په پای کې غیر مشروط 'SELECT * FROM' وکاروئ؟
   +14٪ وخت

پدې حالت کې، موږ ډیر خوشحاله یو چې د هش جوین د اتصال لپاره غوره شوی و، نه د نیسټډ لوپ، ځکه چې بیا به موږ نه یوازې د CTE سکین پاس ترلاسه کړی وای، مګر 10K!

د CTE سکین په اړه لږ څهدلته موږ باید دا په یاد ولرو د CTE سکین د Seq سکین سره ورته دی - دا دی، هیڅ شاخص نه، مګر یوازې یو بشپړ لټون، کوم چې اړتیا لري 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);

[ تشریح.tensor.ru ته وګورئ]

څرنګه چې په دواړو اختیارونو کې د معلوماتو لوستل ورته نږدې 4-5ms وخت نیسي، نو زموږ ټول وخت ګټه -32٪ - دا په خپل خالص شکل کې دی بار د بیس CPU څخه لرې شوی، که چیرې دا ډول غوښتنه په کافي اندازه اجرا شي.

په عموم کې، تاسو باید بیس دې ته مجبور نه کړئ چې "د ګرد سره یو ځای کړئ، یو مربع رول کړئ."

سرچینه: www.habr.com