Как оптимизировать медленные запросы PostgreSQL без UNION

“Магический” трюк с UNION работает, потому что он включает параллельное выполнение запросов в PostgreSQL, заставляя планировщик запросов использовать операцию Parallel Append, которая позволяет нескольким рабочим процессам одновременно сканировать таблицу. Это преобразует последовательное сканирование по индексу в параллельное сканирование битовой карты кучи, что dramatically сокращает время выполнения с 292 секунд до всего 82 миллисекунд.

Содержание

• Понимание разницы в производительности
• Почему трюк с UNION включает параллельную обработку
• Альтернативные стратегии оптимизации
• Параметры конфигурации PostgreSQL
• Стратегии оптимизации индексов
• Техники перезаписи запросов
• Рассмотрения по секционированию

Понимание разницы в производительности

Драматическая разница в производительности между вашими двумя запросами обусловлена тем, как планировщик запросов PostgreSQL выбирает стратегии выполнения на основе структуры запроса. Ваш исходный запрос использует простое Index Scan Backward по индексу ts_key_idx, который обрабатывает данные последовательно в одном процессе.

Согласно документации по параллельным планам PostgreSQL, планировщик может выбрать параллельное объединение обычных планов сканирования по индексу, где “каждое отдельное сканирование по индексу должно выполняться до завершения одним процессом, но разные сканирования могут выполняться одновременно разными процессами”.

Ключевое понимание заключается в том, что ваш “быстрый” запрос с UNION ALL создает сценарий, в котором PostgreSQL может:

1. Использовать несколько рабочих процессов для сканирования таблицы
2. Применить операции с битовой картой для более эффективного извлечения данных
3. Использовать разделяемые структуры памяти для параллельной обработки

Как указано в документации, “когда PostgreSQL необходимо объединить строки из нескольких источников в один результирующий набор, он использует узлы плана Append или MergeAppend. Это обычно происходит при реализации UNION ALL или при сканировании секционированной таблицы.”

Почему трюк с UNION включает параллельную обработку

Трюк с UNION работает, заставляя планировщик запросов перейти на путь параллельного выполнения. Когда вы добавляете даже несуществующее условие вроде WHERE csh.key = 'rubbish', PostgreSQL видит это как требование выполнения нескольких отдельных сканирований, которые могут выполняться параллельно.

Из результатов исследований мы узнаем, что “при параллельном сканировании битовой карты кучи один процесс выбирается в качестве лидера” и что “один процесс выбирается в качестве лидера” при параллельных операциях с битовой картой. Это указывает на то, что трюк с UNION создает несколько путей сканирования, которые могут распределяться между рабочими процессами.

Улучшение производительности, которое вы наблюдаете (в 3500 раз быстрее), соответствует исследованиям, показывающим, что сканирование по битовой карте индекса может быть “в 3 раза быстрее при использовании 3 рабочих процессов, в то время как общий план ускорился примерно на 40%” в некоторых случаях, хотя ваше улучшение еще более значительное.

Критическое ограничение заключается в том, что “[не существует] параллельного сканирования по битовой карте индекса”, как отмечено в одном из источников. Однако фаза сканирования кучи может быть распараллелена, что и обеспечивает трюк с UNION.

Альтернативные стратегии оптимизации

1. Явные подсказки запросов и контроли планировщика

PostgreSQL предоставляет несколько способов влиять на планировщик запросов, не прибегая к трюкам с UNION:

-- Принудительное параллельное выполнение
SET max_parallel_workers_per_gather = 4;
SET max_parallel_workers = 8;

-- Использование конкретных методов соединения
SET enable_seqscan = off;
SET enable_indexscan = on;
SET enable_bitmapscan = on;

2. Материализованные представления

Для часто выполняемых запросов рассмотрите возможность создания материализованного представления:

CREATE MATERIALIZED VIEW fast_key_lookup AS
SELECT key, ts 
FROM table_partition202508
WHERE key IN ('string1', 'string2');

CREATE INDEX mv_fast_key_lookup_idx ON fast_key_lookup (ts);

3. Перезапись запросов для параллельной обработки

Вместо UNION попробуйте перезаписать ваш запрос так, чтобы создать несколько условий, которые могут быть распараллелены:

-- Использование условий OR, которые могут оптимизироваться отдельно
SELECT csh.key, csh.ts
FROM table_partition202508 csh
WHERE (csh.key = 'string1' OR csh.key = 'string2')
ORDER BY csh.ts
LIMIT 10;

4. Использование LATERAL соединений

SELECT csh.key, csh.ts
FROM (VALUES ('string1'), ('string2')) AS keys(key)
LEFT JOIN LATERAL (
    SELECT key, ts
    FROM table_partition202508
    WHERE key = keys.key
    ORDER BY ts
    LIMIT 10
) csh ON true
ORDER BY csh.ts;

Параметры конфигурации PostgreSQL

Несколько параметров конфигурации могут влиять на то, выбирает ли PostgreSQL планы параллельного выполнения:

Настройки параллельных запросов

-- Количество рабочих процессов на операцию gather
SET max_parallel_workers_per_gather = 4;

-- Общее количество рабочих процессов
SET max_parallel_workers = 8;

-- Минимальный размер таблицы для параллельного сканирования
SET min_parallel_table_scan_size = '8MB';

-- Минимальный размер индекса для параллельного сканирования
SET min_parallel_index_scan_size = '512kB';

Параметры стоимости

-- Стоимость передачи кортежей между процессами
SET parallel_tuple_cost = 0.1;  -- По умолчанию 0.1

-- Стоимость запуска параллельных рабочих процессов
SET parallel_setup_cost = 1000.0;

-- Случайная стоимость страницы (влияет на решения о выборе сканирования по битовой карте или по индексу)
SET random_page_cost = 1.1;  -- По умолчанию 1.1 для SSD, 4.0 для HDD

Настройки рабочей памяти

-- Рабочая память для операций сортировки
SET work_mem = '100MB';

-- Память для операций обслуживания
SET maintenance_work_mem = '256MB';

Как отмечено в исследованиях, “увеличение random_page_cost до 2 привело к следующему результату explain”, что может значительно влиять на выбор сканирования по битовой карте вместо сканирования по индексу.

Стратегии оптимизации индексов

1. Оптимизация составных индексов

Ваши текущие индексы хорошо спроектированы, но рассмотрите возможность создания дополнительных специализированных индексов:

-- Для вашего конкретного шаблона запроса
CREATE INDEX partitioned_key_ts_idx ON table_partition202508 (key, ts) 
WHERE key IN ('string1', 'string2');

-- Частичный индекс для более быстрых запросов
CREATE INDEX fast_string1_idx ON table_partition202508 (ts) 
WHERE key = 'string1';

CREATE INDEX fast_string2_idx ON table_partition202508 (ts) 
WHERE key = 'string2';

2. Реорганизация индексов

Как показано в результатах исследований, “SET maintenance_work_mem TO ‘1GB’; CLUSTER foo USING val_index;” может значительно улучшить производительность сканирования по битовой карте:

-- Реорганизовать таблицу с использованием вашего индекса ключа
ALTER TABLE table_partition202508 CLUSTER USING table_partition_key_ts_idx;

3. Оптимизация сканирования по индексу

-- Рассмотрите возможность создания покрывающего индекса
CREATE INDEX covering_key_ts_idx ON table_partition202508 (key, ts) 
INCLUDE (id);  -- если вам нужны дополнительные столбцы

Техники перезаписи запросов

1. Использование WITH-блоков (CTE)

WITH key_data AS (
    SELECT key, ts
    FROM table_partition202508
    WHERE key IN ('string1', 'string2')
)
SELECT key, ts
FROM key_data
ORDER BY ts
LIMIT 10;

2. Подход с оконными функциями

SELECT key, ts
FROM (
    SELECT key, ts,
           ROW_NUMBER() OVER (PARTITION BY key ORDER BY ts) as rn
    FROM table_partition202508
    WHERE key IN ('string1', 'string2')
) ranked
WHERE rn <= 10
ORDER BY ts;

3. Использование EXPLAIN ANALYZE для тестирования разных подходов

EXPLAIN (ANALYZE, BUFFERS)
SELECT csh.key, csh.ts
FROM table_partition202508 csh
WHERE csh.key IN ('string1', 'string2')
ORDER BY csh.ts
LIMIT 10;

Рассмотрения по секционированию

1. Оптимизация отбрасывания секций

Ваша таблица уже секционирована по дате, но убедитесь, что отбрасывание секций работает эффективно:

-- Проверьте, какие секции используются
EXPLAIN (ANALYZE, BUFFERS)
SELECT csh.key, csh.ts
FROM table_partition csh
WHERE csh.key IN ('string1', 'string2')
AND csh.ts BETWEEN '2025-08-01' AND '2025-08-31'
ORDER BY csh.ts
LIMIT 10;

2. Индексирование на уровне секций

Рассмотрите возможность создания индексов для каждой секции вместо глобальных индексов:

-- Создание индексов, специфичных для секций
CREATE INDEX table_partition202508_key_ts_idx 
ON table_partition202508 (key, ts);

-- Рассмотрите хэш-индексы для условий равенства
CREATE INDEX table_partition202508_key_bitmap_idx 
ON table_partition202508 USING hash (key);

3. Параллельное сканирование секций

Для больших таблиц убедитесь, что параллельная обработка может работать между секциями:

-- Включите параллельные запросы для секционированных таблиц
SET max_parallel_workers_per_gather = 4;
SET max_parallel_workers = 8;
SET max_parallel_maintenance_workers = 4;

Заключение

“Магический” трюк с UNION работает, заставляя PostgreSQL перейти на план параллельного выполнения, который объединяет несколько операций сканирования. Однако несколько альтернатив могут обеспечить аналогичную производительность без этого обходного решения:

1. Оптимизируйте конфигурацию PostgreSQL, настраивая параметры параллельных запросов, настройки рабочей памяти и факторы стоимости, чтобы поощрять планировщик выбирать сканирование по битовой карте и параллельное выполнение.

2. Реорганизуйте ваши индексы с помощью команды CLUSTER, что может значительно улучшить производительность сканирования по битовой карте, как показано в результатах исследований.

3. Рассмотрите частичные и специализированные индексы, которые могут быть более эффективно просканированы для ваших конкретных шаблонов запросов.

4. Перезаписывайте ваши запросы с использованием CTE, lateral соединений или других конструкций, которые могут включать параллельную обработку без трюка с UNION.

5. Более эффективно используйте вашу схему секционирования, убедившись, что отбрасывание секций работает и создавая соответствующие индексы, специфичные для секций.

Ключевой вывод заключается в том, что планировщик запросов PostgreSQL нуждается в правильных условиях для выбора планов параллельного выполнения. Понимая эти условия и правильно настраивая вашу базу данных, вы можете получить преимущества параллельной обработки без прибегания к трюкам с запросами.

Источники

1. PostgreSQL: Документация: 18: 15.3. Параллельные планы
2. PostgreSQL: Документация: 11: 15.3. Параллельные планы
3. PostgreSQL: Документация: 15: 15.3. Параллельные планы
4. PostgreSQL: Документация: 12: 15.3. Параллельные планы
5. PostgreSQL: Документация: 13: 15.3. Параллельные планы
6. PostgreSQL: Самая продвинутая в мире открытая объектно-реляционная база данных
7. PostgreSQL: Re: Параллельное сканирование по битовой карте индекса
8. PostgreSQL Bitmap Heap Scan по индексу очень медленный, но Index Only Scan быстрый - Stack Overflow
9. В PostgreSQL, как я могу сделать сканирование по битовой карте индекса параллельным? - Database Administrators Stack Exchange
10. PostgreSQL: Документация: 10: 15.3. Параллельные планы
11. PostgreSQL: Документация: 8.1: Советы по производительности
12. Параллельные запросы v2 | EDB
13. Производительность запросов сканирования по индексу медленнее, чем параллельное последовательное сканирование в Postgres - Database Administrators Stack Exchange
14. PostgreSQL: Документация: 18: 19.7. Планирование запросов
15. PostgreSQL: Оптимизация Bitmap Heap Scan - Database Administrators Stack Exchange