SQL-Ex blog

Далее приведены тесты на Postgres 18.0 с настройками по умолчанию:

• enable_partition_pruning = on


• plan_cache_mode = auto

Поведение Postgres в этой области может измениться в будущем — существуют WIP‑патчи, оптимизирующие производительность.

Создадим простую секционированную таблицу с несколькими секциями:

create table events (

  event_id bigint,

  event_time timestamptz,

  event_data text

) partition by range (event_time);

-- Create 12 monthly partitions

do $$

declare

  i int;

  start_date date;

  end_date date;

begin

  for i in 0..11 loop

    start_date := '2024-01-01'::date + make_interval(months => i);

    end_date := start_date + interval '1 month';

    

    execute format(

      'create table events_%s partition of events for values from (%L) to (%L)',

      to_char(start_date, 'YYYY_MM'),

      start_date,

      end_date

    );

  end loop;

end $$;

Результат:

test=# \d+ events

                                              Partitioned table "public.events"

   Column   |           Type           | Collation | Nullable | Default | Storage  | Compression | Stats target | Description

------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------

 event_id   | bigint                   |           |          |         | plain    |             |              |

 event_time | timestamp with time zone |           |          |         | plain    |             |              |

 event_data | text                     |           |          |         | extended |             |              |

Partition key: RANGE (event_time)

Partitions: events_2024_01 FOR VALUES FROM ('2024-01-01 00:00:00+00') TO ('2024-02-01 00:00:00+00'),

            events_2024_02 FOR VALUES FROM ('2024-02-01 00:00:00+00') TO ('2024-03-01 00:00:00+00'),

            events_2024_03 FOR VALUES FROM ('2024-03-01 00:00:00+00') TO ('2024-04-01 00:00:00+00'),

            events_2024_04 FOR VALUES FROM ('2024-04-01 00:00:00+00') TO ('2024-05-01 00:00:00+00'),

            events_2024_05 FOR VALUES FROM ('2024-05-01 00:00:00+00') TO ('2024-06-01 00:00:00+00'),

            events_2024_06 FOR VALUES FROM ('2024-06-01 00:00:00+00') TO ('2024-07-01 00:00:00+00'),

            events_2024_07 FOR VALUES FROM ('2024-07-01 00:00:00+00') TO ('2024-08-01 00:00:00+00'),

            events_2024_08 FOR VALUES FROM ('2024-08-01 00:00:00+00') TO ('2024-09-01 00:00:00+00'),

            events_2024_09 FOR VALUES FROM ('2024-09-01 00:00:00+00') TO ('2024-10-01 00:00:00+00'),

            events_2024_10 FOR VALUES FROM ('2024-10-01 00:00:00+00') TO ('2024-11-01 00:00:00+00'),

            events_2024_11 FOR VALUES FROM ('2024-11-01 00:00:00+00') TO ('2024-12-01 00:00:00+00'),

            events_2024_12 FOR VALUES FROM ('2024-12-01 00:00:00+00') TO ('2025-01-01 00:00:00+00')

Теперь добавим по нескольку индексов к каждой секции и соберём статистику + построим visibility maps (без данных — для нашей цели это допустимо):

create index on events (event_id);

create index on events (event_time);

create index on events (event_data);

vacuum analyze events;

Итак, у нас 12 секций и по 3 индекса на каждую:

• 1 родительская таблица и её 3 индекса


• 12 таблиц‑секций


• 36 индексов секций (12 секций × 3 индекса)

Итого: 52 отношений, которые потенциально могут быть заблокированы.

Перед началом проверим окружение:

show plan_cache_mode;

Должно вернуться auto (по умолчанию). Это критично, потому что мы хотим наблюдать естественный переход от пользовательских планов (выполнения 1–5) к общим планам (выполнение 6+).

Примечание: мы намеренно тестируем на пустых таблицах. Поскольку мы изучаем поведение блокировок, а не производительность выполнения, наличие или отсутствие данных не влияет на то, что блокируется. Так тест остаётся сфокусированным и воспроизводимым.

Подготовим простой запрос, адресующий один месяц:

prepare get_events (timestamptz) as

select event_id, event_data

from events

where event_time = $1;

Примечание: такой запрос (event_time = $1 для конкретного момента) не вполне реалистичен — в бою используют диапазоны. Но он идеально подходит для изучения блокировок, потому что:

• надёжно срабатывает отсечение секций (partition pruning);


• пустые таблицы заставляют планировщик выбрать SeqScan и избегать сложности, связанной с блокировками индексов;


• мы можем сосредоточиться исключительно на поведении диспетчера блокировок без «шума» от выполнения запроса.

Теперь, как и в упомянутой статье, выполним prepared‑выражение несколько раз и посмотрим на блокировки:

-- Run this snippet 10 times

begin;

explain (verbose) execute get_events('2024-06-15');

select

  count(*) as lock_count,

  array_agg(

    distinct relation::regclass

    order by relation::regclass

  ) filter (where relation is not null) as relations_locked

from pg_locks

where

  pid = pg_backend_pid()

  and relation::regclass::text ~ 'events';

select

  generic_plans,

  custom_plans

from pg_prepared_statements

where name = 'get_events';

rollback;

Вот что происходит (ваши результаты могут отличаться в зависимости от версии PostgreSQL и конфигурации):

Выполнения 1–5 (custom plans):

                                     QUERY PLAN

------------------------------------------------------------------------------------

 Seq Scan on public.events_2024_06 events  (cost=0.00..0.00 rows=1 width=40)

   Output: events.event_id, events.event_data

   Filter: (events.event_time = '2024-06-15 00:00:00+00'::timestamp with time zone)

 Query Identifier: 7956826248783165125

 lock_count |                                                                                relations_locked

------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

          8 | {events,events_2024_06,events_event_id_idx,events_2024_06_event_id_idx,events_event_time_idx,events_2024_06_event_time_idx,events_event_data_idx,events_2024_06_event_data_idx}

(1 row)

 generic_plans | custom_plans

---------------+--------------

             0 |            1

Отсечение секций на этапе планирования успешно определяет, что нужна только секция events_2024_06. И всё же мы блокируем родительскую таблицу и все её индексы, плюс нужную секцию и её индексы. Всего 8 блокировок на уровне отношений (родительская таблица + 3 индекса родителя + секция + 3 индекса секции).

Выполнение 6 (построение generic‑плана):

 lock_count | relations_locked

------------+------------------

         52 | {events,events_event_id_idx,events_event_time_idx,events_event_data_idx,

               events_2024_01,events_2024_01_event_id_idx,events_2024_01_event_time_idx,events_2024_01_event_data_idx,

               events_2024_02,...[all 52 relations]...}

БУМ

Заблокированы все 52 отношения. Мы прыгнули с 8 блокировок до 52. Представьте, что будет при 1000 секций…

Выполнение 7+ (используется кэшированный generic‑план с отсечением на этапе выполнения):

Теперь происходит кое‑что интересное — число блокировок резко падает:

                                     QUERY PLAN

-------------------------------------------------------------------------------------

 Append  (cost=0.00..0.06 rows=12 width=40)

   Subplans Removed: 11

   ->  Seq Scan on public.events_2024_06 events_1  (cost=0.00..0.00 rows=1 width=40)

         Output: events_1.event_id, events_1.event_data

         Filter: (events_1.event_time = $1)

 Query Identifier: 7956826248783165125

 lock_count |                                                                                       relations_locked

------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

         13 | {events,events_2024_01,events_2024_02,events_2024_03,events_2024_04,events_2024_05,events_2024_06,events_2024_07,events_2024_08,events_2024_09,events_2024_10,events_2024_11,events_2024_12}

 generic_plans | custom_plans

---------------+--------------

             2 |            5

13 блокировок на уровне отношений — лучше, чем 52, но всё ещё проблемно. Отсечение секций на этапе выполнения работает («Subplans Removed: 11»), а мы при этом блокируем все 12 секций, хотя сканируем только одну.

Почему 13 блокировок, а не всего 2 (родитель + нужная секция)?

Исполнитель получает блокировки на все секции в InitPlan() до того, как отсечение на этапе выполнения происходит в ExecInitAppend(). Это известное ограничение: блокировки берутся слишком рано в конвейере выполнения.

На сегодня достаточно, продолжим во второй части.