И ещё раз о внешних ключах
Выяснилось, что мы можем повлиять на механику создания/валидации внешних ключей таким же образом, каким мы можем повлиять на план исполнения обычного 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 выбрал неподходящий план, и вы знаете, что другой способ будет наверняка лучше :)