middle

Какие основные приёмы оптимизации запросов в PostgreSQL?

Оптимизация запросов — это системный процесс: сначала EXPLAIN ANALYZE для диагностики, затем точечные изменения. Восемь основных приёмов покрывают подавляющее большинство ситуаций.

1. Создание подходящих индексов

Пример

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42 AND status = 'active';
-- Если Seq Scan — создать индекс:
CREATE INDEX idx_orders_cust_status ON orders (customer_id, status);

2. Актуальность статистики

Пример

ANALYZE transactions;
-- Увеличить детализацию статистики для столбца
ALTER TABLE transactions ALTER COLUMN status SET STATISTICS 1000;
ANALYZE transactions;

3. Выбирать только нужные столбцы

Пример

-- Плохо:
SELECT * FROM transactions WHERE account_id = 100;
-- Хорошо (может использовать Index Only Scan):
SELECT id, amount, created_at FROM transactions WHERE account_id = 100;

4. Keyset pagination вместо OFFSET

Пример

SELECT * FROM transactions
WHERE account_id = 100 AND id > 1000
ORDER BY id
LIMIT 20;

5. Избегать функций на индексированных столбцах

Пример

-- Плохо (индекс не используется):
SELECT * FROM users WHERE UPPER(email) = 'USER@BANK.RU';
-- Хорошо (создать expression index):
CREATE INDEX idx_email_upper ON users (UPPER(email));

6. EXISTS вместо IN для подзапросов

Пример

-- Менее эффективно для больших подзапросов:
SELECT * FROM clients WHERE id IN (SELECT client_id FROM transactions WHERE amount > 1000000);
-- Более эффективно:
SELECT * FROM clients c WHERE EXISTS (
    SELECT 1 FROM transactions t WHERE t.client_id = c.id AND t.amount > 1000000
);

7. Параллельное выполнение запросов

Пример

SET max_parallel_workers_per_gather = 4;
-- В EXPLAIN видно: Gather -> Parallel Seq Scan

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

Пример

CREATE MATERIALIZED VIEW mv_daily_totals AS
SELECT date(created_at) AS day, sum(amount) AS total
FROM transactions GROUP BY date(created_at);

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_totals;

На собеседовании: не перечисляйте все приёмы списком — расскажите алгоритм: «сначала EXPLAIN ANALYZE, смотрю Seq Scan и Rows Removed, затем добавляю индекс или переписываю запрос». Это показывает системный подход, а не заученный список.

#postgresql