В каких случаях PostgreSQL создает индекс по дефолту

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

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

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

1. Primary Key (Первичный ключ)

При создании первичного ключа PostgreSQL автоматически создаёт уникальный индекс (B-tree) для него.

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

# Django
from django.db import models

class User(models.Model):
    id = models.AutoField(primary_key=True)  # Индекс автоматический
    email = models.EmailField()

Проверить индекс:

# SQL
\d users  -- В psql
# или
SELECT * FROM pg_indexes WHERE tablename = 'users';

2. Unique Constraint (Уникальное ограничение)

Для обеспечения уникальности PostgreSQL создаёт уникальный B-tree индекс.

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

# Django
from django.db import models

class User(models.Model):
    email = models.EmailField(unique=True)  # Индекс автоматический

3. Foreign Key (Внешний ключ)

Важный момент: PostgreSQL НЕ создаёт индекс автоматически на столбец внешнего ключа. Это отличает его от MySQL (которая иногда создаёт). Однако это рекомендуется делать вручную для оптимизации JOIN операций.

# SQL — индекс НЕ создаётся автоматически
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id)  -- ИНДЕКСА НЕТ!
);

# Django — также не создаёт индекс
from django.db import models

class Post(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    # Индекс НЕ создаётся автоматически!

# Рекомендуется добавить индекс вручную
class Post(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE, db_index=True)

Проверка наличия индекса:

# SQL
SELECT * FROM pg_indexes WHERE tablename = 'posts' AND indexname LIKE '%user%';

4. Выражения в ограничениях (Constraint expressions)

При использовании функций в UNIQUE или CHECK ограничениях, PostgreSQL создаёт индекс на выражение.

# SQL
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100),
    UNIQUE (LOWER(email))  -- Индекс создаётся на выражение LOWER(email)
);

Случаи, когда индексы НЕ создаются автоматически

Обычные столбцы в WHERE условиях

Постгрес НЕ создаёт индекс просто потому что вы часто фильтруете по этому столбцу.

# SQL — индекс НЕ создаётся
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20)  -- Нет индекса!
);

# Если часто делаете:
SELECT * FROM orders WHERE status = 'completed';

# Нужно создать индекс вручную
CREATE INDEX idx_orders_status ON orders(status);

# Django
class Order(models.Model):
    status = models.CharField(max_length=20, db_index=True)  # Индекс вручную

Столбцы в JOIN условиях

# SQL — индекс НЕ создаётся
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id)  # ИНДЕКСА НЕТ!
);

# Частый JOIN
SELECT * FROM posts p
JOIN users u ON p.user_id = u.id;

# Нужен индекс вручную
CREATE INDEX idx_posts_user_id ON posts(user_id);

Столбцы в ORDER BY и GROUP BY

# SQL — индекс НЕ создаётся
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP  -- Нет индекса!
);

# Если часто сортируете
SELECT * FROM products ORDER BY created_at DESC LIMIT 10;

# Нужен индекс
CREATE INDEX idx_products_created_at ON products(created_at DESC);

Полный пример миграции

# Django migration
from django.db import migrations, models

class Migration(migrations.Migration):
    initial = True
    
    dependencies = []
    
    operations = [
        # Автоматические индексы
        migrations.CreateModel(
            name='User',
            fields=[
                ('id', models.AutoField(primary_key=True)),  # Индекс автоматический
                ('email', models.EmailField(unique=True)),  # Индекс автоматический
                ('username', models.CharField(max_length=100)),  # Нет индекса!
            ],
        ),
        
        migrations.CreateModel(
            name='Post',
            fields=[
                ('id', models.AutoField(primary_key=True)),  # Индекс автоматический
                ('user', models.ForeignKey(on_delete=models.CASCADE, to='user')),  # Нет индекса!
                ('status', models.CharField(max_length=20)),  # Нет индекса!
                ('created_at', models.DateTimeField(auto_now_add=True)),  # Нет индекса!
            ],
        ),
        
        # Добавляем индексы вручную
        migrations.RunSQL(
            "CREATE INDEX idx_user_username ON user(LOWER(username));",
            reverse_sql="DROP INDEX idx_user_username;"
        ),
        
        migrations.RunSQL(
            "CREATE INDEX idx_post_user_id ON post(user_id);",
            reverse_sql="DROP INDEX idx_post_user_id;"
        ),
        
        migrations.RunSQL(
            "CREATE INDEX idx_post_status ON post(status);",
            reverse_sql="DROP INDEX idx_post_status;"
        ),
        
        migrations.RunSQL(
            "CREATE INDEX idx_post_created_at ON post(created_at DESC);",
            reverse_sql="DROP INDEX idx_post_created_at;"
        ),
    ]

Проверка существующих индексов

# SQL запрос для проверки всех индексов
SELECT 
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY tablename, indexname;

# Проверка неиспользуемых индексов
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY tablename;

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

1. Всегда добавляй индекс на внешний ключ

class Post(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE, db_index=True)

2. Индексируй столбцы, используемые в WHERE

class Product(models.Model):
    status = models.CharField(max_length=20, db_index=True)
    category = models.ForeignKey(Category, on_delete=models.CASCADE, db_index=True)

3. Используй composite индексы для часто используемых комбинаций

# SQL
CREATE INDEX idx_posts_user_status ON posts(user_id, status);

# Django
from django.db import models

class Post(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    status = models.CharField(max_length=20)
    
    class Meta:
        indexes = [
            models.Index(fields=['user', 'status'], name='idx_posts_user_status'),
        ]

4. Регулярно проверяй и удаляй неиспользуемые индексы

# Удаление неиспользуемого индекса
DROP INDEX idx_old_index;

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