Здравствуйте, мои маленькие любители программирования. Сегодня мы поговорим о простоте.

Нет, Петров, не о той, что Sancta Simplicitas (кстати, вопреки расхожему мнению, фразу вряд ли произнёс Ян Гус). А о той, что пропадает при увеличении акцидентной (побочной) сложности решения.

Исходные данные Ссылка на заголовок

Есть в БД простая таблица-очередь для хранения списка отложенных задач. У каждой задачи есть название, набор параметров, ключ дедупликации и назначенное время старта

class BufferedTaskModel(Base):
    __tablename__ = "buffered_tasks"

    id = Column(BigInteger, primary_key=True, autoincrement=True)
    scheduled_at = Column(sa.DateTime(timezone=True), nullable=False, index=True)
    task_type = Column(String(500), nullable=False)
    deduplication_key = Column(String(100), nullable=False)
    payload = Column(MutableDict.as_mutable(JSONB), nullable=False, server_default=text("'{}'::jsonb"))

Для таблицы создано несколько индексов:

CREATE INDEX ix_buffered_tasks_scheduled_at ON buffered_tasks USING btree (scheduled_at);
CREATE INDEX ix_buffered_tasks_type_key_scheduled_at ON buffered_tasks USING btree (task_type, deduplication_key, scheduled_at);

И в код метода добавлен простой запрос:

SELECT DISTINCT ON (buffered_tasks.task_type,
                    buffered_tasks.deduplication_key)
       buffered_tasks.task_type AS buffered_tasks_task_type,
       buffered_tasks.deduplication_key AS buffered_tasks_deduplication_key
FROM buffered_tasks
WHERE buffered_tasks.scheduled_at <= now()
ORDER BY buffered_tasks.task_type,
         buffered_tasks.deduplication_key,
         buffered_tasks.scheduled_at
LIMIT 500;

Ничего не предвещало беды Ссылка на заголовок

Ну как, Васечкин, сможешь ли ты своими словами описать, что именно делает этот запрос, не подглядывая в ChatGPT при этом?

Выбери из таблицы записи, в которых комбинация полей task_type и deduplication_key будет уникальной, отсортируй результат и верни первые 500 записей. Похоже, программист очень хотел “попасть” в индекс ix_buffered_tasks_type_key_scheduled_at.

Всё верно, Пётр. Отдельно хочется отметить правильное замечание по поводу индекса. Прошу взглянуть на план запроса, предварительно сделав ставки - как планировщик PG оценит execution time?

Я бы сказал, что меньше секунды, но быстрые запросы мы не разбираем. Поэтому ставлю на 10 секунд.

Хорошо, Васечкин. Что скажет наш коллега Василий?

Не больше 5 секунд. План очень сильно подогнали под индекс, который скорее всего, содержит сразу все данные.

Отлично, Петров. Но, если индекс действительно окажется covered - то откуда возьмутся 5 секунд? 5 миллисекунд, скорее. Но к чему эти догадки, вот он, красавец:

Limit (actual time=1200..40600 rows=500 loops=1)
  Buffers: shared hit=21500 read=81700 written=400
  I/O Timings: shared read=40200 write=6
  ->  Unique (actual time=1200..40600 rows=500 loops=1)
        Buffers: shared hit=21500 read=81700 written=400
        I/O Timings: shared read=40200 write=6
        ->  Index Only Scan using ix_buffered_tasks_type_key_scheduled_at on buffered_tasks
              (actual time=1200..40600 rows=500 loops=1)
              Index Cond: (scheduled_at <= '<timestamp>'::timestamp with time zone)
              Heap Fetches: 6500
              Index Searches: 4900
              Buffers: shared hit=21500 read=81700 written=400
              I/O Timings: shared read=40200 write=6
Planning Time: 0.1 ms
Execution Time: 40600 ms

Запрос, действительно, пошёл по индексу ix_buffered_tasks_type_key_scheduled_at, и все данные он нашёл в индексе… Но вы поглядите на время выполнения! 40 секунд на то, чтобы вернуть 500 записей ?! Давайте разберёмся, что же могло пойти не так? Не будем пока обращать внимание на счётчик Heap Fetches - скорее всего, автовакуум для данной таблицы настроен неэффективно, и Postgres вынужден проверять, а не удалена ли конкретно эта запись из БД…

Поглядите на показатель Index Searches!

Спасибо, Маша “Острый Глаз”! Действительно, все данные для выполнения запроса имелись в самом индексе, но, чтобы добыть их, Postgres был вынужден многократно пробежать по индексу в поисках этих данных. Как же так получилось?

“Все правильно Лобанов, но это же неправильно. В чем подвох?” (С) Доктор Быков, сериал “Интерны” Ссылка на заголовок

А подвох здесь, дорогие мои, в особенности B-tree индекса ix_buffered_tasks_type_key_scheduled_at, который планировщик БД выбрал для выполнения запроса. Как мы помним, порядок полей, указанных при создании индекса, крайне важен для эффективного использования. Как говорит нам строка Index Cond, планировщик использует поле scheduled_at для выборки данных по единственному условию. Но проблема в том, что поле scheduled_at в индексе стоит не на первом месте, и не на втором, а на последнем! Другими словами, чтобы добраться до нужных данных в индексе, надо прочитать его почти полностью, и выбрать только те, что удовлетворяют условию.

Если убрать из запроса условие фильтрации, то план запроса будет куда эффективнее:

Limit (actual time=6..228 rows=500 loops=1)
  Buffers: shared hit=400 read=400 written=2
  I/O Timings: shared read=225 write=0
  ->  Unique (actual time=6..228 rows=500 loops=1)
        Buffers: shared hit=400 read=400 written=2
        I/O Timings: shared read=225 write=0
        ->  Index Only Scan using ix_buffered_tasks_type_key_scheduled_at on buffered_tasks
              (actual time=6..228 rows=500 loops=1)
              Heap Fetches: 400
              Index Searches: 1
              Buffers: shared hit=400 read=400 written=2
              I/O Timings: shared read=225 write=0
Planning:
  Buffers: shared hit=6
Planning Time: 0.1 ms
Execution Time: 228 ms

Тот же индекс, та же стратегия Index Only Scan, но время выполнения менее четверти секунды.

Управляем сложностью Ссылка на заголовок

Но ведь мы не можем просто взять и убрать условие из production-кода? Очевидно, программист решал определённую проблему бизнеса, накладывая такое условие?

И снова верно, Пётр. Мы не ИИ, чтобы молча удалять код, который делает запрос медленее. Мы должны помочь бизнесу, но более эффективным путём. Метод, в котором используется исходный запрос, обрабатывает очередь из задач с отложенным выполнением, выбирая по 500 задач за раз. Логика метода допускает возможность дупликации данных, поэтому из пачки задач с одинаковым task_type и deduplication_key на выполнение будет запущена задача с наименьшим (самым ранним) значением scheduled_at. Остальные дубликаты будут удалены из очереди.

Видимо, записи дубликатов удаляются достаточно часто, раз autovacuum не успевает эффективно почистить таблицу от “мёртвых” записей

Именно, Петров. Но это тема для отдельной вдумчивой беседы. Итак, о чём мы? Ах, да, о решении бизнес-проблемы. Итак, конечной целью будет запуск на выполнение отложенных задач, у которых подошло время запуска. Т.е. метод должен разобрать накопившуюся очередь, очистив её от дубликатов в том числе. Но эту же задачу можно решить и по-другому! Например, так:

Выбери мне сначала пачку записей для задач, у которых подошло время запуска. Отсортируй выборку по времени запуска в восходящем порядке (чем раньше должна быть запущена задача - тем выше в результате она должна быть). А вот уже полученную выборку дедуплицируй.

Переложив это описание на SQL, получим примерно такой запрос:

WITH data AS (
    SELECT
        task_type AS buffered_tasks_task_type,
        deduplication_key AS buffered_tasks_deduplication_key
    FROM buffered_tasks
    WHERE buffered_tasks.scheduled_at <= '<timestamp>'::timestamptz
    LIMIT 500
)
SELECT DISTINCT ON (
    buffered_tasks_task_type,
    buffered_tasks_deduplication_key
)
    buffered_tasks_task_type,
    buffered_tasks_deduplication_key
FROM data
ORDER BY
    buffered_tasks_task_type,
    buffered_tasks_deduplication_key

План выполнения такого запроса будет, ориентировочно, таким:

Unique (actual time=398..398 rows=500 loops=1)
  Buffers: shared hit=5 read=700
  I/O Timings: shared read=395
  ->  Sort (actual time=398..398 rows=500 loops=1)
        Sort Key: data.buffered_tasks_task_type, data.buffered_tasks_deduplication_key
        Sort Method: quicksort  Memory: 100kB
        Buffers: shared hit=5 read=700
        I/O Timings: shared read=395
        ->  Subquery Scan on data (actual time=3..397 rows=500 loops=1)
              Buffers: shared hit=5 read=700
              I/O Timings: shared read=395
              ->  Limit (actual time=3..397 rows=500 loops=1)
                    Buffers: shared hit=5 read=700
                    I/O Timings: shared read=395
                    ->  Index Scan using ix_buffered_tasks_scheduled_at on buffered_tasks
                          (actual time=3..397 rows=500 loops=1)
                          Index Cond: (scheduled_at <= '<timestamp>'::timestamp with time zone)
                          Index Searches: 1
                          Buffers: shared hit=5 read=700
                          I/O Timings: shared read=395
Planning Time: 0.1 ms
Execution Time: 398 ms

Чуть менее эффективно, чем запрос без фильтрации по полю scheduled_at, но в 100 раз быстрее исходного. Это ли не прекрасно, дорогие мои? И, кстати, тут как раз пригодился второй индекс на таблице - ix_buffered_tasks_scheduled_at.

Заключение Ссылка на заголовок

Итак, мои маленькие любители программирования, сегодня мы разобрали крайне интересный сюжет. Подгоняя запрос под индекс, разработчик заставил сначала БД дедуплицировать все записи по всей таблице, а потом выбрать из них те, что подходят по условию фильтрации. Да, при этом использовалось только чтение по индексу, но каждый раз индекс прочитывался полностью. Небольшой семантический сдвиг - сначала выбери часть задач, подходящих под фильтр, а уже потом дедуплицируй - упростил задачу БД. Решение неочевидное, и найти его было непросто. Но не будем забывать, что основным императивом разработки является управление сложностью, на всех возможных уровнях.

На сегодня всё, все свободны, до новых встреч!