SQLite EXPLAIN QUERY PLAN: проверить индекс

Категория: Postgresql · Язык: SQL · Обновлено:

EXPLAIN QUERY PLAN показывает, как SQLite собирается выполнять запрос: сканировать таблицу, искать по индексу или использовать покрывающий индекс. Это главный способ проверить, что созданный индекс действительно применяется. В отличие от EXPLAIN, который выводит байткод VDBE, QUERY PLAN даёт понятное описание.

Сравнить план без индекса и с ним

SQL
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    city TEXT NOT NULL
);

CREATE INDEX idx_users_city ON users(city);

EXPLAIN QUERY PLAN
SELECT id, email FROM users WHERE city = 'Москва';

EXPLAIN QUERY PLAN
SELECT id FROM users WHERE email = 'a@example.com';

CREATE INDEX idx_users_email ON users(email);

EXPLAIN QUERY PLAN
SELECT id FROM users WHERE email = 'a@example.com';

Первый план использует idx_users_city: SQLite ищет строки по индексу и затем читает таблицу за остальными полями. Второй план показывает SCAN users, потому что индекса по email ещё нет. После создания idx_users_email появляется SEARCH users USING COVERING INDEX idx_users_email, и таблица не читается вовсе.

SCAN не всегда плох: для маленькой таблицы или запроса, который всё равно читает большинство строк, индекс может быть медленнее. SEARCH с индексом выгоден при высокой селективности. COVERING INDEX означает, что все нужные поля есть в индексе; это уменьшает обращения к таблице.

Запускайте ANALYZE после наполнения таблицы, чтобы SQLite собрал статистику и планировщик мог выбирать индексы осознанно. PRAGMA optimize; периодически обновляет статистику. Для составных условий порядок колонок в индексе важен: индекс (city, email) не заменит индекс (email) для поиска только по email.

Не создавайте индекс на каждую колонку: каждый индекс замедляет вставку и обновление и занимает место. Проверяйте план на реальных данных и повторяйте после изменения запроса. В Python план можно получить через cursor.execute("EXPLAIN QUERY PLAN ...").fetchall().

Документация: EXPLAIN QUERY PLAN, ANALYZE.