Что будешь делать, если планировщик не использует индекс при поиске записи

Question

claude-haiku-4.5 · Accepted Answer

## Отладка проблемы: Планировщик не использует индекс

### Систематический подход к решению

Это типичная и критичная для production проблема. Когда запрос выполняется полным сканированием таблицы (Seq Scan) вместо индекса, это может замедлить систему в 1000x раз. Вот как я диагностирую и решаю эту проблему.

### Шаг 1: Подтверждение проблемы с EXPLAIN

**Сначала смотрю план запроса**

```java
// Пример: ищем пользователя по email
SELECT * FROM users WHERE email = 'test@example.com';

// Диагностика:
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM users WHERE email = 'test@example.com';

// Результат может быть:
Seq Scan on users (cost=0.00..35000.00 rows=1000000)
  Filter: (email = 'test@example.com')
  Buffers: shared hit=10000 read=5000
```

**Вывод:** Полное сканирование (Seq Scan) вместо индекса (Index Scan)
```java
// Что хотим видеть:
Index Scan using idx_user_email on users (cost=0.29..8.30 rows=1)
  Index Cond: (email = 'test@example.com')
  Buffers: shared hit=4
```

### Шаг 2: Проверка, существует ли индекс

**Смотрю, какие индексы есть на таблице**

```java
// В PostgreSQL
SELECT 
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'users';

// В MySQL
SHOW INDEXES FROM users;
SHOW CREATE TABLE users;

// Возможные результаты:
// 1. Индекса нет вообще → создаём
// 2. Индекс есть, но условие не подходит → переделаем условие
// 3. Индекс есть, но статистика устарела → ANALYZE
// 4. Индекс неправильного типа → перестроим
```

### Шаг 3: Типичные причины и решения

**Причина 1: Индекса просто нет**

```java
// Была таблица
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255),
    name VARCHAR(255),
    created_at TIMESTAMP
    // Нет индекса на email!
);

// Запрос медленный
SELECT * FROM users WHERE email = 'test@example.com'; // Seq Scan

// Решение: создаём индекс
CREATE INDEX idx_user_email ON users(email);

// Проверяем
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
// Теперь: Index Scan
```

**Причина 2: Условие WHERE не может использовать индекс**

```java
// Есть индекс на email
CREATE INDEX idx_user_email ON users(email);

// Но запрос использует функцию
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
// ← Seq Scan, потому что индекс на LOWER(email)

// Решение 1: Создаём индекс на функцию
CREATE INDEX idx_user_email_lower ON users(LOWER(email));
// Теперь запрос с LOWER будет использовать индекс

// Решение 2: Нормализуем данные
ALTER TABLE users ADD COLUMN email_normalized VARCHAR(255) GENERATED ALWAYS AS (LOWER(email));
CREATE INDEX idx_user_email_norm ON users(email_normalized);

// Решение 3: Переделываем запрос (если возможно)
SELECT * FROM users WHERE email = LOWER('test@example.com');
// Все равно может быть Seq Scan, потому что LOWER меняет условие
```

**Причина 3: Тип данных в условии не совпадает**

```java
// Таблица
CREATE TABLE users (
    id BIGINT, // INTEGER
    email VARCHAR
);
CREATE INDEX idx_user_id ON users(id);

// Запрос с неправильным типом
SELECT * FROM users WHERE id = '123'; // String, не INTEGER
// ← Может быть Seq Scan, потому что нужна конвертация

// Решение: правильный тип
SELECT * FROM users WHERE id = 123; // INTEGER
// ← Теперь Index Scan

// В Java (Spring Data JPA)
@Query("SELECT u FROM User u WHERE u.id = ?1")
User findById(Long id); // ← Правильно

// Плохо
@Query("SELECT u FROM User u WHERE u.id = CAST(?1 AS TEXT)")
User findById(Long id);
```

**Причина 4: Условие WHERE использует OR**

```java
// Есть индексы на email и phone
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_phone ON users(phone);

// Запрос с OR может быть Seq Scan
SELECT * FROM users WHERE email = 'test@example.com' OR phone = '1234567890';
// ← Может быть Seq Scan

// Решение 1: Composite index (если часто ищем по обоим полям)
CREATE INDEX idx_email_phone ON users(email, phone);
// Может помочь в некоторых случаях

// Решение 2: Используем UNION вместо OR
SELECT * FROM users WHERE email = 'test@example.com'
UNION
SELECT * FROM users WHERE phone = '1234567890';
// ← Обе части используют индексы

// Решение 3: Check statistics (может быть, планировщик думает, что нужен Seq Scan)
ANALYZE users;

// Решение 4: Force index hint (если планировщик совсем неправ)
-- PostgreSQL не поддерживает INDEX HINT напрямую
-- Но можно использовать:
SET enable_seqscan = off; -- Только для этой session
SELECT * FROM users WHERE email = 'test' OR phone = '123';
SET enable_seqscan = on;
```

**Причина 5: Статистика БД устарела**

```java
// Планировщик использует статистику для выбора плана
// Если статистика устарела, может выбрать неправильный индекс

// Диагностика
EXPLAIN SELECT * FROM large_table WHERE id = 1;
// Вывод: expected rows=500000, actual rows=1
// ← Статистика неправильная!

// Решение: ANALYZE обновляет статистику
ANALYZE large_table;

// Или для конкретного столбца
ANALYZE large_table(id);

// После этого план должен быть правильный
```

### Шаг 4: Поиск неправильного плана (когда индекс есть, но не используется)

```java
// Иногда планировщик имеет устаревшие/неправильные статистические данные
// и выбирает Seq Scan вместо индекса, хотя индекс есть

// Детальный анализ
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT * FROM users WHERE email = 'test@example.com';

// Посмотрим:
// - Filter (какой фильтр применяется)
// - Rows (сколько рядов планировщик думает, что будет)
// - Actual (сколько рядов на самом деле)

// Если:
// - estimated rows: 500000
// - actual rows: 1
// → Статистика неправильная, нужна ANALYZE
```

### Шаг 5: Проверка, подходит ли индекс

```java
// Индекс может быть создан, но быть неправильного типа

// 1. B-tree индекс (default)
CREATE INDEX idx_standard ON users(email); // B-tree
// Хорош для: =, <, >, BETWEEN

// 2. Hash индекс (только = )
CREATE INDEX idx_hash ON users(email) USING hash;
// Хорош для: только точных совпадений =

// 3. BRIN индекс (для очень больших таблиц с сортировкой)
CREATE INDEX idx_brin ON events(created_at) USING brin;
// Хорош для: range queries на больших отсортированных таблицах

// 4. GiST/GIN (для сложных типов)
CREATE INDEX idx_fulltext ON documents USING gin(to_tsvector(content));
// Хорош для: полнотекстовый поиск, массивы, JSON

// Если используешь WHERE email LIKE 'test%'
// B-tree индекс будет использован (хорош)
// Но если WHERE email LIKE '%test%'
// Индекс может не помочь, нужен полнотекстовый поиск
```

### Шаг 6: В коде Java

```java
// Если проблема на уровне приложения
@Service
@AllArgsConstructor
public class UserService {
    private final UserRepository repository;
    
    // ПЛОХО: может быть Seq Scan
    public User findByEmail(String email) {
        return repository.findAll().stream()
            .filter(u -> u.getEmail().equals(email))
            .findFirst()
            .orElse(null);
    }
    
    // ХОРОШО: используем БД для фильтрации
    @Query("SELECT u FROM User u WHERE u.email = ?1")
    public User findByEmail(String email);
    
    // ЕЩЁ ЛУЧШЕ: Spring находит метод автоматически
    public User findByEmail(String email);
    // Spring Data JPA создаст запрос SELECT u FROM User u WHERE u.email = ?1
}

// А в БД проверяем индекс
CREATE INDEX idx_user_email ON users(email);
```

### Полный checklist отладки

```java
public class IndexDebugChecklist {
    public static void debugIndexIssue(String table, String column) {
        // 1. Проверить план запроса
        // EXPLAIN SELECT * FROM table WHERE column = value;
        
        // 2. Проверить наличие индекса
        // SELECT * FROM pg_indexes WHERE tablename = 'table';
        
        // 3. Обновить статистику
        // ANALYZE table;
        
        // 4. Проверить, используется ли индекс
        // EXPLAIN SELECT * FROM table WHERE column = value;
        
        // 5. Если всё ещё Seq Scan:
        //    - Проверить условие WHERE (может быть функция?)
        //    - Проверить тип данных
        //    - Проверить размер таблицы
        //    - Может быть, Seq Scan эффективнее для маленькой таблицы
        
        // 6. Создать индекс если нужно
        // CREATE INDEX idx_name ON table(column);
        
        // 7. ANALYZE снова
        // ANALYZE table;
        
        // 8. Проверить план ещё раз
        // EXPLAIN ANALYZE SELECT * FROM table WHERE column = value;
    }
}
```

### Реальный пример из production

```java
// Проблема: запрос SELECT * FROM orders WHERE user_id = 123 работает 3 секунды

// 1. EXPLAIN показал Seq Scan
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;
// Seq Scan on orders, time: 3000ms

// 2. Проверил индексы
SELECT * FROM pg_indexes WHERE tablename = 'orders';
// Результат: индекс на id, но не на user_id

// 3. Создал индекс
CREATE INDEX idx_orders_user_id ON orders(user_id);

// 4. ANALYZE
ANALYZE orders;

// 5. Проверил ещё раз
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;
// Index Scan using idx_orders_user_id, time: 50ms ← 60x быстрее!
```

### Итоговый алгоритм

```java
public class IndexOptimizationAlgorithm {
    
    /**
     * Когда планировщик не использует индекс
     */
    public void fixIndexUsage() {
        // 1. Подтвердить проблему
        explainQuery(); // EXPLAIN показывает Seq Scan?
        
        // 2. Проверить индекс
        checkIndexExists(); // Индекс есть?
        
        // 3. Обновить статистику
        analyzeTable(); // ANALYZE
        
        // 4. Проверить условие
        validateWhereClause(); // Нет ли функций? Типы совпадают?
        
        // 5. Если индекса нет
        createIndex(); // CREATE INDEX
        
        // 6. Если индекс неправильного типа
        recreateIndex(); // DROP и CREATE с правильным USING
        
        // 7. Проверить результат
        explainQueryAgain(); // Теперь Index Scan?
    }
}
```

Что будешь делать, если планировщик не использует индекс при поиске записи

Комментарии (1)

Отладка проблемы: Планировщик не использует индекс

Систематический подход к решению

Шаг 1: Подтверждение проблемы с EXPLAIN

Шаг 2: Проверка, существует ли индекс

Шаг 3: Типичные причины и решения

Шаг 4: Поиск неправильного плана (когда индекс есть, но не используется)

Шаг 5: Проверка, подходит ли индекс

Шаг 6: В коде Java

Полный checklist отладки

Реальный пример из production

Итоговый алгоритм