Какие индексы используются по умолчанию в PostgreSQL?

Индексы в PostgreSQL по умолчанию

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

1. PRIMARY KEY индекс (B-tree)

Первый и самый важный индекс — по первичному ключу. PostgreSQL автоматически создаёт B-tree индекс для PRIMARY KEY:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- Автоматически создаёт индекс
    email VARCHAR(255) UNIQUE,  -- Тоже автоматически создаёт индекс
    name VARCHAR(255)
);

-- Это эквивалентно:
CREATE INDEX idx_users_id ON users (id);

-- Быстрый поиск по id
SELECT * FROM users WHERE id = 42;  -- O(log n)

2. UNIQUE констрейнт

Уникальное ограничение также создаёт индекс автоматически:

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE,  -- Автоматический индекс
    username VARCHAR(100) UNIQUE
);

-- Быстрая проверка уникальности
INSERT INTO accounts (email, username) VALUES ('user@example.com', 'john');
-- PostgreSQL использует индексы для проверки

3. B-tree индекс (по умолчанию)

Это самый универсальный индекс. Используется по умолчанию при явном создании:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL,
    category VARCHAR(100)
);

-- Явное создание B-tree (по умолчанию)
CREATE INDEX idx_products_category ON products (category);
CREATE INDEX idx_products_price ON products (price);

-- B-tree поддерживает:
-- 1. Точный поиск
SELECT * FROM products WHERE category = 'Electronics';

-- 2. Диапазонный поиск
SELECT * FROM products WHERE price > 100 AND price < 500;

-- 3. Сортировку
SELECT * FROM products ORDER BY price;  -- Может использовать индекс

-- 4. LIKE с префиксом
SELECT * FROM products WHERE name LIKE 'Laptop%';

4. Составные индексы (Composite/Multicolumn)

Индекс на нескольких столбцах для оптимизации часто используемых фильтров:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    status VARCHAR(50),
    created_at TIMESTAMP,
    amount DECIMAL
);

-- Индекс на нескольких столбцах (порядок важен!)
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- Отлично работает для этого запроса
SELECT * FROM orders WHERE user_id = 5 AND status = 'completed';

-- Также работает для первого столбца
SELECT * FROM orders WHERE user_id = 5;

-- Но НЕ работает для status БЕЗ user_id
-- (нарушается порядок в индексе)
SELECT * FROM orders WHERE status = 'completed';  -- Медленно!

5. Hash индекс

Используется только для равенства, не универсален:

-- Hash индекс для быстрого поиска по точному совпадению
CREATE INDEX idx_users_email_hash ON users USING HASH (email);

-- Работает быстро
SELECT * FROM users WHERE email = 'user@example.com';

-- НЕ работает для диапазонов
SELECT * FROM users WHERE email LIKE 'user%';  -- Не использует индекс

-- В современном PostgreSQL B-tree обычно лучше

6. BRIN индекс (Block Range Index)

Для очень больших таблиц с отсортированными данными:

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    timestamp TIMESTAMP NOT NULL,
    user_id INT,
    event_type VARCHAR(50)
);

-- BRIN для временных серий (данные отсортированы по timestamp)
CREATE INDEX idx_events_timestamp_brin ON events USING BRIN (timestamp);

-- Намного меньше памяти чем B-tree
SELECT * FROM events WHERE timestamp > NOW() - INTERVAL '1 day';

-- Хорошо для append-only таблиц (логов, метрик)

7. GiST индекс (Generalized Search Tree)

Для пространственных данных и полнотекстового поиска:

CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    coordinates POINT
);

-- GiST для пространственных запросов
CREATE INDEX idx_locations_coords ON locations USING GIST (coordinates);

-- Поиск по расстоянию
SELECT name, coordinates <-> POINT(55.75, 37.62) AS distance
FROM locations
ORDER BY coordinates <-> POINT(55.75, 37.62)
LIMIT 10;

-- Для полнотекстового поиска
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    search_vector TSVECTOR
);

CREATE INDEX idx_articles_search ON articles USING GIST (search_vector);

SELECT * FROM articles WHERE search_vector @@ to_tsquery('python & database');

8. GIN индекс (Generalized Inverted Index)

Для массивов, JSON и полнотекстового поиска:

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    tags TEXT[],  -- Массив тегов
    metadata JSONB
);

-- GIN для поиска в массивах
CREATE INDEX idx_documents_tags ON documents USING GIN (tags);

SELECT * FROM documents WHERE tags @> ARRAY['python', 'database'];
SELECT * FROM documents WHERE 'python' = ANY(tags);

-- GIN для JSON
CREATE INDEX idx_documents_metadata ON documents USING GIN (metadata);

SELECT * FROM documents WHERE metadata @> '{"type": "article"}';
SELECT * FROM documents WHERE metadata -> 'author' = '"John"';

9. Частичные индексы (Partial Indexes)

Индекс на часть данных для оптимизации:

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

-- Индекс только для активных заказов
CREATE INDEX idx_orders_active ON orders (user_id, created_at)
WHERE status = 'active';

-- Работает отлично
SELECT * FROM orders 
WHERE user_id = 5 AND status = 'active'
ORDER BY created_at DESC;

-- Экономит место, так как не индексирует завершённые
SELECT * FROM orders WHERE status = 'completed';  -- Не использует индекс

10. Выражения в индексах (Expression Indexes)

Индекс на вычисляемое значение:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255),
    created_at TIMESTAMP
);

-- Индекс на LOWER(email) для case-insensitive поиска
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

-- Индекс на дату без времени
CREATE INDEX idx_users_created_date ON users (DATE(created_at));

SELECT * FROM users WHERE DATE(created_at) = '2024-03-22';

11. Как проверить какие индексы есть

-- Все индексы в таблице
SELECT indexname, indexdef 
FROM pg_indexes 
WHERE tablename = 'users';

-- Статистика использования индексов
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,  -- Количество сканирований
    idx_tup_read,  -- Кортежей прочитано
    idx_tup_fetch  -- Кортежей получено
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Неиспользуемые индексы (мусор)
SELECT 
    schemaname,
    tablename,
    indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

12. EXPLAIN для анализа индексов

-- Посмотреть план выполнения
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';

-- Вывод:
-- Seq Scan on users (cost=0.00..100.00 rows=1)  -- Полное сканирование
--   Filter: (email = 'john@example.com')

-- С индексом будет:
-- Index Scan using idx_users_email on users (cost=0.42..8.44 rows=1)
--   Index Cond: (email = 'john@example.com')

Рекомендации по индексам

1. PRIMARY KEY и UNIQUE — создаются автоматически
2. B-tree — универсален, используйте по умолчанию
3. Составные индексы — для часто используемых фильтров
4. Не создавайте лишние — каждый замедляет INSERT/UPDATE/DELETE
5. Анализируйте медленные запросы — EXPLAIN ANALYZE
6. Удаляйте неиспользуемые — экономьте место
7. Частичные индексы — для фильтрованных данных
8. BRIN для больших таблиц — логи, метрики
9. GIN/GiST для JSON и массивов — мощно и эффективно
10. Профилируйте индексы — pg_stat_user_indexes

Выбор индекса влияет на производительность в 1000+ раз. Профилируйте всегда!