update meta_data_vault.law_party_stage3322 stage
set subj_id = (
select max(hub.subj_id)
from templ.hub_subject hub
where stage.inn = hub.inn
or stage.snils_individual = hub.snils
or hub.other_ident_code = coalesce(stage.other_ident_code, stage.other_ident_code_js)
) 

123йй, Все индексы которые есть
CREATE INDEX i_hub_subject_right ON templ.hub_subject (subj_id,load_time);

CREATE INDEX i_hub_subject_right_inn ON templ.hub_subject (inn);

CREATE INDEX i_hub_subject_right_snils ON templ.hub_subject (snils);

CREATE INDEX i_hub_subject_session_id ON templ.hub_subject (session_id);

env,

Update on law_party_stage stage  (cost=0.00..65166.17 rows=30 width=2160) (actual time=0.001..0.001 rows=0 loops=1)
  ->  Seq Scan on law_party_stage stage  (cost=0.00..65166.17 rows=30 width=2160) (actual time=0.000..0.000 rows=0 loops=1)
        SubPlan 2
          ->  Result  (cost=2171.85..2171.86 rows=1 width=0) (never executed)
                InitPlan 1 (returns $4)
                  ->  Limit  (cost=0.42..2171.85 rows=1 width=8) (never executed)
                        ->  Index Scan Backward using pk_hub_subject_right on hub_subject hub  (cost=0.42..8686.15 rows=4 width=8) (never executed)
                              Index Cond: (subj_id IS NOT NULL)
                              Filter: ((stage.inn = (inn)::text) OR (stage.snils_individual = (snils)::text) OR ((other_ident_code)::text = stage.other_ident_code) OR ((other_ident_code)::text = stage.other_ident_code_js))
Planning time: 0.252 ms
Execution time: 0.065 ms

env, да
Скобки забыл, все заработало 
update meta_data_vault.law_party_stage3322 stage
set subj_id = (
select max(hub.subj_id)
from templ.hub_subject hub
where (stage.inn = hub.inn
or (stage.snils_individual = hub.snils
or hub.other_ident_code = coalesce(stage.other_ident_code, stage.other_ident_code_js))));