Зачем нужно индексирование в PostgreSQL?

Индексирование в PostgreSQL

Индекс в PostgreSQL — это структура данных, которая ускоряет поиск и получение данных из таблицы. Без индексов БД выполняет полное сканирование таблицы (Sequential Scan), что медленно при большом объёме данных.

Основное назначение

Индексы нужны для:

• Скорость поиска: найти строку по условию в O(log n) вместо O(n)
• Оптимизация WHERE: WHERE name = 'John' выполняется за микросекунды
• Оптимизация JOIN: объединение таблиц становится быстрее
• Уникальность: PRIMARY KEY, UNIQUE constraints используют индексы
• Сортировка: ORDER BY выполняется быстрее

Как работает индекс

Без индекса (Sequential Scan):

Таблица: id | name | email
1  | John   | john@example.com
2  | Jane   | jane@example.com
3  | Bob    | bob@example.com
...
1000000 | Alice | alice@example.com

Запрос: WHERE name = 'Alice'
Проверяет КАЖДУЮ СТРОКУ (1 млн сканирований) - МЕДЛЕННО!

С индексом (Index Scan):

Индекс (B-Tree структура):
        Alice
       /     \
    Bob      John
   /  \
 Alice... ...

Запрос: WHERE name = 'Alice'  
Прямо переходит к Alice (несколько скачков) - БЫСТРО!

Типы индексов в PostgreSQL

1. B-Tree (самый частый)

-- Создание B-Tree индекса (по умолчанию)
CREATE INDEX idx_users_email ON users(email);

-- Использование:
SELECT * FROM users WHERE email = 'john@example.com'; -- Быстро!
SELECT * FROM users WHERE email LIKE 'john%'; -- Быстро!
SELECT * FROM users ORDER BY email; -- Быстро!

Хорош для:

• Равенства (=)
• Диапазонов (<, >, <=, >=)
• LIKE с началом строки
• ORDER BY

2. Hash (для точного совпадения)

CREATE INDEX idx_users_id_hash ON users USING HASH(id);

-- Использование:
SELECT * FROM users WHERE id = 42; -- Очень быстро!

-- НЕ подходит для диапазонов:
SELECT * FROM users WHERE id > 10; -- Медленно, индекс не используется

Хорош для:

• Точного равенства (=)
• Не подходит для диапазонов

3. GiST (для геопространственных данных)

CREATE INDEX idx_locations_geo ON locations USING gist(location);

-- Использование для поиска по близости:
SELECT * FROM locations 
WHERE ST_Distance(location, ST_Point(0,0)) < 1000;

4. GIN (Generalized Inverted Index)

-- Для массивов и full-text search
CREATE INDEX idx_posts_tags ON posts USING gin(tags);
CREATE INDEX idx_documents_text ON documents USING gin(to_tsvector('russian', content));

-- Использование:
SELECT * FROM posts WHERE tags @> ARRAY['python']; -- Быстро!
SELECT * FROM documents WHERE to_tsvector('russian', content) @@ plainto_tsquery('python');

5. BRIN (Block Range Index)

-- Для больших отсортированных таблиц
CREATE INDEX idx_logs_date ON logs USING brin(created_at);

-- Экономит место, хорош для временных рядов
SELECT * FROM logs WHERE created_at > NOW() - INTERVAL '1 day';

Практические примеры

Пример 1: Поиск по email

from sqlalchemy import Index
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    email = Column(String(255))
    
    # Создаём индекс
    __table_args__ = (
        Index('idx_users_email', 'email'),
    )

# В SQL:
CREATE INDEX idx_users_email ON users(email);

# Запрос будет очень быстрым:
SELECT * FROM users WHERE email = 'john@example.com';

Пример 2: Составной индекс

-- Если часто ищешь по нескольким полям
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Быстро:
SELECT * FROM orders WHERE user_id = 123 AND created_at > '2024-01-01';

Пример 3: Частичный индекс

-- Индекс только для активных пользователей
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

-- Меньше места, быстрее!
SELECT * FROM users WHERE is_active = true AND email = 'john@example.com';

Пример 4: UNIQUE индекс

-- Гарантирует уникальность
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Попытка добавить дубликат вызовет ошибку
INSERT INTO users(email) VALUES ('john@example.com');
INSERT INTO users(email) VALUES ('john@example.com'); -- Ошибка!

Проблемы и оптимизация

Проблема 1: Слишком много индексов

-- ПЛОХО: индекс замедляет INSERT/UPDATE/DELETE
CREATE INDEX idx_users_name ON users(name);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_phone ON users(phone);
CREATE INDEX idx_users_address ON users(address);
-- Каждое UPDATE требует обновить 4 индекса!

Решение: используй индексы только для часто выполняемых запросов.

Проблема 2: Неправильный порядок в составном индексе

-- ПЛОХО
CREATE INDEX idx_orders_date_user ON orders(created_at, user_id);
SELECT * FROM orders WHERE user_id = 123; -- Индекс не используется!

-- ХОРОШО
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
SELECT * FROM orders WHERE user_id = 123; -- Индекс используется!

Проблема 3: Индекс не используется из-за функции

-- ПЛОХО: функция ломает индекс
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';
-- Индекс на email не помогает, нужен индекс на LOWER(email)

-- ХОРОШО
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'john@example.com'; -- Быстро!

Мониторинг индексов

-- Посмотреть все индексы
SELECT * FROM pg_indexes WHERE tablename = 'users';

-- Найти неиспользуемые индексы
SELECT schemaname, tablename, indexname
FROM pg_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY tablename;

-- Статистика использования индекса
SELECT idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexrelname = 'idx_users_email';

-- Посмотреть план запроса (EXPLAIN)
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';
-- Результат покажет, используется ли индекс

Лучшие практики

1. Индексируй колонки в WHERE

SELECT * FROM users WHERE email = 'test@example.com'; -- Индекс на email!
CREATE INDEX idx_users_email ON users(email);

2. Индексируй колонки в JOIN

SELECT * FROM orders o
JOIN users u ON o.user_id = u.id -- Индекс на user_id поможет!
WHERE u.email = 'john@example.com';

CREATE INDEX idx_orders_user_id ON orders(user_id);

3. Используй EXPLAIN для анализа

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
-- Seq Scan = нет индекса (медленно)
-- Index Scan = есть индекс (быстро)

4. Регулярно анализируй таблицы

ANALYZE users; -- Обновить статистику для оптимизатора
VACUUM users;  -- Очистить таблицу от мёртвых строк

Выводы

• Индекс ускоряет SELECT, замедляет INSERT/UPDATE/DELETE
• B-Tree — самый частый тип (для большинства случаев)
• Hash — для точного равенства
• GIN — для массивов и full-text search
• EXPLAIN — проверь, используется ли индекс
• Баланс — не создавай индекс на каждый столбец
• Порядок важен — в составном индексе порядок имеет значение