á¡áá«á¡á¬ážáá»á±á¬áºá áœá¬á áá±á¬á·á¡á á¯á¶ááá¯á¡áá¯á¶ážááŒá¯á áááºá ááºáá±áá¬ááá¯ááŸá¬ááœá±áááºáá¬ááẠáá±á«áºáá±á«ááºáá«áááºá ááá¯á¡ááºáá±á¬ á á¯á á¯áá±á«ááºáž ááŸááºáááºážáá»á¬áž áááŸááááºá¡áá.
âáááºááœá±á·ááâ á¡áá»á¬ážáá¯á¶áž á¥ááá¬ááᯠááŒááááºááŒá áºáááºá á¡áááºá¡ááŒá®ážáá¯á¶ážááŒá¿áᬠ20, á á¬áááºážááœááºáž áááºáááºážá á¬áááºážááœáẠ(á¥ááá¬á áá¬áááœá²áá áºáá¯á¡ááœááºáž)á á¡áá¯ááºáááºáááºáá»á¬ážá á¡áá»ááºážáá»á¯ááºá¡áá»ááºážáá»á¯ááºáá»á¬ážáá«ááŸááá±á¬ á á®áá¶ááá·áºááœá²ááŸá¯ "ááá¯ááºááœááºáá»á¬áž" á¡áá»áá¯ážáá»áá¯ážá¡ááœááºá á¡áá¬ážáá°á¡ááŒá±á¬ááºážá¡áá¬áá áºáᯠááá¯á¡ááºáá«áááºá
á€áá±á¬ááºážáá«ážááœááºááá¯áá²á·ááá¯á·áá±á¬ááŒá¿áá¬á¡ááœáẠ"áá¯á¶á¡áá±á¬" ááŒá±ááŸááºážáá»ááºá "á
áááºáá»áá±á¬" ááŸáá·áºá¡ááœááºááŸá¯ááºááœá±ážáá±á¬ algorithm á PostgreSQL ááœááºá¡áá±á¬ááºá¡áááºáá±á¬áºááŸá¯ááá¯ááŒáá·áºááŸá¯áá«áááºá ááœá±á·ááŸááá±á¬áá±áá¬ááŸááœááºáá±á«ááºá¡ááŒá±á¡áá±áá
áºáá¯ááŸáá·áºá¡áá° SQL ááœáẠ"loop"áá±áá°áá»ááœá¶á·ááŒáá¯ážááá¯ážáááºááŸá¯á¡ááœááºáá±á¬ á¡ááŒá¬ážá¡áá¬ážáá°ááá
á¹á
áá»á¬ážááœáẠá¡áá¯á¶ážááŒá¯áááºá¡ááœááºáá±á¬ á¡áá¯á¶ážáááºááá¯ááºáá«áááºá
áá±áá¬á¡á
á¯á¶ááᯠá
ááºážáááºááŒáá·áºáá¡á±á¬ááº
CREATE INDEX ON task(owner_id, task_date, id);
-- а ÑÑаÑÑй - ÑЎалОЌ
DROP INDEX task_owner_id_task_date_idx;
ááŒá¬ážáá²á·á¡ááá¯ááºážáá² áá±ážáá¬ážáááºá
áŠážá
áœá¬á áá»á±á¬áºááŒá±áááºáááºáá°áá»á¬ážá ID áá»á¬ážááᯠáá»á±á¬áºááŒááºáᬠáá±á¬ááºážááá¯ááŸá¯á á¡ááá¯ážááŸááºážáá¯á¶ážáá¬ážááŸááºážááᯠáá¯á¶ááŒááºážáá¯ááºááŒáá«á
áá¯á·
SELECT
*
FROM
task
WHERE
owner_id = ANY('{1,2,4,8,16,32,64,128,256,512}'::integer[])
ORDER BY
task_date, id
LIMIT 20;
á¡áááºážáááºáááºážáááºážá áᬠ- áá»áœááºá¯ááºááá¯á·ááẠááŸááºáááºáž 20 ááá¯áᬠááŸá¬áá°áá¬ážáá±á¬áºáááºáž Index Scan ááᯠáá»áœááºá¯ááºááá¯á·áᶠááŒááºáá±ážáá²á·áá«áááºá ááá¯ááºáž áááá á®á á¥áºáá¬ážááááº... áá»áŸá±á¬á·áááºááŒáá·áºáá¡á±á¬ááºá
unnest + ARRAY
ááá¯á¡ááºááẠáá°áá®áá±ážááá·áº ááááá¯á¶ážá ááºážá á¬ážáá»ááºáá«á 20 áᬠá á®áá¬ážáááºá ááŸááºáááºážááœá±ááᯠáááºáá¯á¶áá«áá²á áá áºáá¯á á®á¡ááœáẠáá°áá®áá±á¬á¡á á®á¡á á¥áºááœáẠ20 áááºáááá¯á á±áá áá±á¬á·á áá±á¬ááºážáááºá ááá·áºáá»á±á¬áºáá±á¬á¡ááœáŸááºáž (owner_idá task_dateá id) áá»áœááºá¯ááºááá¯á·ááœááºááŸááááºá
áá¯ááºáá°ááŒááºážááŸáá·áº "áá±á¬áºáá¶áá»á¬ážáá²ááá¯á·ááŒáá·áºááœá¬ážááŒááºáž" á¡ááœááºáá°áá®áá±á¬ááá¹ááá¬ážááá¯áá¯á¶ážááŒáá«á
áá¯á·á áá±á«ááºážá
ááºááá¬ážááŸááºáááºážáááŸáááá²á·ááá¯á· ARRAY()
:
WITH T AS (
SELECT
unnest(ARRAY(
SELECT
t
FROM
task t
WHERE
owner_id = unnest
ORDER BY
task_date, id
LIMIT 20 -- ПгÑаМОÑОваеЌ ÑÑÑ...
)) r
FROM
unnest('{1,2,4,8,16,32,64,128,256,512}'::integer[])
)
SELECT
(r).*
FROM
T
ORDER BY
(r).task_date, (r).id
LIMIT 20; -- ... О ÑÑÑ - ÑПже
á¡áá¯ážá ááá¯áá±á¬ááºážáá±ááŒá®á 40% ááá¯ááŒááºááŒá®áž áá±áᬠ4.5 á ááá¯áááºážáá«áááºá á¡á²áá«ááᯠáááºááááºá
CTE ááŸáá áºááá·áº ááá¬ážááŸááºáááºážáá»á¬ážááᯠáá¯ááºáá¯á¶ážáá±á«áºáá¬ááŒááºážáá¡á²áá®á¡áá»ááºááᯠá¡á¬áá¯á¶á áá¯ááºáá«áá á± á¡áá»áá¯á·ááá á¹á áá»á¬ážááœáẠCTE ááœáẠáááºážááᯠ"áá¯ááºááá¯ážááŒááºáž" áááŒá¯áá¯ááºáá² ááŸá¬ááœá±ááŒá®ážáá±á¬áẠááŸááºáááºážááááºáááºáá»á¬ážááŸáá·áº áá»ááºáá»ááºážá¡áá¯ááºáá¯ááºááẠááŒáá¯ážáááºážááŸá¯ááẠááŒá áºáá±á«áºáá¬ááá¯ááºááẠ"áá»á¬ážááŒá¬ážáá±á¬" InitPlan á€áá°áá®áá±á¬áááºáááºáá»á¬ážá á¡áá±á¡ááœááºááŸáá·áº á¡áá»áá¯ážáá»áááº-
SELECT
((
SELECT
t
FROM
task t
WHERE
owner_id = 1
ORDER BY
task_date, id
LIMIT 1
).*);
Result (cost=4.77..4.78 rows=1 width=16) (actual time=0.063..0.063 rows=1 loops=1)
Buffers: shared hit=16
InitPlan 1 (returns $0)
-> Limit (cost=0.42..1.19 rows=1 width=48) (actual time=0.031..0.032 rows=1 loops=1)
Buffers: shared hit=4
-> Index Scan using task_owner_id_task_date_id_idx on task t (cost=0.42..387.57 rows=500 width=48) (actual time=0.030..0.030 rows=1 loops=1)
Index Cond: (owner_id = 1)
Buffers: shared hit=4
InitPlan 2 (returns $1)
-> Limit (cost=0.42..1.19 rows=1 width=48) (actual time=0.008..0.009 rows=1 loops=1)
Buffers: shared hit=4
-> Index Scan using task_owner_id_task_date_id_idx on task t_1 (cost=0.42..387.57 rows=500 width=48) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: (owner_id = 1)
Buffers: shared hit=4
InitPlan 3 (returns $2)
-> Limit (cost=0.42..1.19 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=1)
Buffers: shared hit=4
-> Index Scan using task_owner_id_task_date_id_idx on task t_2 (cost=0.42..387.57 rows=500 width=48) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: (owner_id = 1)
Buffers: shared hit=4"
InitPlan 4 (returns $3)
-> Limit (cost=0.42..1.19 rows=1 width=48) (actual time=0.009..0.009 rows=1 loops=1)
Buffers: shared hit=4
-> Index Scan using task_owner_id_task_date_id_idx on task t_3 (cost=0.42..387.57 rows=500 width=48) (actual time=0.009..0.009 rows=1 loops=1)
Index Cond: (owner_id = 1)
Buffers: shared hit=4
áá°áá®áá±á¬ááŸááºáááºážááᯠ4 ááŒáááºááŒáá·áºááŸá¯áá²á·áááº... PostgreSQL 11 áááá¯ááºáá®á¡ááá á€á¡ááŒá¯á¡áá°ááẠáá¯á¶ááŸááºááŒá áºáá±á«áºááŒá®áž ááŒá±ááŸááºážáá»ááºááŸá¬ áááºážááᯠCTE ááœáẠáá¯ááºááá¯ážáááºááŒá áºááŒá®ážá á€áá¬ážááŸááºážáá»á¬ážááŸá optimizer á¡ááœáẠáá¯á¶ážáááá·áºáááºáá»ááºááŒá áºáááºá
Recursive accumulator
ááááºáá¬ážááŸááºážááœááºá áá»áœááºá¯ááºááá¯á·á¡á¬ážáá¯á¶ážáááºáááºá ááá¯ááºáž ááá ááá¯á¡ááºáá±á¬ 20 ááᯠáá±á¬ááºá 960 ááá¯ááºáá±á¬áºáááºáž áááºážáá«ážáááº- ááŒá áºááá¯ááºáá«ááá¬ážá
ááá¯á¡ááºáá²á· ááá¯áá¯áááœá±ááᯠá¡áá¯á¶ážáá»ááŒáá·áºáá¡á±á¬áẠá¡á¬ážáá¯á¶áž 20 ááŸááºáááºážáá»á¬áž ááá¯ááá¯áááºááŸá¬á áá»áœááºá¯ááºááá¯á·ááẠáá»áœááºá¯ááºááá¯á·ááá¯á¡ááºááá·áºááá¬áááá¯á·áá±á¬ááºááŸááááºá¡áá áá±áá¬áááºááŸá¯ááŒááºážááá¯áᬠáááºáá±á¬ááºážáá±á¬áºááŒáá«áááºá
á¡ááá·áº 1: á áááºá á¬áááºáž
áá±áá»á¬áááºááŸá¬á ááŸááºáááºáž 20 á áá»áœááºá¯ááºááá¯á·á "áá áºááŸááº" á á¬áááºážááẠáá»áœááºá¯ááºááá¯á·á owner_id áá±á¬á·áá»á¬ážáá²á០áá áºáá¯á¡ááœáẠ"ááá" ááŸááºáááºážáá»á¬ážááŒáá·áº á áááºááá·áºáá«áááºá ááá¯á·ááŒá±á¬áá·áº ááááŠážá áœá¬ áá»áœááºá¯ááºááá¯á·ááẠááá¯ááá¯á·áá±á¬ á¡áá¬ááᯠááŸá¬ááááá·áºáááºá áá±á¬á·áá áºáá¯á á®á¡ááœáẠâá¡áááºáá¯á¶ážâ áááºážááᯠá á¬áááºážáá²ááá¯á· ááá·áºáá«á áá»áœááºá¯ááºááá¯á· ááá¯áá»ááºáá±á¬ á¡á á®á¡á á¥áºá¡ááá¯ááºáž á á®áá« - (task_date, id)á
á¡ááá·áº 2- "áá±á¬ááºáááº" ááá·áºááœááºážááŸá¯áá»á¬ážááᯠááŸá¬áá«á
ááá¯áá»áœááºá¯ááºááá¯á·áá á¬áááºážááŸááááá¯á¶ážáááºáá±á¬ááºááŸá¯ááá¯ááá°ááŒá®ážá áááºáá«á á¡ááœáŸááºážá¡ááá¯ááºáž "ááŒá±ááŸááºáž" ááŸááºážáá«á owner_id áá®ážááᯠááááºážááááºážáá¬ážáá«á ááá¯á·áá±á¬áẠááœá±á·ááŸááá¬ážáá±á¬ ááŸááºáááºážáá»á¬ážá¡á¬ážáá¯á¶ážááẠááááºááœá±ážáá»ááºááŸá¯ááœáẠáá±á¬ááºáá áºáá¯á¡ááá¡áá»ááŒá áºáááºá áá¯ááºáá«áááºá áááºáá«ážáá±á¬á·ááᯠááŒááºááœá¬ážáááºá¡áá á á¬áááºážááœáẠáá¯ááá áááºááœáá·áºá
á¡áááºá áá»áœááºá¯ááºááá¯á·ááẠáá¯áááá á¶áá»áááºááᯠáá»á±á¬áºááŒááºááá¯ááºáá²á·áá»áŸááºá áá±á¬ááºáá¯á¶ážá á¬áááºááŒááºážááᯠááááá áºáá¯á¡á á¬áž á á¬áááºážáá²ááá¯á· ááá·áºááá·áºáááºá (áá°áá®áá±á¬ owner_id ááŒáá·áº) ááá¯á·áá±á¬áẠá á¬áááºážááᯠáááºáá¶á á®á á áºáá«á
ááá¯ááá¯áááºááŸá¬á á
á¬áááºážááœáẠáá±á¬á·áá
áºáá¯á
á®á¡ááœáẠáááºáá±á«ááºáá
áºáá¯áááºááá¯á ááá«ááŸáááŒá±á¬ááºáž áá»áœááºá¯ááºááá¯á·á¡ááŒá²áááŸáááẠ(ááá·áºááœááºážááŸá¯áá»á¬ážáá¯ááºááœá¬ážáᬠáá»áœááºá¯ááºááá¯á·ááẠâááá°ážáá«áá á
á¬áááºážááŸááááááºáá±á¬ááºááŸá¯ááẠááá¯ážááŸááºážá
áœá¬ áá»á±á¬ááºááœááºááœá¬ážáááºááŒá
áºááŒá®áž áááºááá·áºá¡áá¬á០áááºááá·áºáááºááá¯ááºáá«á ) ááŸáá·áº áá°ááá¯á· á¡ááŒá²áááºážá
á®áá¬ážáááºá á¡ááá®áá±ážááŸááºážáá±á¬á· (task_dateá id) á áááºá
ááºá¡ááá¯ááºá
á¡ááá·áº 3- ááŸááºáááºážáá»á¬ážááᯠá á áºáá¯ááºááŒá®áž áá»á²á·áá«á
áá»áœááºá¯ááºááá¯á·á recursive ááœá±ážáá»ááºááŸá¯áá¡áááºážá¡áá»áá¯á·ááœááºá á¡áá»áá¯á·áá±á¬ááŸááºáááºážáá»á¬áž rv
ááœá¬ážáá±ááẠ- ááááŠážá
áœá¬ áá»áœááºá¯ááºááá¯á·ááẠ"á
á¬áááºážá 2nd entry á áááºá
ááºááᯠááŒááºááŒááºáž" áá²á·ááá¯á· ááœá±á·áááŒá®áž áááºážááᯠá
á¬áááºážá០1st á¡ááŒá
Ạá¡á
á¬ážááá¯ážáá«á áá«ááŒá±á¬áá·áº ááá ááŒá
áºáá»ááºááŸá¯ááᯠá
á
áºáá¯ááºááááºá
ááŒá±á¬ááºá áá¬áá±á¬ááºážáá±á¬ áá±á¬ááºáá¯á¶ážáá±ážááœááºáž
WITH RECURSIVE T AS (
-- #1 : заМПÑОЌ в ÑпОÑПк "пеÑвÑе" запОÑО пП ÐºÐ°Ð¶ÐŽÐŸÐŒÑ ÐžÐ· клÑÑей МабПÑа
WITH wrap AS ( -- "ЌаÑеÑОалОзÑеЌ" record'Ñ, ÑÑÐŸÐ±Ñ ÐŸÐ±ÑаÑеМОе к пПлÑÐŒ Ме вÑзÑвалП ÑÐŒÐœÐŸÐ¶ÐµÐœÐžÑ InitPlan/SubPlan
WITH T AS (
SELECT
(
SELECT
r
FROM
task r
WHERE
owner_id = unnest
ORDER BY
task_date, id
LIMIT 1
) r
FROM
unnest('{1,2,4,8,16,32,64,128,256,512}'::integer[])
)
SELECT
array_agg(r ORDER BY (r).task_date, (r).id) list -- ÑПÑÑОÑÑеЌ ÑпОÑПк в ÐœÑжМПЌ пПÑÑЎке
FROM
T
)
SELECT
list
, list[1] rv
, FALSE not_cross
, 0 size
FROM
wrap
UNION ALL
-- #2 : вÑÑОÑÑваеЌ запОÑО 1-гП пП пПÑÑÐŽÐºÑ ÐºÐ»ÑÑа, пПка Ме пеÑеÑагМеЌ ÑеÑез запОÑÑ 2-гП
SELECT
CASE
-- еÑлО МОÑегП Ме МайЎеМП ÐŽÐ»Ñ ÐºÐ»ÑÑа 1-й запОÑО
WHEN X._r IS NOT DISTINCT FROM NULL THEN
T.list[2:] -- ÑбОÑаеЌ ее Оз ÑпОÑка
-- еÑлО ÐŒÑ ÐРпеÑеÑеклО пÑОклаЎМПй клÑÑ 2-й запОÑО
WHEN X.not_cross THEN
T.list -- пÑПÑÑП пÑПÑÑгОваеЌ ÑÐŸÑ Ð¶Ðµ ÑпОÑПк без ЌПЎОÑОкаÑОй
-- еÑлО в ÑпОÑке Ñже ÐœÐµÑ 2-й запОÑО
WHEN T.list[2] IS NULL THEN
-- пÑПÑÑП вПзвÑаÑаеЌ пÑÑÑПй ÑпОÑПк
'{}'
-- пеÑеÑПÑÑОÑПвÑваеЌ ÑлПваÑÑ, ÑбОÑÐ°Ñ 1-Ñ Ð·Ð°Ð¿ÐžÑÑ Ðž ЎПбавлÑÑ Ð¿ÐŸÑлеЎМÑÑ ÐžÐ· МайЎеММÑÑ
ELSE (
SELECT
coalesce(T.list[2] || array_agg(r ORDER BY (r).task_date, (r).id), '{}')
FROM
unnest(T.list[3:] || X._r) r
)
END
, X._r
, X.not_cross
, T.size + X.not_cross::integer
FROM
T
, LATERAL(
WITH wrap AS ( -- "ЌаÑеÑОалОзÑеЌ" record
SELECT
CASE
-- еÑлО вÑе-ÑакО "пеÑеÑагМÑлО" ÑеÑез 2-Ñ Ð·Ð°Ð¿ÐžÑÑ
WHEN NOT T.not_cross
-- ÑП ÐœÑÐ¶ÐœÐ°Ñ Ð·Ð°Ð¿ÐžÑÑ - пеÑÐ²Ð°Ñ ÐžÐ· ÑппОÑка
THEN T.list[1]
ELSE ( -- еÑлО Ме пеÑеÑеклО, ÑП клÑÑ ÐŸÑÑалÑÑ ÐºÐ°Ðº в пÑеЎÑÐŽÑÑей запОÑО - ПÑÑалкОваеЌÑÑ ÐŸÑ ÐœÐµÐµ
SELECT
_r
FROM
task _r
WHERE
owner_id = (rv).owner_id AND
(task_date, id) > ((rv).task_date, (rv).id)
ORDER BY
task_date, id
LIMIT 1
)
END _r
)
SELECT
_r
, CASE
-- еÑлО 2-й запОÑО Ñже ÐœÐµÑ Ð² ÑпОÑке, МП ÐŒÑ Ñ
ПÑÑ ÑÑП-ÑП МаÑлО
WHEN list[2] IS NULL AND _r IS DISTINCT FROM NULL THEN
TRUE
ELSE -- МОÑегП Ме МаÑлО ОлО "пеÑеÑагМÑлО"
coalesce(((_r).task_date, (_r).id) < ((list[2]).task_date, (list[2]).id), FALSE)
END not_cross
FROM
wrap
) X
WHERE
T.size < 20 AND -- ПгÑаМОÑОваеЌ ÑÑÑ ÐºÐŸÐ»ÐžÑеÑÑвП
T.list IS DISTINCT FROM '{}' -- ОлО пПка ÑпОÑПк Ме кПМÑОлÑÑ
)
-- #3 : "ÑазвПÑаÑОваеЌ" запОÑО - пПÑÑЎПк гаÑаМÑОÑПваМ пП пПÑÑÑПеМОÑ
SELECT
(rv).*
FROM
T
WHERE
not_cross; -- беÑеЌ ÑПлÑкП "МепеÑеÑекаÑÑОе" запОÑО
áá«ááŒá±á¬áá·áº áá»áœááºáá±á¬áºááᯠáááºáááºáá»áááºá 50% á¡ááœáẠáá±áá¬áááºááŒááºážá 20% ááᯠáá¯ááºááœááºááŸá¯ááŒá¯áááºá. ááá¯ááá¯áááºááŸá¬á á á¬áááºááŒááºážááẠá¡áá»áááºááŒá¬ááŒáá·áºááá¯ááºáááºáᯠáá¯á¶ááŒááºááẠá¡ááŒá±á¬ááºážááŒáá»ááºááŸááá«á (á¥ááá¬á áá±áá¬ááẠáááŒá¬áá áááºááŸáºááœáẠáááŸááá«á áááºážá¡ááœáẠáá áºááºááá¯á· ááœá¬ážááááº)á ááá¯á·áá±á¬áẠá€áááºážááŒáá·áº áááºááẠá á¬áááºááŒááºážá¡áá±á«áº ááŸá®ááá¯ááŸá¯ áááºážáá«ážááá¯ááºáááºá .
áááºááá¯á·áááºááá¯á
á±á ááœááºáá»ááºáá»áááºááẠ"áá¯á¶á¡" áááááœá±ážáá»ááºááŸá¯ááẠááá¯áá±á¬ááºážáá¬áááºá áá«áá±ááá·áº áá®ááœá±ážáá»ááºá
áᬠ3 áá¯áá²á áááºáá¬ááᯠáá¯á¶ážááá² ááá¯áá¬ááá±á¬á· ááá·áºá¡ááœááºáá«áá²á
source: www.habr.com