Из названия не понятно буду ли я писать про странное тестовое задание или про то, как я упустил работу мечты. Дело в том, что я и сам не знаю. Собственно эту дилемму хочу показать вам, а вы меня рассудите. По дороге я покажу, на мой взгляд, интересный способ, как выбрать из очереди еще необработанные элементы с помощью не подходящего индекса.
Диалект SQL от PostgreSQL.
Итак, задание.
Имеем таблицу “msg”,
CREATE TABLE msg ( id numeric(10) PRIMARY KEY ,status numeric(8) NOT NULL ,process_date date NOT NULL ,some_text char(1000) ); CREATE INDEX msg_idx ON msg (process_date); ANALYZE msg;
в которую помещаются сообщения, требующие обработки. Сообщения обрабатываются в порядке возрастания значения поля “process_date“. Каждый обработанный элемент в очереди помечается значением ‘10782572’. Сообщения извлекаются из очереди порциями по 100 записей. Подавляющая часть сообщений в таблице “msg” уже обработана (status = 10782572). Для извлечения очередной порции сообщений используется следующий SQL-запрос:
SELECT id ,process_date ,status FROM msg WHERE status <> '10782572'::numeric ORDER BY process_date FETCH FIRST (100) ROWS ONLY;
Суть задания:
Является ли используемый SQL-запрос оптимальным? Если нет, то почему? Как его можно изменить, чтобы добиться максимального быстродействия?
Поехали.
Должен сказать, что вместе с заданием шел скрипт наполнения таблицы тестовыми данными, который я тут приводить не буду. Он основан на функции random и всё время будет выдавать разный набор.
На что я сразу обратил внимание - это использование бесполезного rows only и fetch вместо limit.
Но спишем все это на попытку запутать. Ведь само задание кажется очень и очень простым.
Решение.
Для начала взглянем на план выполнения без всяких переделок, чтобы ответить на вопрос является ли запрос оптимальным.
Limit (cost=0.42..2937.72 rows=100 width=16) (actual time=3403.035..3406.669 rows=100 loops=1) -> Index Scan using msg_idx on msg (cost=0.42..590396.06 rows=20100 width=16) (actual time=3403.034..3406.659 rows=100 loops=1) Filter: (status <> '10782572'::numeric) Rows Removed by Filter: 962028 Planning Time: 0.222 ms Execution Time: 3406.693 ms
Время выполнения 3.4 секунды. В очереди на кассе это ерунда, а вот под нагрузкой это время может отозваться болью у каждого пользователя. Собственно ожидание больше секунды уже нестерпимая боль.
Слово Filter это вообще красный флаг, оно означает, что скорее всего мы прочитали с диска гораздо больше чем нужно запросу, а затем откинули не нужные строки.
Row Removed нам это и подтверждает. 962028 строк из прочитанных 1000000 было отброшено. Собственно прочитан был весь индекс по process_date. Ну не беда ли?!
Вот так я и ответил. На что мне сказали, что
не нужно было комментировать этот план.
Давайте посмотрим на тестовые данные, которые сгенерировал скрипт из задания. Я приведу урезанную версию, чтобы было понятно.
id | status | process_date | some_text |
1 | 10782572 | 2026-01-01 | asdf |
2 | 10782572 | 2026-01-02 | dfghj |
3 | 12 | 2026-01-03 | ;lkj |
4 | 10782572 | 2026-01-04 | dfgh |
5 | 34 | 2026-01-05 | gert |
Заметьте что 2026-01-04 обработано, т.к. status = 10782572.
Но постойте, ведь в задании сказано, что
Сообщения обрабатываются в порядке возрастания значения поля “process_date“.
Здесь же получается, что 3-е число еще не обработано, 4-ое обработано, а 5-ое снова не обработано.
Странно все это подумал я и решил, что данный факт просто опишу в решении.
Итак первый алгоритм, который ко мне пришел был следующим.
with recursive cte as ( select max(process_date) as pd from msg union all select distinct process_date from msg fm join cte on (cte.pd - interval '1 day') = fm.process_date where fm.status <> '10782572'::numeric(8) ) SELECT id ,process_date ,status FROM msg WHERE process_date = any(array(select pd from cte)) and status <> '10782572'::numeric(8) ORDER BY process_date FETCH FIRST (100) ROWS ONLY;
Давайте разберем что я тут делаю.
Если вкратце, то я использую только индексный доступ, чтобы определить только необработанные элементы.
Быстро выбираю самую последнюю запись
select max(process_date) as pd from msg
Далее рекурсивно спускаюсь вниз по временной шкале пока не встречу полностью обработанную дату
select distinct process_date from msg fm join cte on (cte.pd - interval '1 day') = fm.process_date where fm.status <> '10782572'::numeric(8)
Т.е. если запрос ничего не вернул, значит этот день полностью обработан и рекурсия останавливается.
После того, как я нашел диапазон необработанных дат остаётся только их выбрать из основной таблицы, чтобы захватить нужные поля, далее отсортировать и взять первые 100 элементов.
SELECT id ,process_date ,status FROM msg WHERE process_date = any(array(select pd from cte)) and status <> '10782572'::numeric(8) ORDER BY process_date FETCH FIRST (100) ROWS ONLY;
План выполнения показывать не буду, чтобы не загромождать, но он поверьте хорош, т.к. там нет столь тяжелого Rows Removed.
Вполне довольный решил переспать с этим алгоритмом. Вдруг чего умнее придет в голову.
И ведь пришло.
with date_range as ( select (select max(process_date) from msg) as date_to, (select process_date from msg where status = '10782572'::numeric(8) order by process_date desc limit 1 ) as date_from ) SELECT id ,process_date ,status FROM msg fm join date_range dr on fm.process_date between dr.date_from and dr.date_to WHERE fm.status <> 10782572::numeric(8) ORDER BY process_date FETCH FIRST (100) ROWS ONLY;
Идея та же самая, но вот рекурсии уже нет. Её я заменил на индексный доступ в обратном порядке
(select process_date from msg where status = '10782572'::numeric(8) order by process_date desc limit 1 ) as date_from
Запрос выше последовательно сканирует индекс по process_date в обратном порядке пока не встретит обработанную запись. А вот как только такая запись находится limit 1 останавливает сканирование и возвращает эту запись. Красота, только есть проблема. Если за последней (мы идем в обратном порядке) обработанной записью есть не обработанные, мы об этом никогда не узнаем.
Итак, задание я посчитал выполненным. Указал в нем на проблему не последовательной обработки элементов и послал на проверку.
Ответ был таков (но я его своими словами опишу).
Первая часть задания нацелена на то, чтобы оптимизатор не использовал мешающий индекс. К тому же мы увидели несостыковки в части логики.
«Мешающий индекс» подумал я. Бывают такие, конечно, среди десятка других, но тут то он один. А задание в том чтобы переписать запрос. В общем, я был обескуражен, взволнован и удивлен одновременно.
Отключим индекс и посмотрим на план выполнения. Только вот данных для наглядности добавим побольше. В сумме, примерно 55 млн.
set enable_indexscan = off; explain(analyze) SELECT id ,process_date ,status FROM msg WHERE status <> 10782572::numeric(8) ORDER BY process_date FETCH FIRST (100) ROWS ONLY;
Limit (cost=772626.06..772637.72 rows=100 width=16) (actual time=3691.510..3701.549 rows=100 loops=1) -> Gather Merge (cost=772626.06..775155.80 rows=21682 width=16) (actual time=3646.649..3656.677 rows=100 loops=1) Workers Planned: 2 Workers Launched: 2 -> Sort (cost=771626.03..771653.14 rows=10841 width=16) (actual time=3623.705..3623.715 rows=67 loops=3) Sort Key: process_date Sort Method: top-N heapsort Memory: 32kB Worker 0: Sort Method: top-N heapsort Memory: 32kB Worker 1: Sort Method: top-N heapsort Memory: 32kB -> Parallel Seq Scan on msg (cost=0.00..771211.70 rows=10841 width=16) (actual time=122.181..3620.000 rows=13319 loops=3) Filter: (status <> '10782572'::numeric(8,0)) Rows Removed by Filter: 18526680 Planning Time: 0.079 ms JIT: Functions: 13 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 1.571 ms (Deform 0.690 ms), Inlining 272.073 ms, Optimization 68.598 ms, Emission 52.106 ms, Total 394.348 ms Execution Time: 3702.109 ms
Снова видим Filter и громадный Rows Removed. Время выполнения почти 4 секунды.
В итоге, но уже без надежды на успех постарался на пальцах описать, почему индекс не только не мешает, но и помогает. При этом сделал число 2026-01-04 не обработанным.
Вот план моего варианта.
set enable_indexscan = on; explain(analyze) with date_range as ( select (select max(process_date) from msg) as date_to, (select process_date from msg where status = 10782572::numeric(8) order by process_date desc limit 1 ) as date_from ) SELECT id ,process_date ,status FROM msg fm join date_range dr on fm.process_date between dr.date_from and dr.date_to WHERE fm.status <> 10782572::numeric(8) ORDER BY process_date FETCH FIRST (100) ROWS ONLY;
Limit (cost=1.41..346469.99 rows=100 width=16) (actual time=85.106..85.274 rows=100 loops=1) InitPlan 1 -> Limit (cost=0.44..0.50 rows=1 width=4) (actual time=69.283..69.284 rows=1 loops=1) -> Index Scan Backward using msg_idx on msg (cost=0.44..3149956.72 rows=55729572 width=4) (actual time=69.278..69.278 rows=1 loops=1) Filter: (status = '10782572'::numeric(8,0)) Rows Removed by Filter: 39957 InitPlan 3 -> Result (cost=0.46..0.47 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=1) InitPlan 2 -> Limit (cost=0.44..0.46 rows=1 width=4) (actual time=0.017..0.017 rows=1 loops=1) -> Index Only Scan Backward using msg_idx on msg msg_1 (cost=0.44..1036483.85 rows=55755590 width=4) (actual time=0.014..0.014 rows=1 loops=1) Heap Fetches: 0 -> Index Scan using msg_idx on msg fm (cost=0.44..450409.61 rows=130 width=16) (actual time=72.283..72.443 rows=100 loops=1) Index Cond: ((process_date >= (InitPlan 1).col1) AND (process_date <= (InitPlan 3).col1)) Filter: (status <> '10782572'::numeric(8,0)) Rows Removed by Filter: 983 Planning Time: 0.367 ms JIT: Functions: 16 Options: Inlining false, Optimization false, Expressions true, Deforming true Timing: Generation 1.312 ms (Deform 0.494 ms), Inlining 0.000 ms, Optimization 0.474 ms, Emission 12.407 ms, Total 14.193 ms Execution Time: 86.705 ms
Rows Removed минимален, как и количество отброшенных записей. К тому же оптимизированный вариант работает намного быстрей.
Ответ был таков.
Мы не можем гарантировать условия последовательной обработки элементов очереди.
Это видимо и были мои несостыковки в логике. Хотя вариант с рекурсивным cte решает эту проблему
Интересоваться, почему в задании сказано, что элементы обрабатываются в порядке возрастания process_date, а гарантировать они это не могут я не стал. Понятное дело не стал. Ведь в задании про это ни слова.
Вообще говоря, для обработки очереди нам даны for update и skip locked.
От себя. Я не жалею, что упустил работу мечта, но мне очень интересно, где я был не прав. Хотя возможно вы тоже видите, что в задании упущены ключевые моменты, без которых, выполнить его сложно.
P.S.
Условие
status <> 10782572::numeric(8)
я писал и так и эдак, но это константа и в плане выглядит одинаково.
