Почему индексы могут быть незадействованы в PostgreSQL?

Почему PostgreSQL не использует индексы

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

1. Оптимизатор считает sequential scan дешевле

PostgreSQL оценивает стоимость двух стратегий:

• Index Scan + случайный доступ к строкам (дорого)
• Sequential Scan (дешевле для небольших таблиц)

CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);
CREATE INDEX idx_users_name ON users(name);
INSERT INTO users (name) SELECT 'user_' || i FROM generate_series(1, 1000) i;

EXPLAIN ANALYZE SELECT * FROM users WHERE name = 'user_500';
-- Seq Scan on users (cost=0.00..20.00 rows=1)
-- При таблице из 1000 строк seq scan часто быстрее!

2. Не хватает статистики

Оптимизатор принимает решения на основе статистики (ANALYZE):

ANALYZE users;
ALTER TABLE users SET (autovacuum = false);
EXPLAIN SELECT * FROM users WHERE id = 500;

3. Неправильно выбран тип индекса

Для разных операций нужны разные индексы:

CREATE TABLE metrics (
    id SERIAL PRIMARY KEY,
    value FLOAT,
    created_at TIMESTAMP
);

CREATE INDEX idx_value ON metrics(value);
-- Диапазонный запрос может не использовать индекс
SELECT * FROM metrics WHERE value > 10 AND value < 20;

-- Лучше использовать BRIN для отсортированных данных
CREATE INDEX idx_value_brin ON metrics USING BRIN (value);

4. Использование функций в WHERE

Оптимизатор не может использовать индекс, если применены функции:

CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT);
CREATE INDEX idx_email ON users(email);

-- Индекс НЕ будет использован!
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

-- Правильно: индекс с функцией
CREATE INDEX idx_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

5. Неправильный порядок в составном индексе

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT,
    status TEXT,
    created_at TIMESTAMP
);

-- Неправильный порядок
CREATE INDEX idx_orders_wrong ON orders(status, user_id, created_at);
SELECT * FROM orders WHERE user_id = 5 AND status = 'pending';

-- Правильный порядок (часто используемые первыми)
CREATE INDEX idx_orders_right ON orders(user_id, status, created_at);
SELECT * FROM orders WHERE user_id = 5 AND status = 'pending';

6. Проблемы с NULL значениями

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    category TEXT,
    deleted_at TIMESTAMP NULL
);

CREATE INDEX idx_deleted ON products(deleted_at);

-- Запрос с IS NULL может не использовать индекс
SELECT * FROM products WHERE deleted_at IS NULL;

-- Правильно: частичный индекс
CREATE INDEX idx_active ON products(deleted_at) WHERE deleted_at IS NULL;

7. Неправильные типы данных

-- Неправильно: id как TEXT
CREATE TABLE users (id TEXT PRIMARY KEY);
SELECT * FROM users WHERE id = '12345';

-- Правильно: id как UUID или INT
CREATE TABLE users (id UUID PRIMARY KEY);
SELECT * FROM users WHERE id = '12345'::uuid;

8. Параметр random_page_cost

Если random_page_cost слишком низкий, seq scan может выглядеть дешевле:

SHOW random_page_cost;  -- По умолчанию: 4.0
SET random_page_cost = 1.1;  -- На SSD можно снизить

9. Данные не отсортированы

Если данные вставляются случайно, случайный доступ через индекс медленнее:

CREATE TABLE logs (id SERIAL PRIMARY KEY, timestamp TIMESTAMP);
CREATE INDEX idx_timestamp ON logs(timestamp);

-- Случайный доступ медленнее seq scan
SELECT * FROM logs WHERE timestamp > NOW() - INTERVAL '1 day';

-- Физическое переупорядочивание данных
CLUSTER logs USING idx_timestamp;

Как проверить использование индекса

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'test@example.com';
-- Ищем: Index Scan (хорошо) или Seq Scan (плохо)

Чек-лист

1. Есть ли индекс? (\d table_name)
2. Используется ли? (EXPLAIN ANALYZE)
3. Статистика свежа? (ANALYZE table_name)
4. Нет ли функций в WHERE?
5. Правильный порядок в составном индексе?
6. Используются ли все колонки?
7. Нет ли type casting?

Вывод

Индексы — это не волшебство. Оптимизатор принимает решение на основе статистики, размера таблицы и типа запроса. Иногда seq scan действительно быстрее, и это нормально.