.NET Разработчик: “День 2661. #МоиИнструменты #PG Инструменты Оптимизации Запросов в Post…”

День 2661. #МоиИнструменты #PG
Инструменты Оптимизации Запросов в PostgreSQL. Часть 9

9. HypoPG (Гипотетические индексы для PostgreSQL)
Что даёт: тестирование влияния индексов без их создания.

Зачем нужен
Создание индексов на больших таблицах — дорогостоящий процесс. HypoPG позволяет тестировать эффективность индексов (видеть изменения плана запроса) без их фактического создания.

Использование

-- Установка расширения
CREATE EXTENSION hypopg;

1. Анализируем долгий запрос:

EXPLAIN ANALYZE
SELECT * FROM orders 
WHERE customer_id = 12345 
  AND order_date >= '2024-01-01';

Результат:

Seq Scan on orders (cost=0.00..185234.25) (actual time=2341.234ms)

2. Создаём гипотетический индекс:

SELECT hypopg_create_index('CREATE INDEX ON orders(customer_id, order_date)');
-- Вывод: (18001,"<18001>btree_orders(customer_id, order_date)")

ID индекса: 18001

3. Анализируем запрос повторно. Результат:

Index Scan using <18001>btree_orders (cost=0.43..8.45) (actual time=0.112ms)

Улучшение производительности: 2341мс → 0,112мс (в 20000 раз быстрее)
Решение: создать реальный индекс.

4. Удаляем гипотетический индекс и создаём реальный.

SELECT hypopg_drop_index(18001);

CREATE INDEX CONCURRENTLY idx_orders_customer_date 
ON orders(customer_id, order_date);

Тестируем разные варианты индекса
Когда неясно, какой индекс лучше:

-- 1: (customer_id, order_date)
SELECT hypopg_create_index('CREATE INDEX ON orders(customer_id, order_date)');
EXPLAIN SELECT … FROM orders WHERE customer_id = ? AND order_date >= ?;
-- Cost: 8.45

-- 2: (order_date, customer_id) – другой порядок
SELECT hypopg_reset(); -- Удаляем предыдущие гипот. индексы
SELECT hypopg_create_index('CREATE INDEX ON orders(order_date, customer_id)');
EXPLAIN SELECT … FROM orders WHERE customer_id = ? AND order_date >= ?;
-- Cost: 12.34

-- 3: Частичный (только последние заказы)
SELECT hypopg_reset();
SELECT hypopg_create_index('CREATE INDEX ON orders(customer_id) WHERE order_date >= ''2024-01-01''');
EXPLAIN SELECT … FROM orders WHERE customer_id = ? AND order_date >= '2024-01-01';
-- Cost: 6.12 (лучше!)

-- Создаём оптимальный индекс
CREATE INDEX CONCURRENTLY idx_orders_customer_recent 
ON orders(customer_id) 
WHERE order_date >= '2024-01-01';

Когда использовать
- Большие таблицы (создание индекса дорого);
- Не уверены, какой индекс создать;
- Хотите протестировать перед внедрением в прод;
- База в проде (нельзя экспериментировать).

Когда отказаться
- Небольшие таблицы (просто создайте индекс, это быстро);
- Очевидно, какой индекс нужен.

Скрытая функция
Автоматическая рекомендация индекса на основе рабочей нагрузки запросов (совместно с pg_stat_statements):

CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION hypopg;
-- Ищем запросы, которым нужны индексы
SELECT 
  calls,
  total_exec_time,
  query,
  hypopg_create_index(
    'CREATE INDEX ON ' || 
    regexp_replace(query, '^.*FROM\s+(\w+).*WHERE\s+(\w+)\s*=.*', 
                   '\1(\2)')
  ) AS suggested_index
FROM pg_stat_statements
WHERE calls > 100 
  AND total_exec_time > 10000 -- 10+ секунд
  AND query ~* 'WHERE.*=' -- Содержат WHERE
ORDER BY total_exec_time DESC
LIMIT 10;

Предложит индексы для самых медленных запросов. Останется протестировать каждый с помощью EXPLAIN.

С осторожностью
Гипотетические индексы сохраняются в сессии и влияют на все запросы в сессии.

SELECT hypopg_create_index('CREATE INDEX ON orders(customer_id)');
-- …
-- Позже в той же сессии:
EXPLAIN SELECT * FROM orders;

EXPLAIN использует гипотетический индекс, что исказит результаты.

Решения
1. Использовать отдельную сессию для тестов;
2. Удалять гипотетические индексы после тестов:

SELECT hypopg_reset();

3. Использовать транзакции:

BEGIN;
  SELECT hypopg_create_index(…);
  EXPLAIN SELECT …;
ROLLBACK; -- Очистит гипот. индексы

Источник: https://medium.com/@reliabledataengineering/15-sql-optimization-tools-that-make-queries-10x-faster-8629ac451d97