Post @drema201 — АО «ГНИВЦ» corporate blog

Dec 18 2024 at 16:177.4K

И ещё раз о внешних ключах

Выяснилось, что мы можем повлиять на механику создания/валидации внешних ключей таким же образом, каким мы можем повлиять на план исполнения обычного SQL-запроса.

Мой тестовый пример (все имена реальных объектов заменены, планы и цифры реальные). Выполняю 3 варианта валидации FK c разными настройками сессии, план исполнения смотрю через расширение pg_query_state.

Исполнение "по умолчанию" выбирается merge join.
ALTER TABLE child_t VALIDATE CONSTRAINT fk_child_parent;

select plan from pg_query_state(1483434);
                                                          plan
------------------------------------------------------------------------------------------------------------------------
 Merge Anti Join (Current loop: actual rows=0, loop number=1)                                                          +
   Merge Cond: (fk.entity_egrn_id = pk.id)                                                                             +
   ->  Index Only Scan using child_t_idx1 on child_t fk (Current loop: actual rows=1, loop number=1)    +
         Index Cond: (entity_egrn_id IS NOT NULL)                                                                      +
         Heap Fetches: 1                                                                                               +
   ->  Index Only Scan using parent_t_pkey on parent_t pk (Current loop: actual rows=113215, loop number=1)+
         Heap Fetches: 113215
(1 row)

SET enable_mergejoin TO false;
ALTER TABLE child_t VALIDATE CONSTRAINT fk_child_parent;

 select plan from pg_query_state(1483434);
                                             plan
-----------------------------------------------------------------------------------------------
 Hash Anti Join (Current loop: actual rows=0, loop number=1)                                  +
   Hash Cond: (fk.entity_egrn_id = pk.id)                                                     +
   ->  Seq Scan on child_t fk (Current loop: actual rows=1, loop number=1)            +
         Filter: (entity_id IS NOT NULL)                                                 +
   ->  Hash (Current loop: actual rows=0, loop number=1)                                      +
         Buckets: 67108864  Batches: 8  Memory Usage: 165802kB                                +
         ->  Seq Scan on parent_t pk (Current loop: actual rows=33979819, loop number=1)

SET enable_hashjoin TO false;
SET enable_mergejoin TO false;
ALTER TABLE child_t VALIDATE CONSTRAINT fk_child_parent;

select plan from pg_query_state(1483434);
                                               plan
---------------------------------------------------------------------------------------------------
 Nested Loop Anti Join (Current loop: actual rows=0, loop number=1)                               +
   ->  Seq Scan on child_t fk (Current loop: actual rows=329042, loop number=1)           +
         Filter: (entity_id IS NOT NULL)                                                     +
   ->  Index Only Scan using parent_t_pkey on parent_t pk (actual rows=1 loops=329041)+
         Index Cond: (id = fk.entity_id)                                                     +
         Heap Fetches: 329041

Почему всё это может быть важно:
1. Вы банально могли что-то делать в своей сессии и манипулировать её состоянием, например, "форсить" вычитку через Nested Loop, а затем запустить построение/валидацию FK на громадных таблицах.
2. По каким-то причинам PG выбрал неподходящий план, и вы знаете, что другой способ будет наверняка лучше :)

Articles

Information