Подолжаю публиковать авторскую переработку Understanding EXPLAIN от Guillaume Lelarge.
Ещё раз обращу внимание, что часть информации для краткости опущено, так что настоятельно рекомендую ознакомиться с оригиналом.
Предыдущие части:
Кэш
Что происходит на физическом уровне при выполнениии нашего запроса? Разберёмся. Мой сервер поднят на Ubuntu 13.10.
Останавливаю PostgreSQL, принудительно фиксирую изменения в файловой системе, очищаю кэши, запускаю PostgreSQL:
> sudo service postgresql-9.3 stop
> sudo sync
> sudo su -
# echo 3 > /proc/sys/vm/drop_caches
# exit
> sudo service postgresql-9.3 start
Теперь кэши очищены, пробуем выполнить запрос с опцией BUFFERS
EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM foo;
QUERY PLAN
— Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.525..734.754 rows=1000010 loops=1)
Buffers: shared read=8334
Total runtime: 1253.177 ms
(3 rows)
Таблица считывается частями — блоками. Кэш пуст. Таблица полностью считывается с диска. Для этого пришлось считать 8334 блока.
Buffers: shared read
— количество блоков, считанное с диска.
Повторим последний запрос
EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM foo;
QUERY PLAN
— Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.173..693.000 rows=1000010 loops=1)
Buffers: shared hit=32 read=8302
Total runtime: 1208.433 ms
(3 rows)
Buffers: shared hit
— количество блоков, считанных из кэша PostgreSQL.
Если повторите этот запрос несколько раз, то увидите, как PostgreSQL с каждым разом всё больше данных берёт из кэша. С каждым запросом PostgreSQL наполняет свой кэш.
Операции чтения из кэша быстрее, чем операции чтения с диска. Можете заметить эту тенденцию, отслеживая значение Total runtime
.
Объём кэша определяется константой shared_buffers
в файле postgresql.conf
.
WHERE
Добавим в запрос условие
EXPLAIN SELECT * FROM foo WHERE c1 > 500;
QUERY PLAN
— Seq Scan on foo (cost=0.00..20834.12 rows=999522 width=37)
Filter: (c1 > 500)
(2 rows)
Индексов у таблицы нет. При выполнении запроса последовательно считывается каждая запись таблицы (Seq Scan
). Каждая запись сравнивается с условием c1 > 500
. Если условие выполняется, запись вводится в результат. Иначе — отбрасывается. Filter
означает именно такое поведение.
Значение cost
, что логично, увеличилось.
Ожидаемое количество строк результата — rows
— уменьшилось.
В оригинале даются объяснения, почему cost
принимает именно такое значение, а также каким образом рассчитывается ожидаемое количество строк.
Пора создать индексы.
CREATE INDEX ON foo(c1);
EXPLAIN SELECT * FROM foo WHERE c1 > 500;
QUERY PLAN
— Seq Scan on foo (cost=0.00..20834.12 rows=999519 width=37)
Filter: (c1 > 500)
(2 rows)
Ожидаемое количество строк изменилось. Уточнилось. В остальном ничего нового. Что же с индексом?
EXPLAIN (ANALYZE) SELECT * FROM foo WHERE c1 > 500;
QUERY PLAN
— Seq Scan on foo (cost=0.00..20834.12 rows=999519 width=37) (actual time=0.572..848.895 rows=999500 loops=1)
Filter: (c1 > 500)
Rows Removed by Filter: 510
Total runtime: 1330.788 ms
(4 rows)
Отфильтровано только 510 строк из более чем миллиона. Пришлось считать более 99,9% таблицы.
Принудительно заставим использовать индекс, запретив Seq Scan:
SET enable_seqscan TO off;
EXPLAIN (ANALYZE) SELECT * FROM foo WHERE c1 > 500;
QUERY PLAN
— Index Scan using foo_c1_idx on foo (cost=0.42..34623.01 rows=999519 width=37) (actual time=0.178..1018.045 rows=999500 loops=1)
Index Cond: (c1 > 500)
Total runtime: 1434.429 ms
(3 rows)
Index Scan
, Index Cond
вместо Filter
— используется индекс foo_c1_idx
.
При выборке практически всей таблицы использование индекса только увеличивает cost
и время выполнения запроса. Планировщик не глуп!
Не забываем отменить запрет на использование Seq Scan:
SET enable_seqscan TO on;
Изменим запрос:
EXPLAIN SELECT * FROM foo WHERE c1 < 500;
QUERY PLAN
— Index Scan using foo_c1_idx on foo (cost=0.42..25.78 rows=491 width=37)
Index Cond: (c1 < 500)
(2 rows)
Тут планировщик решил использовать индекс.
Усложним условие. Используем текстовое поле.
EXPLAIN SELECT * FROM foo
WHERE c1 < 500 AND c2 LIKE 'abcd%';
QUERY PLAN
— Index Scan using foo_c1_idx on foo (cost=0.42..27.00 rows=1 width=37)
Index Cond: (c1 < 500)
Filter: (c2 ~~ 'abcd%'::text)
(3 rows)
Как видим, используется индекс foo_c1_idx
для условия c1 < 500
. Для c2 ~~ 'abcd%'::text
используется фильтр.
Обратите внимание, что в выводе результатов используется POSIX фомат оператора LIKE.
Если в условии только текстовое поле:
EXPLAIN (ANALYZE)
SELECT * FROM foo WHERE c2 LIKE 'abcd%';
QUERY PLAN
— Seq Scan on foo (cost=0.00..20834.12 rows=100 width=37) (actual time=14.497..412.030 rows=10 loops=1)
Filter: (c2 ~~ 'abcd%'::text)
Rows Removed by Filter: 1000000
Total runtime: 412.120 ms
(4 rows)
Ожидаемо, Seq Scan
.
Строим индекс по c2
:
CREATE INDEX ON foo(c2);
EXPLAIN (ANALYZE) SELECT * FROM foo
WHERE c2 LIKE 'abcd%';
QUERY PLAN
— Seq Scan on foo (cost=0.00..20834.12 rows=100 width=37) (actual time=20.992..424.946 rows=10 loops=1)
Filter: (c2 ~~ 'abcd%'::text)
Rows Removed by Filter: 1000000
Total runtime: 425.039 ms
(4 rows)
Опять Seq Scan
? Индекс не используется потому, что база у меня для текстовых полей использует формат UTF-8.
При создании индекса в таких случаях надо использовать класс оператора text_pattern_ops
:
CREATE INDEX ON foo(c2 text_pattern_ops);
EXPLAIN SELECT * FROM foo WHERE c2 LIKE 'abcd%';
QUERY PLAN
— Bitmap Heap Scan on foo (cost=4.58..55.20 rows=100 width=37)
Filter: (c2 ~~ 'abcd%'::text)
-> Bitmap Index Scan on foo_c2_idx1 (cost=0.00..4.55 rows=13 width=0)
Index Cond: ((c2 ~>=~ 'abcd'::text) AND (c2 ~<~ 'abce'::text))
(4 rows)
Ура! Получилось!
Bitmap Index Scan
— используется индекс foo_c2_idx1
для определения нужных нам записей, а затем PostgreSQL лезет в саму таблицу: (Bitmap Heap Scan
) -, чтобы убедиться, что эти записи на самом деле существуют. Такое поведение связано с версионностью PostgreSQL.
Если выбирать не всю строку, а только поле, по которому постоен индекс
EXPLAIN SELECT c1 FROM foo WHERE c1 < 500;
QUERY PLAN
— Index Only Scan using foo_c1_idx on foo (cost=0.42..25.78 rows=491 width=4)
Index Cond: (c1 < 500)
(2 rows)
Index Only Scan
выполняется быстрее, чем Index Scan
за счёт того, что не требуется читать строку таблицы полностью: width=4
.
Резюме
- Seq Scan — читается вся таблица.
- Index Scan — используется индекс для условий WHERE, читает таблицу при отборе строк.
- Bitmap Index Scan — сначала Index Scan, затем контроль выборки по таблице. Эффективно для большого количества строк.
- Index Only Scan — самый быстрый. Читается только индекс.
Автор: zoroda