Анализ запросов

PostgreSQL создает план выполнения для каждого запроса. Поскольку результирующий план имеет решающее значение для производительности, в PostgreSQL имеется планировщик, выбирающий оптимальный вариант на основе оценки стоимости каждой из альтернатив. Вы можете использовать команду EXPLAIN, чтобы увидеть, какой план создает планировщик для запроса.

Создадим таблицу для последующих тестовых примеров:

CREATE TABLE test_table (field1 integer, field2 text);

Добавим данные в таблицу и выполним команду ANALYZE, чтобы создать статистику таблицы после добавления данных:

INSERT INTO test_table
  SELECT i, md5(random()::text)
  FROM generate_series(1, 1000000) AS i;
ANALYZE test_table;

Вызовем команду EXPLAIN, чтобы посмотреть план запроса:

EXPLAIN SELECT * FROM test_table;

Результат:

 QUERY PLAN
---------------------------------------------------------------------
Seq Scan on test_table (cost=0.00..18334.00 rows=1000000 width=37)

PostgreSQL может считывать данные из таблицы несколькими способами. Seq Scan означает, что PostgreSQL считывает данные последовательно, блок за блоком.

Параметр cost содержит два значения: предполагаемые начальные и общие затраты на исполнение запроса. Начальные затраты имеют значение 0.00. Это затраты до начала вывода, например, затраты на сортировку в операции сортировки. Предполагаемые общие затраты (18334.00) показывают затраты, необходимые для извлечения всех строк. Параметр cost не учитывает затраты на отправку результатов клиенту, поскольку планировщик не может на них повлиять. По умолчанию, за единицу измерения cost принимается стоимость последовательного доступа к странице seq_page_cost. Величина cost чаще всего говорит о потреблении ресурсов, а не о затраченном времени, хотя эти величины коррелируют между собой. Обратитесь к статье Planner Cost Constants за дополнительными сведениями.

Параметр rows показывает предполагаемое количество строк, которое возвращает Seq Scan.

Параметр width представляет собой предполагаемый средний размер выходной строки в байтах.

Когда вы вызываете EXPLAIN, PostgreSQL не выполняет запрос, вместо этого он создает предполагаемый план выполнения на основе доступной статистики. Фактический план может отличаться. Используйте параметр ANALYZE, чтобы выполнить запрос и получить актуальную статистику исполнения.

EXPLAIN (ANALYZE) SELECT * FROM test_table;

Результат:

                      QUERY PLAN
------------------------------------------------------------------
Seq Scan on test_table
    (cost=0.00..18334.00 rows=1000000 width=37)
    (actual time=0.005..62.035 rows=1000000 loops=1)
Planning Time: 0.056 ms
Execution Time: 96.757 ms

Параметр actual time — время, затраченное на получение первой строки и всех строк, в миллисекундах.

Параметр rows — фактическое количество строк, которое возвращает Seq Scan.

Параметр loops показывает, сколько раз PostgreSQL полностью отсканировал таблицу, т.е. выполнил Seq Scan над всеми данными таблицы.

Execution Time — общее время выполнения запроса.

Команда EXPLAIN ANALYZE выполняет запросы. Если вам нужно получить статистику по модифицирующим запросам INSERT, DELETE или UPDATE и не изменять данные, используйте команды BEGIN и ROLLBACK, чтобы выполнить EXPLAIN ANALYZE внутри блока транзакции с последующим откатом:

BEGIN;

EXPLAIN (ANALYZE) INSERT INTO test_table
  SELECT i, md5(random()::text)
  FROM generate_series(1, 100) AS i;

ROLLBACK;

Использование кеша

Команда EXPLAIN позволяет получить информацию об использовании кеша. Для этой цели нужно указать в запросе параметр BUFFERS.

Чтобы получить корректные результаты, перезапустите ADPG для очистки буферного кеша перед тестированием BUFFERS. Для этого можно использовать действие Reconfigure & Restart.

Выполним команду EXPLAIN с параметрами ANALYZE и BUFFERS:

EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM test_table;

Результат:

                      QUERY PLAN
------------------------------------------------------------------
Seq Scan on test_table
    (cost=0.00..18334.00 rows=1000000 width=37)
    (actual time=1.400..626.291 rows=1000000 loops=1)
   Buffers: shared read=8334
Planning:
   Buffers: shared hit=47 read=14
Planning Time: 35.535 ms
Execution Time: 663.332 ms

PostgreSQL читает таблицу частями, называемыми блоками. Параметр Buffers: shared read показывает количество блоков, считанных с диска. Таблица полностью считана с диска, считано 8334 блока, так как кеш пуст.

Выполним этот же запрос:

EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM test_table;

Результат:

                      QUERY PLAN
-------------------------------------------------------------------
Seq Scan on test_table
    (cost=0.00..18334.00 rows=1000000 width=37)
    (actual time=0.037..73.519 rows=1000000 loops=1)
  Buffers: shared hit=32 read=8302
Planning Time: 0.033 ms
Execution Time: 108.673 ms

PostgreSQL добавил к результату параметр Buffers: shared hit. Buffers: shared hit — это количество блоков, считанных из кеша PostgreSQL. Если вы повторите этот запрос несколько раз, то увидите, что PostgreSQL с каждым новым выполнением будет брать все больше и больше данных из кеша. PostgreSQL также добавляет данные в кеш при каждом запросе. Так как чтение из кеша быстрее, чем с диска, значение Buffers: shared hit увеличивается, а Execution Time сокращается при каждом выполнении запроса.

Конфигурационный параметр shared_buffers определяет размер кеша. Вы можете изменить его через ADCM. За дополнительной информацией обратитесь к статье Конфигурационные параметры.

Использование индексов

Добавим в запрос выражение WHERE:

EXPLAIN SELECT * FROM test_table WHERE field1 > 750;

Результат:

                             QUERY PLAN
---------------------------------------------------------------------
Seq Scan on test_table (cost=0.00..20834.00 rows=999288 width=37)
  Filter: (field1 > 750)

Таблица не имеет индексов, и PostgreSQL считывает каждую запись последовательно (Seq Scan).

Filter: (field1 > 750) означает, что к каждой записи применяется условие фильтрации field1 > 750. Если значение field1 отвечает условию, PostgreSQL добавляет соответствующую строку в результат.

Значение cost увеличилось. Предполагаемое количество строк в результате запроса (rows) уменьшилось из-за выражения WHERE.

Создадим индекс для field1 и выполним тот же запрос:

CREATE INDEX ON test_table(field1);
EXPLAIN (ANALYZE) SELECT * FROM test_table WHERE field1 > 750;

Результат:

                             QUERY PLAN
---------------------------------------------------------------------
 Seq Scan on test_table
    (cost=0.00..20834.00 rows=999279 width=37)
    (actual time=0.132..89.711 rows=999250 loops=1)
   Filter: (field1 > 750)
   Rows Removed by Filter: 750
Planning Time: 0.083 ms
Execution Time: 121.256 ms

Было отфильтровано только 750 строк, запрос вернул более 99.9% таблицы. Несмотря на созданный индекс, планировщик выбрал Seq Scan.

С помощью опции enable_seqscan запретим Seq Scan. Планировщик запросов будет вынужден использовать индекс.

SET enable_seqscan TO off;
EXPLAIN (ANALYZE) SELECT * FROM test_table WHERE field1 > 750;

Результат:

                             QUERY PLAN
---------------------------------------------------------------------
Index Scan using test_table_field1_idx on test_table
    (cost=0.42..36794.81 rows=999279 width=37)
    (actual time=0.054..162.518 rows=999250 loops=1)
   Index Cond: (field1 > 750)
Planning Time: 0.087 ms
Execution Time: 195.522 ms

Index Scan показывает, что PostgreSQL использует индекс. Index Cond содержит условие для поиска строк.

PostgreSQL использовал индекс, но это увеличило время выполнения. В предыдущем примере планировщик выбрал лучший план без индекса.

Включим опцию enable_seqscan и изменим запрос следующим образом:

SET enable_seqscan TO on;
EXPLAIN (ANALYZE) SELECT * FROM test_table WHERE field1 < 750;

Результат:

                             QUERY PLAN
---------------------------------------------------------------------
Index Scan using test_table_field1_idx on test_table
    (cost=0.42..31.02 rows=720 width=37)
    (actual time=0.031..0.148 rows=749 loops=1)
   Index Cond: (field1 < 750)
Planning Time: 0.074 ms
Execution Time: 0.182 ms

Наилучший план использует индекс test_table_field1_idx.

Добавим индекс для field2. Поскольку наша база данных хранит текстовые значения в кодировке UTF8, создадим индекс с помощью класса операторов text_pattern_ops.

CREATE INDEX ON test_table(field2 text_pattern_ops);

Добавим в запрос условие для текстового поля:

EXPLAIN (ANALYZE) SELECT * FROM test_table WHERE field2 LIKE 'a%';

Результат:

                             QUERY PLAN
---------------------------------------------------------------------
Bitmap Heap Scan on test_table
        (cost=2711.24..11912.67 rows=70714 width=37)
        (actual time=7.672..25.626 rows=62382 loops=1)
   Filter: (field2 ~~ 'a%'::text)
   Heap Blocks: exact=8332
   ->  Bitmap Index Scan on test_table_field2_idx1
        (cost=0.00..2693.57 rows=69314 width=0)
        (actual time=6.343..6.344 rows=62382 loops=1)
         Index Cond: ((field2 ~>=~ 'a'::text) AND (field2 ~<~ 'b'::text))
Planning Time: 0.097 ms
Execution Time: 28.520 ms

В выводе команды EXPLAIN знак -> и отступы отражают иерархическую структуру дерева плана.

Bitmap Index Scan собирает битовую карту с позициями записей, соответствующих условию. Далее PostgreSQL в рамках оператора Bitmap Heap Scan последовательно сканирует целевую таблицу по этим позициям.

Bitmap Heap Scan эффективно работает с большим количеством строк, попадающих под условие.

PostgreSQL также поддерживает операцию Index Only Scan, которая используется, если запрос ссылается только на индексированные столбцы. Выполним запрос, который возвращает одно индексированное поле:

EXPLAIN SELECT field1 FROM test_table WHERE field1 < 750;

Результат:

                             QUERY PLAN
---------------------------------------------------------------------
Index Only Scan using test_table_field1_idx on test_table
   (cost=0.42..29.69 rows=758 width=4)
  Index Cond: (field1 < 750)

Index Only Scan работает быстрее, чем Index Scan, так как Index Only Scan не требует чтения всей строки (width=4), за исключением случая проверки видимости индексной записи.

Индексы повышают производительность различных операций, включая сортировку. Рассмотрим выражение ORDER BY.

Удалим индекс для field1:

DROP INDEX test_table_field1_idx;

Отсортируем таблицу по field1:

EXPLAIN (ANALYZE) SELECT * FROM test_table ORDER BY field1;

Результат:

                             QUERY PLAN
---------------------------------------------------------------------
Gather Merge  (cost=63795.04..161033.70 rows=833416 width=37)
              (actual time=94.311..273.651 rows=1000000 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Sort  (cost=62795.02..63836.79 rows=416708 width=37)
            (actual time=91103..127.215 rows=333367 loops=3)
        Sort Key: field1
        Sort Method: external merge  Disk: 13568kB
        Worker 0:  Sort Method: external merge  Disk: 12040kB
        Worker 1:  Sort Method: external merge  Disk: 20504kB
        ->  Parallel Seq Scan on test_table
            (cost=0.00..12502.08 rows=416708 width=37)
            (actual time=0.008..29.574 rows=333367 loops=3)
Planning Time: 0.099 ms
Execution Time: 316.971 ms

Когда планировщик определяет, что параллельный запрос является самой быстрой стратегией выполнения, он создает план, который включает ноду Gather или Gather Merge (в нашем случае Gather Merge). Эта нода содержит поддерево плана выполнения, который обрабатывается параллельно несколькими процессами.

Первая операция — Parallel Seq Scan с таблицей test_table. Разные последовательные блоки таблицы параллельно сканируются процессами Worker 0 и Worker 1.

После этого PostgreSQL сортирует результаты. Sort Key содержит условие сортировки field1. Метод сортировки external merge Disk означает, что система создает на диске временные файлы.

План выполнения запроса, приведенный выше, содержит нескольких операций и выглядит ресурсозатратным.

Создадим индекс для field1 и выполним тот же запрос:

CREATE INDEX ON test_table(field1);
EXPLAIN (ANALYZE) SELECT * FROM test_table ORDER BY field1;

Результат:

                             QUERY PLAN
---------------------------------------------------------------------
Index Scan using test_table_field1_idx on test_table
    (cost=0.42..34319.93 rows=1000100 width=37)
    (actual time=0.018..106.251 rows=1000100 loops=1)
Planning Time: 0.140 ms
Execution Time: 148.662 ms

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

Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней