Оптимизация запросов. Основы EXPLAIN в PostgreSQL (часть 2)

Подолжаю публиковать авторскую переработку Understanding EXPLAIN от Guillaume Lelarge.
Ещё раз обращу внимание, что часть информации для краткости опущено, так что настоятельно рекомендую ознакомиться с оригиналом.

Предыдущие части:

Часть 1

Кэш

Что происходит на физическом уровне при выполнениии нашего запроса? Разберёмся. Мой сервер поднят на 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 — самый быстрый. Читается только индекс.

Часть 3