BUFFERS. Варианты local-shared-temp ↔ hit-read

Чтобы посмотреть план выполнения запроса в PostgreSQL необходимо воспользоваться командой EXPLAIN. У неё есть несколько параметров. С помощью параметра ANALYZE запрос будет выполнен и мы увидим реальный план выполнения запроса. А если еще добавить параметр BUFFERS — тогда дополнительно будет показана информация об использовании буфера.

Есть несколько вариантов отображения информации о буфере. Сегодня я расскажу только часть из неё. Про written и dirtied — в другой раз!

Вывод BUFFERS может состоять из нескольких частей, я расскажу о первых двух:

Откуда данные:

• local кеш из локальной памяти обслуживающего процесса
• shared кеш из общей памяти экземпляра
• temp временные файлы на диске

Что сделали:

• hit извлечение из памяти
• read чтение с диска

Пересечения:

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

Оглавление:

• local hit
• local read
• shared hit
• shared read
• temp read

local hit

Данные находятся в кеше обслуживающего процесса, в котором создана временная таблица. Размер такого кеша под временные таблицы задаётся параметром temp_buffers:

CREATE TEMP TABLE temp_aircrafts AS SELECT * FROM aircrafts;

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM temp_aircrafts WHERE aircraft_code = '773';
                                                  QUERY PLAN                                                   
-----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=22.76..22.77 rows=1 width=8) (actual time=0.020..0.026 rows=1 loops=1)
   Buffers: local hit=1
   →  Seq Scan on temp_aircrafts  (cost=0.00..22.75 rows=5 width=0) (actual time=0.011..0.015 rows=1 loops=1)
         Filter: (aircraft_code = '773'::bpchar)
         Rows Removed by Filter: 8
         Buffers: local hit=1
 Planning Time: 0.070 ms
 Execution Time: 0.058 ms
(8 rows)

local read

Перезайдём в сеанс, затем уменьшим размер temp_buffers до одного мегабайта, создадим две временные таблицы. Заполним буфер temp_buffers данными из одной временной таблицы и потом запросим данные из другой временной таблицы. Серверу придётся «сходить» за этими данными на диск. Напомню что данные временных таблиц храняться на диске в файлах вида t_nnn (SELECT pg_relation_filepath(‘temp_aircrafts’);):

\c

SET temp_buffers = '1024 kB';
CREATE TEMP TABLE temp_aircrafts AS SELECT * FROM aircrafts;
CREATE TEMP TABLE temp_tickets AS SELECT * FROM tickets;
(два раза выполнить): SELECT count(*) FROM temp_tickets;

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM temp_aircrafts WHERE aircraft_code = '773';
                                                  QUERY PLAN                                                   
-----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=22.76..22.77 rows=1 width=8) (actual time=0.088..0.095 rows=1 loops=1)
   Buffers: local read=1 dirtied=1
   →  Seq Scan on temp_aircrafts  (cost=0.00..22.75 rows=5 width=0) (actual time=0.074..0.081 rows=1 loops=1)
         Filter: (aircraft_code = '773'::bpchar)
         Rows Removed by Filter: 8
         Buffers: local read=1 dirtied=1
 Planning:
   Buffers: shared hit=15
 Planning Time: 0.190 ms
 Execution Time: 0.138 ms
(10 rows)

shared hit

Наверное, самый быстрый доступ — чтение из общей памяти. Ранее уже обращались к этой таблице, так что данные есть в shared_buffers:

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM aircrafts WHERE aircraft_code = '773';
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1.11..1.12 rows=1 width=8) (actual time=0.018..0.023 rows=1 loops=1)
   Buffers: shared hit=1
   →  Seq Scan on aircrafts_data ml  (cost=0.00..1.11 rows=1 width=0) (actual time=0.009..0.012 rows=1 loops=1)
         Filter: (aircraft_code = '773'::bpchar)
         Rows Removed by Filter: 8
         Buffers: shared hit=1
 Planning Time: 0.049 ms
 Execution Time: 0.042 ms
(8 rows)

shared read

Перезапустим Постгрес, общая память еще не будет заполнена, так что придётся прочитать данные с диска:

sudo systemctl restart postgresql.service 
psql -d demo

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM aircrafts WHERE aircraft_code = '773';
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1.11..1.12 rows=1 width=8) (actual time=0.026..0.031 rows=1 loops=1)
   Buffers: shared read=1
   →  Seq Scan on aircrafts_data ml  (cost=0.00..1.11 rows=1 width=0) (actual time=0.017..0.020 rows=1 loops=1)
         Filter: (aircraft_code = '773'::bpchar)
         Rows Removed by Filter: 8
         Buffers: shared read=1
 Planning:
   Buffers: shared hit=47 read=22
 Planning Time: 0.365 ms
 Execution Time: 0.103 ms
(10 rows)

temp read

Возникает когда нужно поработать с временными файлами temp_file_limit. Это бывает нужно при выполнении операций сортировки и хешировании, или для сохранения удерживаемого курсора. Для примера возьмём сортировку и уменьшим размер work_mem — именно там происходит сортировка но, если размера work_mem не хватает, то будут использоваться временные файлы:

SET work_mem = '64 kB';

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM seats ORDER BY fare_conditions;
                                                 QUERY PLAN                                                  
-----------------------------------------------------------------------------------------------------------
 Sort  (cost=90.93..94.28 rows=1339 width=15) (actual time=4.552..6.715 rows=1339 loops=1)
   Sort Key: fare_conditions
   Sort Method: external merge  Disk: 40kB
   Buffers: shared hit=8, temp read=5 written=5
   ->  Seq Scan on seats  (cost=0.00..21.39 rows=1339 width=15) (actual time=0.009..2.058 rows=1339 loops=1)
         Buffers: shared hit=8
 Planning Time: 0.056 ms
 Execution Time: 8.818 ms
(8 rows)

 Разобравшись с этими пятью вариантами local-shared-temp ↔ hit-read можно будет лучше понимать то, что происходит с сервером при выполнении запросов.