https://pastein.ru/t/pug
Загрузка данных
Subquery Scan on r (cost=12172536.27..21842430.31 rows=60941 width=498)
CTE target_decisions
-> Seq Scan on ops_assignee_decision d_3 (cost=0.00..19909.30 rows=487530 width=4)
Filter: (accepted IS NOT NULL)
CTE paid_registers
-> HashAggregate (cost=317077.43..317206.11 rows=12868 width=4)
Group Key: o_1.ops_payment_register_id
-> Nested Loop (cost=58352.74..316949.82 rows=51043 width=4)
-> Bitmap Heap Scan on operation_type ot_1 (cost=2.76..4.89 rows=2 width=4)
Recheck Cond: ((code)::text = ANY ('{PAYMENT_PP,ADDITIONAL_PAYMENT_PP}'::text[]))
-> Bitmap Index Scan on operation_type_code_key (cost=0.00..2.76 rows=2 width=0)
Index Cond: ((code)::text = ANY ('{PAYMENT_PP,ADDITIONAL_PAYMENT_PP}'::text[]))
-> Bitmap Heap Scan on operation o_1 (cost=58349.98..157513.06 rows=95941 width=8)
Recheck Cond: ((ot_1.id = operation_type_id) AND (ops_payment_register_id IS NOT NULL))
Filter: ((approved IS NOT NULL) AND (deleted IS NULL) AND (effective_date <= CURRENT_DATE))
-> BitmapAnd (cost=58349.98..58349.98 rows=95978 width=0)
-> Bitmap Index Scan on ind_operation_operation_type_id (cost=0.00..13098.68 rows=1556408 width=0)
Index Cond: (operation_type_id = ot_1.id)
-> Bitmap Index Scan on operation_ops_payment_register_id_idx (cost=0.00..43931.86 rows=5182839 width=0)
Index Cond: (ops_payment_register_id IS NOT NULL)
CTE confirmed_returns
-> Unique (cost=1627.17..1628.05 rows=176 width=4)
-> Sort (cost=1627.17..1627.61 rows=176 width=4)
Sort Key: o_2.ops_payment_return_id
-> Hash Join (cost=3.07..1620.60 rows=176 width=4)
Hash Cond: (o_2.operation_type_id = ot_2.id)
-> Index Scan using operation_ops_payment_return_id_idx on operation o_2 (cost=0.57..1522.42 rows=35704 width=8)
Index Cond: (ops_payment_return_id IS NOT NULL)
Filter: ((approved IS NOT NULL) AND (deleted IS NULL) AND (effective_date <= CURRENT_DATE))
-> Hash (cost=2.49..2.49 rows=1 width=4)
-> Index Scan using operation_type_code_key on operation_type ot_2 (cost=0.27..2.49 rows=1 width=4)
Index Cond: ((code)::text = 'RETURN'::text)
-> WindowAgg (cost=11833792.81..21499878.04 rows=60941 width=356)
InitPlan 4
-> Result (cost=0.00..0.01 rows=1 width=1)
InitPlan 6
-> Result (cost=1.02..1.03 rows=1 width=1)
InitPlan 5
-> Seq Scan on user_group ug (cost=0.00..1.02 rows=1 width=0)
Filter: (user_id = (NULLIF(current_setting('app.user_id'::text, true), ''::text))::bigint)
InitPlan 9
-> Result (cost=0.00..0.01 rows=1 width=1)
InitPlan 11
-> Result (cost=1.02..1.03 rows=1 width=1)
InitPlan 10
-> Seq Scan on user_group ug_2 (cost=0.00..1.02 rows=1 width=0)
Filter: (user_id = (NULLIF(current_setting('app.user_id'::text, true), ''::text))::bigint)
InitPlan 14
-> Result (cost=0.00..0.01 rows=1 width=1)
InitPlan 16
-> Result (cost=1.02..1.03 rows=1 width=1)
InitPlan 15
-> Seq Scan on user_group ug_5 (cost=0.00..1.02 rows=1 width=0)
Filter: (user_id = (NULLIF(current_setting('app.user_id'::text, true), ''::text))::bigint)
-> Nested Loop Left Join (cost=11833631.07..21497589.61 rows=60941 width=426)
-> Nested Loop Left Join (cost=11833588.29..18888701.22 rows=60941 width=398)
-> Merge Left Join (cost=11833472.54..11833777.85 rows=60941 width=366)
Merge Cond: (oad.id = pr_1.ops_assignee_decision_id)
-> Sort (cost=11821224.75..11821377.10 rows=60941 width=334)
Sort Key: oad.id
-> Hash Left Join (cost=11073585.81..11816381.43 rows=60941 width=334)
Hash Cond: (oad.id = pay.decision_id)
-> Hash Join (cost=10965230.85..11707866.50 rows=60941 width=302)
Hash Cond: (oad.agent_id = a.id)
-> Hash Left Join (cost=10964830.26..11707305.87 rows=60941 width=252)
Hash Cond: (oad.id = ch.decision_id)
-> Hash Left Join (cost=27701.94..770017.58 rows=60941 width=220)
Hash Cond: (oad.id = oadp.ops_assignee_decision_id)
-> Hash Join (cost=16856.41..758503.32 rows=60941 width=213)
Hash Cond: (oad.id = td.id)
-> Gather (cost=1011.69..741820.66 rows=60941 width=213)
Workers Planned: 2
-> Nested Loop (cost=11.69..734726.56 rows=25392 width=213)
-> Hash Left Join (cost=11.26..727097.79 rows=14030 width=193)
Hash Cond: ((im.insurance_number)::text = (dr.insurance_number)::text)
-> Nested Loop Left Join (cost=8.60..727042.50 rows=14030 width=180)
-> Nested Loop (cost=8.44..726691.06 rows=14030 width=154)
-> Nested Loop (cost=5.90..652046.96 rows=28060 width=89)
-> Nested Loop (cost=3.40..507726.34 rows=56120 width=66)
-> Nested Loop (cost=2.96..434515.09 rows=56120 width=58)
-> Parallel Index Scan using ops_inherited_payment_pkey on ops_assignee_payment oap (cost=0.42..17124.72 rows=112240 width=12)
-> Index Scan using individual_pkey on individual ip (cost=2.54..3.72 rows=1 width=54)
Index Cond: (id = oap.assignee_id)
Filter: CASE WHEN (InitPlan 14).col1 THEN true WHEN (InitPlan 16).col1 THEN (ANY (id = (hashed SubPlan 17).col1)) ELSE (NOT (ANY (id = (hashed SubPlan 18).col1))) END
SubPlan 17
-> Unique (cost=2.09..2.10 rows=2 width=8)
-> Sort (cost=2.09..2.09 rows=2 width=8)
Sort Key: a_5.individual_id
-> Append (cost=0.00..2.08 rows=2 width=8)
-> Nested Loop (cost=0.00..1.03 rows=1 width=8)
Join Filter: (ug_6.group_id = a_5.group_id)
-> Seq Scan on user_group ug_6 (cost=0.00..1.02 rows=1 width=8)
Filter: (user_id = (NULLIF(current_setting('app.user_id'::text, true), ''::text))::bigint)
-> Seq Scan on group_individual_access a_5 (cost=0.00..0.00 rows=1 width=16)
-> Nested Loop (cost=0.00..1.03 rows=1 width=8)
Join Filter: (ug_7.group_id = a_6.group_id)
-> Seq Scan on user_group ug_7 (cost=0.00..1.02 rows=1 width=8)
Filter: (user_id = (NULLIF(current_setting('app.user_id'::text, true), ''::text))::bigint)
-> Seq Scan on group_contract_access a_6 (cost=0.00..0.00 rows=1 width=16)
Filter: (individual_id IS NOT NULL)
SubPlan 18
-> Seq Scan on rbac_public_hidden_individual rbac_public_hidden_individual_1 (cost=0.00..0.00 rows=1 width=8)
-> Index Scan using sharer_pkey on sharer sh (cost=0.43..1.30 rows=1 width=12)
Index Cond: (id = oap.sharer_id)
-> Index Scan using contract_pkey on contract c (cost=2.50..2.57 rows=1 width=27)
Index Cond: (id = sh.contract_id)
Filter: CASE WHEN (InitPlan 4).col1 THEN true WHEN (InitPlan 6).col1 THEN (ANY (id = (hashed SubPlan 7).col1)) ELSE (NOT (ANY (id = (hashed SubPlan 8).col1))) END
SubPlan 7
-> Nested Loop (cost=0.00..1.03 rows=1 width=8)
Join Filter: (ug_1.group_id = a_1.group_id)
-> Seq Scan on user_group ug_1 (cost=0.00..1.02 rows=1 width=8)
Filter: (user_id = (NULLIF(current_setting('app.user_id'::text, true), ''::text))::bigint)
-> Seq Scan on rbac_contract_access a_1 (cost=0.00..0.00 rows=1 width=16)
SubPlan 8
-> Nested Loop (cost=0.00..1.02 rows=1 width=8)
Join Filter: (a_2.group_id = g_1.id)
-> Seq Scan on rbac_contract_access a_2 (cost=0.00..0.00 rows=1 width=16)
-> Seq Scan on individual_groups g_1 (cost=0.00..1.01 rows=1 width=4)
Filter: hidden_from_external
-> Index Scan using individual_pkey on individual im (cost=2.54..2.66 rows=1 width=69)
Index Cond: (id = sh.individual_id)
Filter: CASE WHEN (InitPlan 9).col1 THEN true WHEN (InitPlan 11).col1 THEN (ANY (id = (hashed SubPlan 12).col1)) ELSE (NOT (ANY (id = (hashed SubPlan 13).col1))) END
SubPlan 12
-> Unique (cost=2.09..2.10 rows=2 width=8)
-> Sort (cost=2.09..2.09 rows=2 width=8)
Sort Key: a_3.individual_id
-> Append (cost=0.00..2.08 rows=2 width=8)
-> Nested Loop (cost=0.00..1.03 rows=1 width=8)
Join Filter: (ug_3.group_id = a_3.group_id)
-> Seq Scan on user_group ug_3 (cost=0.00..1.02 rows=1 width=8)
Filter: (user_id = (NULLIF(current_setting('app.user_id'::text, true), ''::text))::bigint)
-> Seq Scan on group_individual_access a_3 (cost=0.00..0.00 rows=1 width=16)
-> Nested Loop (cost=0.00..1.03 rows=1 width=8)
Join Filter: (ug_4.group_id = a_4.group_id)
-> Seq Scan on user_group ug_4 (cost=0.00..1.02 rows=1 width=8)
Filter: (user_id = (NULLIF(current_setting('app.user_id'::text, true), ''::text))::bigint)
-> Seq Scan on group_contract_access a_4 (cost=0.00..0.00 rows=1 width=16)
Filter: (individual_id IS NOT NULL)
SubPlan 13
-> Seq Scan on rbac_public_hidden_individual (cost=0.00..0.00 rows=1 width=8)
-> Memoize (cost=0.16..0.18 rows=1 width=34)
Cache Key: c.fund_id
Cache Mode: logical
-> Index Scan using fund_pkey on fund f (cost=0.15..0.17 rows=1 width=34)
Index Cond: (id = c.fund_id)
-> Hash (cost=2.66..2.66 rows=1 width=28)
-> Index Scan using ind_dia_rops_dia_register_id on dia_rops dr (cost=0.44..2.66 rows=1 width=28)
Index Cond: (dia_register_id = 1000000120)
-> Index Scan using idx_ops_assignee_decision_ops_assignee_payment_id on ops_assignee_decision oad (cost=0.42..0.52 rows=2 width=28)
Index Cond: (ops_assignee_payment_id = oap.id)
-> Hash (cost=9750.60..9750.60 rows=487530 width=4)
-> CTE Scan on target_decisions td (cost=0.00..9750.60 rows=487530 width=4)
-> Hash (cost=6443.57..6443.57 rows=352157 width=11)
-> Seq Scan on ops_assignee_decision_payment oadp (cost=0.00..6443.57 rows=352157 width=11)
-> Hash (cost=10937127.96..10937127.96 rows=29 width=36)
-> Subquery Scan on ch (cost=10937127.09..10937127.96 rows=29 width=36)
-> GroupAggregate (cost=10937127.09..10937127.67 rows=29 width=36)
Group Key: o.ops_assignee_decision_id
-> Sort (cost=10937127.09..10937127.16 rows=29 width=10)
Sort Key: o.ops_assignee_decision_id
-> Hash Join (cost=180927.33..10937126.38 rows=29 width=10)
Hash Cond: (t.operation_id = o.id)
-> Gather (cost=1002.92..10753132.23 rows=1085187 width=10)
Workers Planned: 2
-> Nested Loop (cost=2.93..10643613.53 rows=452161 width=10)
-> Hash Join (cost=2.50..548683.52 rows=40862 width=4)
Hash Cond: (ca.account_type_id = cat.id)
-> Parallel Seq Scan on account ca (cost=0.00..496847.87 rows=19572787 width=8)
-> Hash (cost=2.49..2.49 rows=1 width=4)
-> Index Only Scan using account_type_mnemonics_idx on account_type cat (cost=0.27..2.49 rows=1 width=4)
Index Cond: (mnemonics = 'НВ ПП ОПС'::text)
-> Append (cost=0.42..241.66 rows=539 width=14)
-> Index Scan using transaction_2000_credit_account_id_effective_date_value_idx on transaction_2000 t_1 (cost=0.42..1.61 rows=6 width=14)
Index Cond: (credit_account_id = ca.id)
-> Index Scan using transaction_2001_credit_account_id_effective_date_value_idx on transaction_2001 t_2 (cost=0.42..1.53 rows=4 width=14)
Index Cond: (credit_account_id = ca.id)
-> Index Scan using transaction_2002_credit_account_id_effective_date_value_idx on transaction_2002 t_3 (cost=0.42..1.80 rows=5 width=14)
Index Cond: (credit_account_id = ca.id)
-> Index Scan using transaction_2003_credit_account_id_effective_date_value_idx on transaction_2003 t_4 (cost=0.42..1.16 rows=3 width=14)
Index Cond: (credit_account_id = ca.id)
-> Index Scan using transaction_2004_credit_account_id_effective_date_value_idx on transaction_2004 t_5 (cost=0.42..1.18 rows=3 width=14)
Index Cond: (credit_account_id = ca.id)
-> Index Scan using transaction_2005_credit_account_id_effective_date_value_idx on transaction_2005 t_6 (cost=0.43..2.37 rows=9 width=13)
Index Cond: (credit_account_id = ca.id)
-> Index Scan using transaction_2006_credit_account_id_effective_date_value_idx on transaction_2006 t_7 (cost=0.43..2.23 rows=9 width=14)
Index Cond: (credit_account_id = ca.id)
-> Index Scan using transaction_2007_credit_account_id_effective_date_value_idx on transaction_2007 t_8 (cost=0.43..2.66 rows=11 width=13)
Index Cond: (credit_account_id = ca.id)
-> Index Scan using transaction_2008_credit_account_id_effective_date_value_idx on transaction_2008 t_9 (cost=0.43..3.46 rows=14 width=14)
Index Cond: (credit_account_id = ca.id)
-> Index Scan using transaction_2009_credit_account_id_effective_date_value_idx on transaction_2009 t_10 (cost=0.43..2.72 rows=12 width=14)
Index Cond: (credit_account_id = ca.id)
-> Index Scan using transaction_2010_credit_account_id_effective_date_value_idx on transaction_2010 t_11 (cost=0.43..3.26 rows=15 width=14)
Index Cond: (credit_account_id = ca.id)
-> Index Scan using transaction_2011_01_credit_account_id_effective_date_value_idx on transaction_2011_01 t_12 (cost=0.42..1.09 rows=2 width=14)
Index Cond: (credit_account_id = ca.id)
-> Index Scan using transaction_2011_02_credit_account_id_effective_date_value_idx on transaction_2011_02 t_13 (cost=0.42..1.11 rows=2 width=14)
Index Cond: (credit_account_id = ca.id)
-> Index Scan using transaction_2011_03_credit_account_id_effective_date_value_idx on transaction_2011_03 t_14 (cost=0.43..1.05 rows=2 width=14)
Index Cond: (credit_account_id = ca.id)
