แแแ แแแแฃแแแ แฉแแแแแ แแแแแแจแแ แแแฃแแ แแแแแชแแแแแแก แซแแแแแก แแแแชแแแ แแแกแแฆแแแแแแก แแแแ แแแแ, แกแแแแ แแ แแแแแฆแแแ แฉแแแแฌแแ แแแแก แกแแญแแ แ แกแแแ แแ แ แแแแแแแแแก.
แงแแแแแแ "แชแฎแแแ แแแแก แแกแแแแกแ" แแแแแแแแแ แฉแแแแแแ 20 แฃแซแแแแแกแ แแ แแแแแแ, แฉแแแแแแแแแ แแแแแแจแ แแแแแแ แกแแแจแ (แแแแแแแแแ, แแแแแ แแแแงแแคแแแแแแจแ). แกแฎแแแแแกแฎแแ แแแแแฏแแ แฃแแ "แแแคแแแแกแแแแก" แกแแแฃแจแแ แกแคแแ แแแแแก แแแแแ แแแแแฎแแแแแ, แแกแแแแกแ แแแแ แกแแแแแแ แฎแจแแ แแ แแ แแก แกแแญแแ แ.
แกแขแแขแแแจแ แแแแแแฎแแแแแ PostgreSQL-แแ แแแแแแ แ แแ แแแแแแแก แแแแแญแ แแก โแแฃแแฃแแ แงแแแแโ แแแ แกแแแก, โแญแแแแแแฃแ แโ แแ แซแแแแแ แ แแฃแแ แแแแแ แแแแแก แแแแฎแแ แชแแแแแแแก. "แแแ แงแฃแแ" SQL-แจแ แแฆแแแฉแแแแแ แแแแแชแแแแแแแแ แแแกแแกแแแแแ แแแ แแแแ, แ แแแแแแช แแแแแแแแแ แ แแแแ แช แแแแแแ แแแแแแแแ แแแแกแแแแก, แแกแแแ แกแฎแแ แแกแแแแก แจแแแแฎแแแแแแจแ แแแแแกแแงแแแแแแแ.
แแแแฆแแ แกแแขแแกแขแ แแแแแชแแแแ แแแแ แแแ
CREATE INDEX ON task(owner_id, task_date, id);
-- ะฐ ััะฐััะน - ัะดะฐะปะธะผ
DROP INDEX task_owner_id_task_date_idx;
แ แแแแ แช แแกแแแก, แแกแ แฌแแ แแ
แแแ แแแ แ แแแจแ, แแแแแ แแแแแแกแแฎแแ แแแแฎแแแแแก แฃแแแ แขแแแแกแ แแแ แกแแ, แจแแแกแ แฃแแแแแแแแก แแแ แแแแแแก แแแฌแแแแแแแก แแแแแชแแแแ
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-แแ แแแแแแแ แฃแแ 960 แฎแแแ, แ แแแแแแช แจแแแแแ แแกแแแ แฃแแแ แแแแแแแแฃแแแงแ... แแ แแแชแแแแ แแแแแแแแ แฌแแแแแแแฎแแ.
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-แจแ, แ แแช แแ แแแ แกแแแแจแ แแแขแแแแแแขแแ แแก แฃแแแ แแแ แกแแแฆแแแ แแ.
แ แแแฃแ แกแแฃแแ แแแฃแแฃแแแขแแ แ
แฌแแแ แแแ แกแแแจแ, แฏแแแจแ, แแแแแฎแฃแแแแ 200 แฎแแแ แกแแญแแ แ 20-แแก แแฃแแแกแแแแก. แฃแแแ แแ แ 960, แแแแ แแ แแแแแ แฃแคแ แ แแแแแแแ - แจแแกแแซแแแแแแแ?
แจแแแแชแแแแ แแแแแแแงแแแแ แแก แชแแแแ, แ แแแแแแช แแแญแแ แแแแ แกแฃแ xnumx แฉแแแแฌแแ แแแ. แแแฃ, แฉแแแ แแแแแแ แแแ แแแแแชแแแแ แแแแแแแแแแก แแฎแแแแ แแแแแ, แกแแแแ แแ แแแแแฆแฌแแแ แกแแญแแ แ แ แแแแแแแแแก.
แแแแแฏแ 1: แแแฌแงแแแแก แกแแ
แชแฎแแแแ, แฉแแแแ "แกแแแแแแ" แกแแ 20 แฉแแแแฌแแ แแกแแแ แฃแแแ แแแแฌแงแแก "แแแ แแแแ" แฉแแแแฌแแ แแแแ แฉแแแแ แแคแแแแแแ_id แแแกแแฆแแแแกแแแแก. แแแแขแแ, แฉแแแ แแแ แแแแแ แแแแฃแแแแ แแกแแแแแก "แแแ แแแแ" แแแแแแฃแแ แแแกแแฆแแแแกแแแแก แแ แฉแแแแ แกแแแจแ, แแแแแแแแ แกแแกแฃแ แแแแ แแแแแแแแแแ แแแแ - (แแแแแแแแแก_แแแ แแฆแ, id).
แแแแแฏแ 2: แแแแแแแ "แจแแแแแแ" แฉแแแแฌแแ แแแ
แแฎแแ แแฃ แแแแฆแแแ แแแ แแแ แฉแแแแฌแแ แก แฉแแแแ แกแแแแแ แแ แแแแแฌแงแแแ "แแแแแฏแ" แฃแคแ แ แฅแแแแแ แแแแแฅแกแ แแคแแแแแแ_id-key-แแก แจแแแแฎแแแ, แแแจแแ แงแแแแ แแแแแแแ แฉแแแแฌแแ แ แแฎแแแแ แจแแแแแแแ แแแฆแแแฃแแ แจแแ แฉแแแแกแแก. แ แ แแฅแแ แฃแแแ, แแฎแแแแ แกแแแแ แแ แแแแแแแแแแ แแแแแงแแแแแฃแแ แแแกแแฆแแแก แแแแ แ แฉแแแแฌแแ แ แกแแแจแ.
แแฃ แแฆแแแฉแแแ, แ แแ แฉแแแ "แแแแแแแแแแ" แแแแ แ แฉแแแแฌแแ แ, แแแจแแ แแแแ แฌแแแแแฎแฃแแ แฉแแแแฌแแ แ แฃแแแ แแแแแแขแแก แกแแแก แแแ แแแแแก แแแชแแแแ (แแแแแ แแคแแแแแแ_id-แแ), แ แแก แจแแแแแแแช แกแแ แฎแแแแฎแแ แแแแแแแแฃแแแ.
แแแฃ, แงแแแแแแแแก แแฎแแแแแแ, แ แแ แกแแแก แแ แแฅแแก แแ แแแ แแแขแ แฉแแแแฌแแ แ แแแแแแฃแแ แแแกแแฆแแแแกแแแแก (แแฃ แฉแแแแฌแแ แแแ แแแกแ แฃแแแ แแ แฉแแแ แแ "แแแแแแแแแ", แแแจแแ แแแ แแแแ แฉแแแแฌแแ แ แฃแแ แแแแ แแแฅแ แแแ แกแแแแแ แแ แแ แแคแแ แ แแแแแแขแแแ. ), แแ แแกแแแ แงแแแแแแแแก แแแแแแแแฃแแแ แแแแแแแชแแแก แแแกแแฆแแแแก แแ แแแแ แแแแแแแแแแ แแแแ (แแแแชแแแแก_แแแ แแฆแ, ID).
แแแแแฏแ 3: แฉแแแแฌแแ แแแแก แแแคแแแขแแ แ แแ แแแคแแ แแแแแ
แฉแแแแ แ แแแฃแ แกแแฃแแ แจแแ แฉแแแแก แ แแแแแแก แแแฌแแแจแ แ แแแแแแแแ แฉแแแแฌแแ แ rv
แแฃแแแแ แแแฃแแแ - แฏแแ แแฎแแแแแแ แแกแแแก, แ แแแแ แแชแแ โแกแแแก แแ-2 แฉแแแแฌแแ แแก แกแแแฆแแ แแก แแแแแแแแแโ, แจแแแแแ แแ แแแก แแแแแชแแแแแ แกแแแแแ แแแ แแแ แแแแแแแ. แแกแ แ แแ, แแแ แแแแ แจแแแแฎแแแแ แฃแแแ แแงแแก แแแคแแแขแ แฃแแ.
แกแแจแแแแแ แกแแแแแแ แจแแแแแฎแแ
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 แแแ แแแแขแแแแ แ แแแแแ แแแแแแงแแแ, แจแแแแแ แแแแแแแแแแฃแแ.
แฌแงแแ แ: www.habr.com