В чём разница между index scan и index only scan?

Index Scan vs Index Only Scan в PostgreSQL

Это два разных способа использования индексов при поиске данных в PostgreSQL. Понимание разницы критично для оптимизации performance запросов.

Index Scan

Index Scan использует индекс для поиска строк, но затем обращается к основной таблице для получения полных данных.

-- Таблица users с индексом на name
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    age INT
);

CREATE INDEX idx_users_name ON users(name);

-- Query с Index Scan
EXPLAIN ANALYZE
SELECT id, email, age FROM users WHERE name = 'Alice';

-->
-- Index Scan using idx_users_name on users (cost=0.29..4.31)
--   Index Cond: (name = 'Alice')
--   -> Heap Fetch (обращение к основной таблице)

Процесс Index Scan:

1. PostgreSQL ищет в индексе строки где name = 'Alice'
2. Находит CTID (физический адрес строки)
3. По CTID идёт в основную таблицу (Heap)
4. Получает полную строку со всеми столбцами

Когда используется:

• Нужны столбцы которые НЕ в индексе
• Нужны часто обновляемые данные

Index Only Scan

Index Only Scan находит данные полностью в индексе, не обращаясь к основной таблице.

-- Query с Index Only Scan
EXPLAIN ANALYZE
SELECT id, name FROM users WHERE name = 'Alice';

-->
-- Index Only Scan using idx_users_name on users (cost=0.29..2.15)
--   Index Cond: (name = 'Alice')
--   -> NO Heap Fetch!

Процесс Index Only Scan:

1. PostgreSQL ищет в индексе строки где name = 'Alice'
2. Находит id и name в индексе
3. Проверяет visibility (хотя ли эта версия видима в этой транзакции)
4. Возвращает результат - БЕЗ обращения в основную таблицу!

Когда используется:

• Все нужные столбцы есть в индексе
• Индекс достаточно свежий (Visibility OK)

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

-- Создаём таблицу и индекс
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50),
    name VARCHAR(200),
    price DECIMAL(10,2),
    description TEXT
);

CREATE INDEX idx_products_sku ON products(sku);

INSERT INTO products VALUES (1, 'SKU-001', 'Laptop', 999.99, '...');

-- QUERY 1: Index Only Scan (оптимально!)
EXPLAIN ANALYZE
SELECT id, sku FROM products WHERE sku = 'SKU-001';

-->
-- Index Only Scan using idx_products_sku
-- Cost: 0.29..1.80

-- QUERY 2: Index Scan (нужны дополнительные данные)
EXPLAIN ANALYZE
SELECT id, sku, name, price FROM products WHERE sku = 'SKU-001';

-->
-- Index Scan using idx_products_sku
-- Heap Fetches: 1
-- Cost: 0.29..8.31  <- Дороже!

Visibility Map (VM)

ПостгресQL использует Visibility Map для оптимизации Index Only Scan:

Визуальность строк:

Independent Scan                    Index Only Scan
┌─────────────────────┐            ┌─────────────────────┐
│ Index               │            │ Index               │
│ id -> name, CTID    │            │ id -> name, CTID    │
└──────────┬──────────┘            └──────────┬──────────┘
           │                                   │
           v                                   v
     ┌──────────┐                       ┌─────────────┐
     │ Heap Tab │                       │ Visibility  │
     │ Fetch all│                       │ Map Check   │
     │ columns  │                       │ (no heap)   │
     └──────────┘                       └─────────────┘

INCLUDE индексы (PostgreSQL 11+)

Індексы с INCLUDE позволяют добавить столбцы в индекс для Index Only Scan:

-- Обычный индекс
CREATE INDEX idx_users_email ON users(email);

-- С INCLUDE - добавляем столбцы без участия в сортировке
CREATE INDEX idx_users_email_inc ON users(email) INCLUDE (name, age);

-- Теперь это будет Index Only Scan!
SELECT email, name, age FROM users WHERE email = 'test@example.com';

-->
-- Index Only Scan using idx_users_email_inc

Сравнение производительности

-- Таблица с 1,000,000 строк
CREATE TABLE large_table AS
SELECT 
    generate_series(1, 1000000) as id,
    'name_' || (random() * 100000)::int as name,
    'email_' || generate_series(1, 1000000) || '@example.com' as email,
    random() * 100 as score,
    md5(random()::text) as big_data  -- Large column
FROM generate_series(1, 1000000);

CREATE INDEX idx_large_name ON large_table(name);

-- Index Only Scan: 2-3ms
EXPLAIN ANALYZE
SELECT id, name FROM large_table WHERE name = 'name_50000';

-- Index Scan: 5-8ms (обращение к основной таблице)
EXPLAIN ANALYZE
SELECT id, name, email, score FROM large_table WHERE name = 'name_50000';

Проблемы с Index Only Scan

-- VACUUM нужен для обновления Visibility Map
VACUUM large_table;

-- БЕЗ VACUUM индекс может не использоваться как Index Only
EXPLAIN ANALYZE
SELECT id, name FROM large_table WHERE name = 'test';  -- Может быть Index Scan!

-- После VACUUM
VACUUM large_table;
EXPLAIN ANALYZE
SELECT id, name FROM large_table WHERE name = 'test';  -- Index Only Scan

Когда Index Only Scan невозможен

-- 1. Нужны столбцы вне индекса
CREATE INDEX idx_users_name ON users(name);
SELECT email FROM users WHERE name = 'Alice';  -- Index Scan

-- 2. Функции на индексированном столбце
SELECT id, UPPER(name) FROM users WHERE UPPER(name) = 'ALICE';  -- Index Scan

-- 3. Недавно обновлённые данные (старый VM)
UPDATE users SET age = 25 WHERE id = 1;
SELECT id, name FROM users WHERE name = 'Alice';  -- Может быть Index Scan!
VACUUM;  -- Обновляет Visibility Map
SELECT id, name FROM users WHERE name = 'Alice';  -- Index Only Scan

Оптимизация: Стратегия индексов

-- ❌ Плохо: индекс не помогает Index Only Scan
CREATE INDEX idx_users_email ON users(email);
SELECT email, phone, address FROM users WHERE email = 'test@example.com';

-- ✅ Хорошо: INCLUDE индекс для Index Only Scan
CREATE INDEX idx_users_email_opt ON users(email) INCLUDE (phone, address);
SELECT email, phone, address FROM users WHERE email = 'test@example.com';

-- ✅ Или составной индекс
CREATE INDEX idx_users_multi ON users(email, phone, address);

Таблица сравнения

Резюме

• Index Scan — ищет в индексе, затем идёт в таблицу за полными данными
• Index Only Scan — всё находится в индексе, таблица не нужна
• Index Only Scan быстрее — меньше IO операций
• Используй INCLUDE индексы для Index Only Scan (PostgreSQL 11+)
• VACUUM регулярно для актуализации Visibility Map
• Профилируй EXPLAIN ANALYZE для оптимизации запросов