SQL-Ex blog

Как мы обсуждали в #PostgresMarathon 2-002, для простого SELECT из таблицы на этапе планирования Postgres блокирует таблицу и все её индексы с помощью AccessShareLock. Небольшая демонстрация, чтобы напомнить (позволю себе немного «сэкономить байты» при наборе SQL):

test=# create table t();

CREATE TABLE

test=# create index on t((1));

CREATE INDEX

test=# create index on t((1));

CREATE INDEX

test=# create index on t((1));

CREATE INDEX

test=# create index on t((1));

CREATE INDEX

test=# create index on t((1));

CREATE INDEX

test=# \d t

                Table "public.t"

 Column | Type | Collation | Nullable | Default

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

Indexes:

    "t_expr_idx" btree ((1))

    "t_expr_idx1" btree ((1))

    "t_expr_idx2" btree ((1))

    "t_expr_idx3" btree ((1))

    "t_expr_idx4" btree ((1))

test=#

test=# begin; explain select from t;

BEGIN

                     QUERY PLAN

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

 Seq Scan on t  (cost=0.00..39.10 rows=2910 width=0)

(1 row)

test=*# select relation::regclass, mode from pg_locks where pid = pg_backend_pid();

  relation   |      mode

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

 t_expr_idx2 | AccessShareLock

 pg_locks    | AccessShareLock

 t_expr_idx3 | AccessShareLock

 t_expr_idx4 | AccessShareLock

 t_expr_idx  | AccessShareLock

 t_expr_idx1 | AccessShareLock

 t           | AccessShareLock

             | ExclusiveLock

(8 rows)

test=*#

— действительно, все индексы заблокированы.

Использование подготовленных операторов для сокращения блокировок

Чтобы смягчить это, можно просто использовать подготовленные операторы (prepared statements). Создадим один:

prepare test_query (int) as select from t;

А затем выполним следующий фрагмент 7 раз:

begin;

explain (verbose) execute test_query(1);

select relation::regclass, mode

from pg_locks

where pid = pg_backend_pid() and relation::regclass <> 'pg_locks'::regclass;

rollback;

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

  relation   |      mode

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

 t_expr_idx2 | AccessShareLock

 t_expr_idx3 | AccessShareLock

 t_expr_idx4 | AccessShareLock

 t_expr_idx  | AccessShareLock

 t_expr_idx1 | AccessShareLock

 t           | AccessShareLock

(6 rows)

А на седьмой раз увидим такое:

 relation |      mode

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

 t        | AccessShareLock

(1 row)

— заблокирована только таблица.

Загадка шестого выполнения

Меня удивило, что единственная блокировка появилась при 7‑м вызове, а не при 6‑м. Я ожидал, что первые 5 раз будет использоваться так называемый «пользовательский план» (custom plan), и все 6 объектов (таблица + 5 индексов) будут заблокированы, а на 6‑м вызове произойдёт переключение на «обобщённый» план (generic plan), и блокироваться будет только сама таблица. Почему 5 вызовов? Из документации:

By default (that is, when plan_cache_mode is set to auto), the server will automatically choose whether to use a generic or custom plan for a prepared statement that has parameters. The current rule for this is that the first five executions are done with custom plans and the average estimated cost of those plans is calculated. Then a generic plan is created and its estimated cost is compared to the average custom-plan cost. Subsequent executions use the generic plan if its cost is not so much higher than the average custom-plan cost as to make repeated replanning seem preferable.

Но почему изменение характера блокировок мы видим только на 7‑м вызове, а не на 6‑м?

Проверим статистику подготовленных операторов:

test=# select * from pg_prepared_statements \gx

-[ RECORD 1 ]---+-------------------------------------------

name            | test_query

statement       | prepare test_query (int) as select from t;

prepare_time    | 2025-10-15 02:06:25.570003+00

parameter_types | {integer}

result_types    | {}

from_sql        | t

generic_plans   | 2

custom_plans    | 5

— действительно, сначала использовался пользовательский план 5 раз, затем произошёл переход на обобщённый план, который применился на 6‑м и 7‑м вызовах. Но почему при 6‑м вызове мы всё ещё видели блокировки всех индексов? Вот в чём загадка.

Итак, почему 6‑е выполнение использует обобщённый план, но всё равно блокирует все индексы?

Ответ в двух разных механизмах блокировок в кэше планов PostgreSQL:

1. Planner locks — блокируют всё, что есть в дереве запроса (Query tree, все потенциальные пути доступа).


2. Executor locks — блокируют только то, что есть в PlannedStmt (что действительно будет использоваться).

Посмотрим исходники (использую код PG18).

Первые пять

Как уже было сказано, для первых 5 выполнений используются пользовательские планы. Внутри GetCachedPlan видим такую последовательность:

1. Получение блокировок планировщика:
   

   AcquirePlannerLocks(plansource->query_list, true);

   
   
   
   
   • Блокирует дерево запроса (результат парсинга).
   
   
   • Дерево запроса в нашем случае содержит: таблицу + все 5 индексов.
   
   
   • Итог: блокируются таблица и её 5 индексов — всего 6 блокировок.
   
   
   
   


2. Выбор типа плана:
   

   /* Generate custom plans until we have done at least 5 (arbitrary) */
   
   if (plansource->num_custom_plans < 5)
   
       return true;  // Use custom plan

   
   


3. Построение пользовательского плана:
   

   plan = BuildCachedPlan(plansource, qlist, boundParams, queryEnv);
   
                                           // ^^^^^^^^^^^ фактическое значение параметра

   
   
   
   
   • Планировщик строит план с конкретным значением параметра.
   
   
   • План не кэшируется (удаляется после выполнения).
   
   
   • Результат: 6 блокировок планировщика остаются до конца транзакции.
   
   
   
   

Номер шесть

На 6‑м выполнении начало такое же, как у первых пяти. Всё выглядит одинаково, кроме одного — этапа построения и использования кэшированного плана.

1. Получение блокировок планировщика — как и прежде (см. выше).


2. Выбор типа плана — теперь иначе: при стандартном plan_cache_mode (auto) происходит переключение на обобщённый план:
   

   /*
   
    * Prefer generic plan if it's less expensive than the average custom
   
    * plan.  (Because we include a charge for cost of planning in the
   
    * custom-plan costs, this means the generic plan only has to be less
   
    * expensive than the execution cost plus replan cost of the custom
   
    * plans.)
   
    *
   
    * Note that if generic_cost is -1 (indicating we've not yet determined
   
    * the generic plan cost), we'll always prefer generic at this point.
   
    */
   
   if (plansource->generic_cost < avg_custom_cost)
   
     return false;  // Use generic plan

   
   
   
   
   • Теперь num_custom_plans = 5, порог достигнут.
   
   
   • generic_cost = -1 (ещё не посчитана).
   
   
   • -1 < avg_custom_cost — значит, пытаемся строить обобщённый план.
   
   
   
   


3. Проверка наличия кэшированного плана:
   

   if (CheckCachedPlan(plansource))  // Возвращает FALSE — плана ещё нет

   
   


4. Построение обобщённого плана:
   

   plan = BuildCachedPlan(plansource, qlist, NULL, queryEnv);
   
                                             // ^^^^ NULL означает 'generic'
   
   plansource->gplan = plan;  // Кэшируем

   
   
   
   
   • Планировщик строит план без значений параметров.
   
   
   • Создаётся PlannedStmt, который в нашем случае (выбран последовательный обход) содержит только таблицу.
   
   
   • План кэшируется для последующего использования.
   
   
   • Результат: всё ещё 6 блокировок планировщика (с шага 1) — блокировки исполнителя ещё не получены (!!).
   
   
   
   

Вот что я неправильно понял, читая только документацию: на 6‑м выполнении мы строим обобщённый план, но ещё не проходим по пути «использовать кэшированный план», который получил бы блокировки исполнителя. Мы всё ещё используем блокировки планировщика — то есть, блокируем все индексы.

Наконец, «готово» — седьмое и далее

Начиная с 7‑го выполнения и дальше, используется кэшированный обобщённый план и блокировки исполнителя — в нашем случае (последовательный обход) это означает, что блокируется только таблица:

1. Получение блокировок планировщика (таблица + 5 индексов).


2. Выбор типа плана → обобщённый.


3. Проверка наличия кэшированного плана:
   

   if (CheckCachedPlan(plansource))  // Возвращает TRUE — план уже есть!
   
   {
   
       plan = plansource->gplan;  // Используем кэшированный план
   
   }

   
   


4. Внутри CheckCachedPlan берутся блокировки исполнителя:
   

   AcquireExecutorLocks(plan->stmt_list, true);

   
   
   
   
   • Блокируется PlannedStmt (выход планировщика).
   
   
   • PlannedStmt содержит только таблицу (выбран последовательный обход) в нашем случае.
   
   
   • Получена 1 блокировка исполнителя.
   
   
   • Итог: всего 1 блокировка.
   
   
   
   

Итоги

Подготовленные операторы могут заметно снизить конкуренцию LWLock:LockManager — но только начиная с 7‑го выполнения, а не с 6‑го. В ходе выполнений 1–6, при стандартном plan_cache_mode (auto), Postgres всё ещё использует блокировки планировщика, которые блокируют все задействованные в запросе таблицы, вместе со всеми их индексами.