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

# Автоматическое создание индексов в PostgreSQL

PostgreSQL автоматически создаёт индексы в определённых случаях. Это важно знать для оптимизации производительности БД.

Случаи автоматического создания индексов

1 PRIMARY KEY

При создании первичного ключа PostgreSQL автоматически создает B-tree индекс.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- индекс создается автоматически
    name VARCHAR(100),
    email VARCHAR(100)
);

-- Эквивалентно:
CREATE TABLE users (
    id SERIAL,
    name VARCHAR(100),
    email VARCHAR(100),
    PRIMARY KEY (id)
);
-- на самом деле создается индекс: users_pkey

Зачем: Первичный ключ должен быть уникальным и быстро доступным. Индекс обеспечивает O(log N) поиск.

2 UNIQUE constraint

Для уникального ограничения создается индекс (обычно B-tree).

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE,  -- индекс создается для UNIQUE
    username VARCHAR(50) UNIQUE
);

-- PostgreSQL создает индексы:
-- users_email_key
-- users_username_key

Почему: Чтобы быстро проверять уникальность при INSERT/UPDATE.

3 FOREIGN KEY (частично)

Для внешнего ключа индекс НЕ создается автоматически, но это оптимизация.

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id)  -- индекс НЕ создается!
);

-- ПЛОХО: поиск по user_id будет медленным

-- ХОРОШО: создай индекс вручную
CREATE INDEX idx_orders_user_id ON orders(user_id);

Почему не автоматически: В некоторых БД разработчик может не хотеть индекс.

Подробный пример

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,              -- 📍 индекс users_pkey
    email VARCHAR(100) UNIQUE NOT NULL,    -- 📍 индекс users_email_key
    username VARCHAR(50) UNIQUE NOT NULL,  -- 📍 индекс users_username_key
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,               -- 📍 индекс posts_pkey
    user_id BIGINT REFERENCES users(id),   -- ⚠️ NO индекс (нужен вручную)
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Проверяем созданные индексы
\d users
-- Indexes:
--   "users_pkey" PRIMARY KEY, btree (id)
--   "users_email_key" UNIQUE, btree (email)
--   "users_username_key" UNIQUE, btree (username)

\d posts
-- Indexes:
--   "posts_pkey" PRIMARY KEY, btree (id)
--   WARNING: нет индекса на user_id!

Проблема: отсутствие индекса на FOREIGN KEY

-- Запрос будет МЕДЛЕННЫМ без индекса на user_id
SELECT * FROM posts WHERE user_id = 123;
-- Seq Scan on posts, Filter: (user_id = 123)
-- Time: 2500ms (плохо)

-- Создаем индекс
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- Теперь быстро
SELECT * FROM posts WHERE user_id = 123;
-- Index Scan using idx_posts_user_id on posts
-- Time: 5ms (хорошо)

Типы автоматических индексов

Primary Key — B-tree

CREATE TABLE table_name (
    id SERIAL PRIMARY KEY  -- B-tree по умолчанию
);

-- Явная схема
CREATE TABLE table_name (
    id SERIAL PRIMARY KEY USING BTREE
);

Unique — B-tree

CREATE TABLE table_name (
    email VARCHAR(100) UNIQUE  -- B-tree по умолчанию
);

Какие индексы НЕ создаются автоматически

1 Обычные колонки (без ограничений)

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),  -- НЕТ индекса
    age INT,            -- НЕТ индекса
    email VARCHAR(100) UNIQUE  -- ЕСТЬ индекс
);

-- Если часто ищешь по name, создай вручную
CREATE INDEX idx_users_name ON users(name);

2 Foreign Keys

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id)  -- НЕТ индекса
);

-- Создай вручную
CREATE INDEX idx_orders_user_id ON orders(user_id);

3 Условные индексы (Partial Indexes)

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

Когда создавать индексы вручную

Паттерн 1: Частые поиски по колонке

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    category_id INT,
    name VARCHAR(255),
    price DECIMAL(10, 2)
);

-- Часто ищешь товары по категории
SELECT * FROM products WHERE category_id = 5;

-- Создай индекс
CREATE INDEX idx_products_category ON products(category_id);

Паттерн 2: Compound index (индекс на несколько колонок)

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

-- Часто ищешь по (user_id, created_at)
SELECT * FROM orders WHERE user_id = 123 AND created_at > '2024-01-01';

-- Создай составной индекс
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

Паттерн 3: Индекс на Foreign Key

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id)  -- индекс не создается
);

-- Чтобы быстро найти все посты пользователя
CREATE INDEX idx_posts_user_id ON posts(user_id);

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

-- Все индексы в таблице
\d table_name

-- Или через запрос
SELECT indexname, indexdef 
FROM pg_indexes 
WHERE tablename = 'users';

-- Результат:
--  indexname    |  indexdef
-- ---------------+--------
--  users_pkey   | CREATE UNIQUE INDEX users_pkey ON public.users USING btree (id)
--  users_email_key | CREATE UNIQUE INDEX users_email_key ON public.users USING btree (email)

-- Размер индекса
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Пример миграции (Goose)

-- Файл: 0001_create_users_table.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,  -- индекс создается автоматически
    email VARCHAR(100) UNIQUE NOT NULL,  -- индекс создается автоматически
    username VARCHAR(50) UNIQUE NOT NULL,  -- индекс создается автоматически
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Файл: 0002_create_posts_table.sql
CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,  -- индекс автоматически
    user_id BIGINT NOT NULL REFERENCES users(id),  -- индекс НЕ автоматически
    title VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Добавляем индекс вручную
CREATE INDEX idx_posts_user_id ON posts(user_id);

Ответ на вопрос

В PostgreSQL индексы создаются по умолчанию в двух случаях:

1. PRIMARY KEY — автоматически создается B-tree индекс
2. UNIQUE constraint — автоматически создается B-tree индекс

Не создаются по умолчанию:

• FOREIGN KEY (нужно создавать вручную)
• Обычные колонки (нужны вручную)

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

✅ Правильно:

-- Таблица с основными ограничениями
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,  -- индекс есть
    email VARCHAR(100) UNIQUE,  -- индекс есть
    user_id BIGINT REFERENCES orders(id)  -- индекс нужен вручную
);

-- Добавляем нужные индексы
CREATE INDEX idx_users_user_id ON users(user_id);
CREATE INDEX idx_users_created_at ON users(created_at);  -- если часто ищешь

❌ Неправильно:

-- Забыли индекс на foreign key
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id)
);
-- SELECT * FROM posts WHERE user_id = ? будет медленно