Можно ли создать индекс по двум полям в PostgreSQL?

Можно ли создать индекс по двум полям в PostgreSQL?

Краткий ответ

Да, можно. Составные индексы (composite/multi-column indexes) по двум и более полям - это очень мощный инструмент оптимизации в PostgreSQL. Правильное использование может дать ускорение в 1000+ раз.

Создание составного индекса

Базовый синтаксис

CREATE INDEX idx_user_email_status ON users(email, status);

Это создаёт индекс по двум полям: сначала по email, потом по status.

В Java/Hibernate через migration

-- migrations/0001_create_user_index.sql
CREATE INDEX idx_users_email_status 
ON users(email, status);

Элемент Goose migration должна быть упорядочена:

-- +goose Up
CREATE INDEX idx_users_email_status ON users(email, status);

-- +goose Down
DROP INDEX IF EXISTS idx_users_email_status;

Порядок полей имеет значение

-- Индекс по (email, status)
CREATE INDEX idx_email_status ON users(email, status);

-- Эти запросы будут использовать индекс:
SELECT * FROM users WHERE email = 'user@example.com';
SELECT * FROM users WHERE email = 'user@example.com' AND status = 'active';

-- Этот запрос НЕ будет использовать индекс:
SELECT * FROM users WHERE status = 'active';

Это называется индекс prefix: индекс можно использовать, если условие начинается с первого поля.

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

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

CREATE INDEX idx_orders_user_date 
ON orders(user_id, created_at DESC);

Подходит для:

• Поиска по точному значению
• Range queries (>, <, BETWEEN)
• Сортировки

2. Hash индекс

CREATE INDEX idx_user_email_hash 
ON users USING HASH (email, status);

Подходит только для проверки на равенство (=).

3. GiST индекс

CREATE INDEX idx_location_time 
ON events USING GIST (location, created_at);

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

4. GIN индекс

CREATE INDEX idx_tags_author 
ON articles USING GIN (tags, author_id);

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

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

Пример 1: Система с пользователями и статусами

// Код Java/JPA
@Entity
@Table(name = "users", indexes = {
    @Index(name = "idx_email_status", columnList = "email,status"),
    @Index(name = "idx_created_at", columnList = "created_at DESC")
})
public class User {
    @Id
    private UUID id;
    
    @Column(nullable = false)
    private String email;
    
    @Column(nullable = false)
    private String status; // ACTIVE, INACTIVE, BANNED
    
    @Column(name = "created_at", nullable = false)
    private LocalDateTime createdAt;
}

Миграция в SQL:

-- migrations/0001_create_users.sql
CREATE TABLE users (
    id UUID PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    status VARCHAR(50) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
);

CREATE INDEX idx_email_status ON users(email, status);
CREATE INDEX idx_created_at ON users(created_at DESC);

Пример 2: E-Commerce с заказами

@Entity
@Table(name = "orders", indexes = {
    @Index(name = "idx_user_status", columnList = "user_id,status"),
    @Index(name = "idx_created_range", columnList = "created_at DESC")
})
public class Order {
    @Id
    private UUID id;
    
    @Column(name = "user_id", nullable = false)
    private UUID userId;
    
    @Column(nullable = false)
    private String status; // PENDING, COMPLETED, CANCELLED
    
    @Column(name = "created_at", nullable = false)
    private LocalDateTime createdAt;
    
    private BigDecimal totalAmount;
}

Запрос, который использует индекс:

// Быстро благодаря индексу idx_user_status
public List<Order> findActiveOrdersByUser(UUID userId) {
    return orderRepository.findByUserIdAndStatus(userId, "COMPLETED");
}

// SQL Query:
// SELECT * FROM orders WHERE user_id = ? AND status = 'COMPLETED';

Пример 3: Аналитика с временными диапазонами

-- Индекс для диапазона дат + фильтр по категории
CREATE INDEX idx_events_category_date 
ON events(category_id, created_at DESC) 
WHERE active = true;

Этот запрос будет очень быстрым:

List<Event> events = eventRepository.findByCategoryAndDateRange(
    categoryId, 
    startDate, 
    endDate
);

// SQL:
// SELECT * FROM events 
// WHERE category_id = ? 
//   AND created_at BETWEEN ? AND ? 
//   AND active = true;

Partial индексы (фильтрованные индексы)

Создают индекс только для некоторых строк:

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

Преимущества:

• Меньше занимает место на диске
• Быстрее создание и обновление
• Перфектен для разреженных данных

Использование EXPLAIN для проверки

@Repository
public interface UserRepository extends JpaRepository<User, UUID> {
    List<User> findByEmailAndStatus(String email, String status);
}

Проверка плана выполнения:

EXPLAIN ANALYZE 
SELECT * FROM users 
WHERE email = 'user@example.com' AND status = 'ACTIVE';

-- Результат:
-- Bitmap Heap Scan on users  (cost=...) 
-- ->  Bitmap Index Scan on idx_email_status  ← ХОРОШО

Если индекс не используется:

-- Seq Scan on users  ← ПЛОХО, полное сканирование таблицы

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

1. Порядок полей

-- Хорошо: equality сначала, потом range
CREATE INDEX idx ON orders(user_id, created_at);

2. Индексирование часто фильтруемых комбинаций

// Если часто пишешь такие запросы:
List<User> findByEmailAndStatus(String email, String status);
List<User> findByEmailAndCreatedAtBetween(String email, LocalDate from, LocalDate to);

// То создавай индексы под них

3. Не переусердствуй с индексами

-- Плохо: каждое поле в отдельном индексе
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_status ON users(status);
CREATE INDEX idx_created ON users(created_at);

-- Хорошо: составной индекс
CREATE INDEX idx_email_status ON users(email, status);

4. Мониторь размер индексов

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;

Вывод

Составные индексы по двум и более полям - это критически важная часть оптимизации PostgreSQL. Правильный выбор порядка полей может дать ускорение в сотни раз для частых запросов. Всегда используй EXPLAIN для проверки, работает ли индекс.