ΠΠ΅ΡΠΈΠΎΠ΄ΠΈΡΠ½ΠΎ Π²ΡΠ·Π½ΠΈΠΊΠ²Π° Π·Π°Π΄Π°ΡΠ°ΡΠ° Π·Π° ΡΡΡΡΠ΅Π½Π΅ Π½Π° ΡΠ²ΡΡΠ·Π°Π½ΠΈ Π΄Π°Π½Π½ΠΈ ΡΡΠ΅Π· Π½Π°Π±ΠΎΡ ΠΎΡ ΠΊΠ»ΡΡΠΎΠ²Π΅, Π΄ΠΎΠΊΠ°ΡΠΎ ΠΏΠΎΠ»ΡΡΠΈΠΌ Π½Π΅ΠΎΠ±Ρ ΠΎΠ΄ΠΈΠΌΠΈΡ ΠΎΠ±Ρ Π±ΡΠΎΠΉ Π·Π°ΠΏΠΈΡΠΈ.
ΠΠ°ΠΉ-βΡΠ΅Π°Π»ΠΈΡΡΠΈΡΠ½ΠΈΡΡβ ΠΏΡΠΈΠΌΠ΅Ρ Π΅ Π΄Π° ΡΠ΅ ΠΏΠΎΠΊΠ°ΠΆΠ΅ 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, Π½ΠΎ ΠΎΡΠ΅ ΠΏΠΎ-ΠΌΠ°Π»ΠΊΠΎ - Π²ΡΠ·ΠΌΠΎΠΆΠ½ΠΎ Π»ΠΈ Π΅?
ΠΠ΅ΠΊΠ° ΡΠ΅ ΠΎΠΏΠΈΡΠ°ΠΌΠ΅ Π΄Π° ΠΈΠ·ΠΏΠΎΠ»Π·Π²Π°ΠΌΠ΅ Π·Π½Π°Π½ΠΈΡΡΠ°, ΠΎΡ ΠΊΠΎΠΈΡΠΎ ΡΠ΅ Π½ΡΠΆΠ΄Π°Π΅ΠΌ ΠΎΠ±ΡΠΎ 20 Π·Π°ΠΏΠΈΡΠΈ. Π’ΠΎΠ΅ΡΡ, Π½ΠΈΠ΅ ΡΠ΅ ΠΏΠΎΠ²ΡΠ°ΡΡΠΌΠ΅ ΠΈΠ·Π²Π°ΠΆΠ΄Π°Π½Π΅ΡΠΎ Π½Π° Π΄Π°Π½Π½ΠΈ ΡΠ°ΠΌΠΎ Π΄ΠΎΠΊΠ°ΡΠΎ Π½Π΅ Π±ΡΠ΄Π΅ Π΄ΠΎΡΡΠΈΠ³Π½Π°ΡΠ° Π½Π΅ΠΎΠ±Ρ ΠΎΠ΄ΠΈΠΌΠ°ΡΠ° Π½ΠΈ ΡΡΠΌΠ°.
Π‘ΡΡΠΏΠΊΠ° 1: Π‘ΡΠ°ΡΡΠΎΠ² ΡΠΏΠΈΡΡΠΊ
ΠΡΠ΅Π²ΠΈΠ΄Π½ΠΎ Π½Π°ΡΠΈΡΡ βΡΠ΅Π»Π΅Π²ΠΈβ ΡΠΏΠΈΡΡΠΊ ΠΎΡ 20 Π·Π°ΠΏΠΈΡΠ° ΡΡΡΠ±Π²Π° Π΄Π° Π·Π°ΠΏΠΎΡΠ½Π΅ Ρ βΠΏΡΡΠ²ΠΈΡΠ΅β Π·Π°ΠΏΠΈΡΠΈ Π·Π° Π΅Π΄ΠΈΠ½ ΠΎΡ Π½Π°ΡΠΈΡΠ΅ ΠΊΠ»ΡΡΠΎΠ²Π΅ owner_id. ΠΠ°ΡΠΎΠ²Π° ΠΏΡΡΠ²ΠΎ Π½Π°ΠΌΠΈΡΠ°ΠΌΠ΅ ΡΠ°ΠΊΠΈΠ²Π° "ΠΏΡΡΠ²ΠΈ" Π·Π° Π²ΡΠ΅ΠΊΠΈ ΠΎΡ ΠΊΠ»ΡΡΠΎΠ²Π΅ΡΠ΅ ΠΈ Π³ΠΎ ΠΏΠΎΡΡΠ°Π²ΡΠΌΠ΅ Π² ΡΠΏΠΈΡΡΠΊΠ°, ΠΊΠ°ΡΠΎ Π³ΠΎ ΡΠΎΡΡΠΈΡΠ°ΠΌΠ΅ Π² ΡΠ΅Π΄Π°, ΠΊΠΎΠΉΡΠΎ ΠΆΠ΅Π»Π°Π΅ΠΌ - (task_date, id).
Π‘ΡΡΠΏΠΊΠ° 2: Π½Π°ΠΌΠ΅ΡΠ΅ΡΠ΅ βΡΠ»Π΅Π΄Π²Π°ΡΠΈΡΠ΅β Π·Π°ΠΏΠΈΡΠΈ
Π‘Π΅Π³Π°, Π°ΠΊΠΎ Π²Π·Π΅ΠΌΠ΅ΠΌ ΠΏΡΡΠ²ΠΈΡ Π·Π°ΠΏΠΈΡ ΠΎΡ Π½Π°ΡΠΈΡ ΡΠΏΠΈΡΡΠΊ ΠΈ Π·Π°ΠΏΠΎΡΠ½Π΅ΠΌ "ΡΡΡΠΏΠΊΠ°" ΠΏΠΎ-Π½Π°Π΄ΠΎΠ»Ρ Π² ΠΈΠ½Π΄Π΅ΠΊΡΠ° ΡΡΡ Π·Π°ΠΏΠ°Π·Π²Π°Π½Π΅ Π½Π° owner_id-key, ΡΠΎΠ³Π°Π²Π° Π²ΡΠΈΡΠΊΠΈ Π½Π°ΠΌΠ΅ΡΠ΅Π½ΠΈ Π·Π°ΠΏΠΈΡΠΈ ΡΠ° ΡΠ°ΠΌΠΎ ΡΠ»Π΅Π΄Π²Π°ΡΠΈΡΠ΅ Π² ΡΠ΅Π·ΡΠ»ΡΠ°Π½ΡΠ½Π°ΡΠ° ΡΠ΅Π»Π΅ΠΊΡΠΈΡ. Π Π°Π·Π±ΠΈΡΠ° ΡΠ΅, ΡΠ°ΠΌΠΎ Π΄ΠΎΠΊΠ°ΡΠΎ ΠΏΡΠ΅ΡΠ΅ΡΠ΅ΠΌ ΠΏΡΠΈΠ»ΠΎΠΆΠ΅Π½ΠΈΡ ΠΊΠ»ΡΡ Π²ΡΠΎΡΠΈ Π·Π°ΠΏΠΈΡ Π² ΡΠΏΠΈΡΡΠΊΠ°.
ΠΠΊΠΎ ΡΠ΅ ΠΎΠΊΠ°ΠΆΠ΅, ΡΠ΅ ΡΠΌΠ΅ βΠΏΡΠ΅ΡΠ΅ΠΊΠ»ΠΈβ Π²ΡΠΎΡΠΈΡ Π·Π°ΠΏΠΈΡ, ΡΠΎΠ³Π°Π²Π° ΠΏΠΎΡΠ»Π΅Π΄Π½ΠΈΡΡ ΠΏΡΠΎΡΠ΅ΡΠ΅Π½ Π·Π°ΠΏΠΈΡ ΡΡΡΠ±Π²Π° Π΄Π° ΡΠ΅ Π΄ΠΎΠ±Π°Π²ΠΈ ΠΊΡΠΌ ΡΠΏΠΈΡΡΠΊΠ° Π²ΠΌΠ΅ΡΡΠΎ ΠΏΡΡΠ²ΠΈΡ (ΡΡΡ ΡΡΡΠΈΡ owner_id), ΡΠ»Π΅Π΄ ΠΊΠΎΠ΅ΡΠΎ ΡΠΏΠΈΡΡΠΊΡΡ ΡΠ΅ ΡΠΎΡΡΠΈΡΠ° ΠΎΡΠ½ΠΎΠ²ΠΎ.
Π’ΠΎΠ΅ΡΡ Π²ΠΈΠ½Π°Π³ΠΈ ΠΏΠΎΠ»ΡΡΠ°Π²Π°ΠΌΠ΅, ΡΠ΅ ΡΠΏΠΈΡΡΠΊΡΡ ΠΈΠΌΠ° Π½Π΅ ΠΏΠΎΠ²Π΅ΡΠ΅ ΠΎΡ Π΅Π΄ΠΈΠ½ Π·Π°ΠΏΠΈΡ Π·Π° Π²ΡΠ΅ΠΊΠΈ ΠΎΡ ΠΊΠ»ΡΡΠΎΠ²Π΅ΡΠ΅ (Π°ΠΊΠΎ Π·Π°ΠΏΠΈΡΠΈΡΠ΅ ΡΠ° ΡΠ²ΡΡΡΠΈΠ»ΠΈ ΠΈ Π½Π΅ ΡΠΌΠ΅ βΠΏΡΠ΅ΡΠ΅ΠΊΠ»ΠΈβ, ΡΠΎΠ³Π°Π²Π° ΠΏΡΡΠ²ΠΈΡΡ Π·Π°ΠΏΠΈΡ ΠΏΡΠΎΡΡΠΎ ΡΠ΅ ΠΈΠ·ΡΠ΅Π·Π½Π΅ ΠΎΡ ΡΠΏΠΈΡΡΠΊΠ° ΠΈ Π½ΠΈΡΠΎ Π½ΡΠΌΠ° Π΄Π° Π±ΡΠ΄Π΅ Π΄ΠΎΠ±Π°Π²Π΅Π½ΠΎ ), ΠΈ ΡΠ΅ Π²ΠΈΠ½Π°Π³ΠΈ ΡΠΎΡΡΠΈΡΠ°Π½ΠΈ Π²ΡΠ² Π²ΡΠ·Ρ
ΠΎΠ΄ΡΡ ΡΠ΅Π΄ Π½Π° ΠΊΠ»ΡΡΠ° Π½Π° ΠΏΡΠΈΠ»ΠΎΠΆΠ΅Π½ΠΈΠ΅ΡΠΎ (task_date, id).
Π‘ΡΡΠΏΠΊΠ° 3: Π€ΠΈΠ»ΡΡΠΈΡΠ°Π½Π΅ ΠΈ ΡΠ°Π·ΡΠΈΡΡΠ²Π°Π½Π΅ Π½Π° Π·Π°ΠΏΠΈΡΠΈ
Π ΡΠ°ΡΡΡΠ° ΠΎΡ ΡΠ΅Π΄ΠΎΠ²Π΅ΡΠ΅ Π½Π° Π½Π°ΡΠ°ΡΠ° ΡΠ΅ΠΊΡΡΡΠΈΠ²Π½Π° ΡΠ΅Π»Π΅ΠΊΡΠΈΡ, Π½ΡΠΊΠΎΠΈ Π·Π°ΠΏΠΈΡΠΈ rv
ΡΠ΅ Π΄ΡΠ±Π»ΠΈΡΠ°Ρ - ΠΏΡΡΠ²ΠΎ Π½Π°ΠΌΠΈΡΠ°ΠΌΠ΅ ΠΊΠ°ΡΠΎ βΠΏΡΠ΅ΡΠΈΡΠ°Π½Π΅ Π½Π° Π³ΡΠ°Π½ΠΈΡΠ°ΡΠ° Π½Π° 2-ΡΠΈ Π·Π°ΠΏΠΈΡ ΠΎΡ ΡΠΏΠΈΡΡΠΊΠ°β, Π° ΡΠ»Π΅Π΄ ΡΠΎΠ²Π° Π·Π°ΠΌΠ΅ΡΡΠ²Π°ΠΌΠ΅ ΠΊΠ°ΡΠΎ 1-Π²ΠΈ ΠΎΡ ΡΠΏΠΈΡΡΠΊΠ°. Π ΡΠ°ΠΊΠ°, ΠΏΡΡΠ²ΠΎΡΠΎ ΡΡΠ΅ΡΠ°Π½Π΅ ΡΡΡΠ±Π²Π° Π΄Π° Π±ΡΠ΄Π΅ ΡΠΈΠ»ΡΡΠΈΡΠ°Π½ΠΎ.
Π£ΠΆΠ°ΡΠ½ΠΎ ΠΏΠΎΡΠ»Π΅Π΄Π½ΠΎ Π·Π°ΠΏΠΈΡΠ²Π°Π½Π΅
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