פּיריאַדיקלי, די אַרבעט פון זוכן פֿאַר פֿאַרבונדענע דאַטן מיט אַ גאַנג פון שליסלען ערייזאַז. ביז מיר באַקומען די פארלאנגט גאַנץ נומער פון רעקאָרדס.
די מערסט "פאַקטיש לעבן" בייַשפּיל איז צו ווייַזן 20 אָולדאַסט פּראָבלעמס, ליסטעד אויף דער רשימה פון עמפּלוייז (למשל, אין איין אָפּטייל). פֿאַר פאַרשידן פאַרוואַלטונג "דאַשבאָרדז" מיט קורץ סאַמעריז פון אַרבעט געביטן, אַ ענלעך טעמע איז פארלאנגט גאַנץ אָפט.
אין דעם אַרטיקל מיר וועלן קוקן אין די ימפּלאַמענטיישאַן אין PostgreSQL פון אַ "נאַיוו" לייזונג צו אַזאַ אַ פּראָבלעם, אַ "סמאַרטער" און זייער קאָמפּליצירט אַלגערידאַם. "שלייף" אין SQL מיט אַן אַרויסגאַנג צושטאַנד פון די געפֿונען דאַטן, וואָס קענען זיין נוציק ביידע פֿאַר אַלגעמיין אַנטוויקלונג און פֿאַר נוצן אין אנדערע ענלעך קאַסעס.
זאל ס נעמען אַ פּרובירן דאַטן שטעלן פון . צו פאַרמיידן די געוויזן רעקאָרדס פון צייט צו צייט "שפּרינגען" ווען די סאָרטעד וואַלועס צונויפפאַלן, יקספּאַנד די ונטערטעניק אינדעקס דורך אַדינג אַ ערשטיק שליסל. אין דער זעלביקער צייט, דאָס וועט מיד געבן עס אייגנארטיקייט און גאַראַנטירן אונדז אַז די סאָרטירונג סדר איז אַנאַמביגיואַס:
CREATE INDEX ON task(owner_id, task_date, id);
-- а старый - удалим
DROP INDEX task_owner_id_task_date_idx;ווי עס ווערט געהערט, אַזוי שטייט געשריבן
ערשטער, לאָזן ס סקיצע אויס די סימפּלאַסט ווערסיע פון די בקשה, פאָרן די IDs פון די פּערפאָרמערז :
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 רעקאָרדס, אָבער אינדעקס סקאַן אומגעקערט עס צו אונדז 960 שורות, װא ם הא ט דעמאל ט אוי ך געמוז ט װערן .
ומנעסט + אַרריי
דער ערשטער באַטראַכטונג וואָס וועט העלפֿן אונדז איז אויב מיר דאַרפֿן בלויז 20 סאָרטירט רעקאָרדס, דעמאָלט נאָר לייענען ניט מער ווי 20 אויסגעשטעלט אין דער זעלביקער סדר פֿאַר יעדער שליסל. גוט, פּאַסיק אינדעקס (אָוונער_יד, טאַסק_דאַטע, שייַן) מיר האָבן.
לאָמיר נוצן די זעלבע מעקאַניזאַם פֿאַר יקסטראַקטינג און "פאַרשפּרייטן אין שפאלטן" ינטאַגראַל טיש רעקאָרד, אזויווי אין . מיר קענען אויך צולייגן פאָלדינג אין אַ מענגע ניצן די פֿונקציע 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, אָבער אַפֿילו ווייניקער - איז עס מעגלעך?
זאל ס פּרובירן צו נוצן די וויסן וואָס מיר דאַרפֿן total xnumx רעקאָרדס. דאָס איז, מיר וועלן יטערייט דאַטן לייענען בלויז ביז מיר דערגרייכן די סומע וואָס מיר דאַרפֿן.
שריט 1: סטאַרטינג רשימה
דאָך, אונדזער "ציל" רשימה פון 20 רעקאָרדס זאָל אָנהייבן מיט די "ערשטער" רעקאָרדס פֿאַר איינער פון אונדזער אָונערז_יד שליסלען. דעריבער, ערשטער מיר וועלן געפֿינען אַזאַ "זייער ערשטער" פֿאַר יעדער פון די שליסלען און לייגן עס צו דער רשימה, סאָרטינג עס אין די סדר מיר ווילן - (טאַסק_דאַטע, שייַן).
טרעטן 2: געפֿינען די "ווייַטער" איינסן
איצט אויב מיר נעמען די ערשטער פּאָזיציע פון אונדזער רשימה און אָנהייבן "שריט" ווייַטער צוזאמען דעם אינדעקס פּראַזערווינג די אָונערז_יד שליסל, אַלע די געפֿונען רעקאָרדס זענען פּונקט די ווייַטער אָנעס אין די ריזאַלטינג סעלעקציע. פון קורס, נאָר ביז מיר אַריבער די באַט שליסל רגע פּאָזיציע אין דער רשימה.
אויב עס טורנס אויס אַז מיר "קראָסיז" די רגע רעקאָרד, דעמאָלט די לעצטע פּאָזיציע לייענען זאָל זיין מוסיף צו דער רשימה אַנשטאָט פון דער ערשטער (מיט דער זעלביקער אָונערז_יד), נאָך וואָס מיר שייַעך-סאָרט די רשימה ווידער.
דאָס איז, מיר שטענדיק באַקומען אַז די רשימה האט ניט מער ווי איין פּאָזיציע פֿאַר יעדער פון די שליסלען (אויב די איינסן לויפן אויס און מיר טאָן ניט "קרייז", דער ערשטער פּאָזיציע פון דער רשימה וועט פשוט פאַרשווינדן און גאָרנישט וועט זיין צוגעגעבן. ), און זיי שטענדיק אויסגעשטעלט אין אַסענדינג סדר פון די אַפּלאַקיישאַן שליסל (טאַסק_דאַטע, שייַן).
טרעטן 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