https://pastein.ru/t/xug
Загрузка данных
Subquery Scan on r (cost=1318054.82..10987948.87 rows=60941 width=498)
CTE target_decisions
-> Seq Scan on ops_assignee_decision d_2 (cost=0.00..19909.30 rows=487530 width=4)
Filter: (accepted IS NOT NULL)
CTE charge_operations
-> Merge Join (cost=56812.83..79404.29 rows=2249 width=8)
Merge Cond: (o.ops_assignee_decision_id = d_3.id)
-> Gather Merge (cost=1000.75..3714750.15 rows=413859 width=8)
Workers Planned: 2
-> Nested Loop (cost=0.72..3665980.53 rows=172441 width=8)
-> Parallel Index Scan using ind_operation_ops_assignee_decision_id on operation o (cost=0.57..2790831.09 rows=35005615 width=12)
Filter: ((approved IS NOT NULL) AND (deleted IS NULL) AND (effective_date <= CURRENT_DATE))
-> Memoize (cost=0.15..0.18 rows=1 width=4)
Cache Key: o.operation_type_id
Cache Mode: logical
-> Index Scan using operation_type_pkey on operation_type ot (cost=0.14..0.17 rows=1 width=4)
Index Cond: (id = o.operation_type_id)
Filter: ((code)::text = 'CHARGE_FROM_RESERVE'::text)
-> Sort (cost=55810.32..57029.14 rows=487530 width=4)
Sort Key: d_3.id
-> CTE Scan on target_decisions d_3 (cost=0.00..9750.60 rows=487530 width=4)
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=899907.07..10565992.30 rows=60941 width=356)
InitPlan 5
-> Result (cost=0.00..0.01 rows=1 width=1)
InitPlan 7
-> Result (cost=1.02..1.03 rows=1 width=1)
InitPlan 6
-> 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 10
-> Result (cost=0.00..0.01 rows=1 width=1)
InitPlan 12
-> Result (cost=1.02..1.03 rows=1 width=1)
InitPlan 11
-> 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 15
-> Result (cost=0.00..0.01 rows=1 width=1)
InitPlan 17
-> Result (cost=1.02..1.03 rows=1 width=1)
InitPlan 16
-> 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=899745.34..10563703.87 rows=60941 width=426)
-> Nested Loop Left Join (cost=899702.55..7954815.48 rows=60941 width=398)
-> Merge Left Join (cost=899586.81..899892.12 rows=60941 width=366)
Merge Cond: (oad.id = pr_1.ops_assignee_decision_id)
-> Sort (cost=887339.01..887491.37 rows=60941 width=334)
Sort Key: oad.id
-> Hash Left Join (cost=870147.47..882495.69 rows=60941 width=334)
Hash Cond: (oad.id = pay.decision_id)
-> Hash Join (cost=761792.52..773980.76 rows=60941 width=302)
Hash Cond: (td.id = oad.id)
-> CTE Scan on target_decisions td (cost=0.00..9750.60 rows=487530 width=4)
-> Hash (cost=761030.75..761030.75 rows=60941 width=302)
-> Hash Left Join (cost=748803.16..761030.75 rows=60941 width=302)
Hash Cond: (oad.id = ch.decision_id)
-> Gather (cost=736172.27..748239.89 rows=60941 width=270)
Workers Planned: 1
-> Hash Left Join (cost=735172.27..741145.79 rows=35848 width=270)
Hash Cond: (c.fund_id = f.id)
-> Parallel Hash Right Join (cost=735159.80..741038.02 rows=35848 width=244)
Hash Cond: (oadp.ops_assignee_decision_id = oad.id)
-> Parallel Seq Scan on ops_assignee_decision_payment oadp (cost=0.00..4993.51 rows=207151 width=11)
-> Parallel Hash (cost=734842.40..734842.40 rows=25392 width=237)
-> Hash Join (cost=412.12..734842.40 rows=25392 width=237)
Hash Cond: (oad.agent_id = a.id)
-> Nested Loop (cost=11.53..734375.12 rows=25392 width=187)
-> Hash Left Join (cost=11.11..726746.35 rows=14030 width=167)
Hash Cond: ((im.insurance_number)::text = (dr.insurance_number)::text)
-> 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 15).col1 THEN true WHEN (InitPlan 17).col1 THEN (ANY (id = (hashed SubPlan 18).col1)) ELSE (NOT (ANY (id = (hashed SubPlan 19).col1))) END
SubPlan 18
-> 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 19
-> 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 5).col1 THEN true WHEN (InitPlan 7).col1 THEN (ANY (id = (hashed SubPlan 8).col1)) ELSE (NOT (ANY (id = (hashed SubPlan 9).col1))) END
SubPlan 8
-> 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 9
-> 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 10).col1 THEN true WHEN (InitPlan 12).col1 THEN (ANY (id = (hashed SubPlan 13).col1)) ELSE (NOT (ANY (id = (hashed SubPlan 14).col1))) END
SubPlan 13
-> 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 14
-> Seq Scan on rbac_public_hidden_individual (cost=0.00..0.00 rows=1 width=8)
-> 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=354.15..354.15 rows=3715 width=58)
-> Seq Scan on agent a (cost=0.00..354.15 rows=3715 width=58)
-> Hash (cost=8.32..8.32 rows=332 width=34)
-> Seq Scan on fund f (cost=0.00..8.32 rows=332 width=34)
-> Hash (cost=12630.83..12630.83 rows=5 width=36)
-> Subquery Scan on ch (cost=12630.68..12630.83 rows=5 width=36)
-> GroupAggregate (cost=12630.68..12630.78 rows=5 width=36)
Group Key: co.decision_id
-> Sort (cost=12630.68..12630.70 rows=5 width=26)
Sort Key: co.decision_id
-> Nested Loop (cost=1.41..12630.63 rows=5 width=26)
Join Filter: (cat.id = ca.account_type_id)
-> 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)
-> Nested Loop (cost=1.14..12600.02 rows=2249 width=30)
-> Nested Loop (cost=0.57..6342.18 rows=2249 width=30)
-> CTE Scan on charge_operations co (cost=0.00..44.98 rows=2249 width=8)
-> Index Scan using transaction_operation_id_gidx on transaction tr (cost=0.57..2.79 rows=0 width=0)
Index Cond: (operation_id = co.operation_id)
-> Index Scan using account_pkey on account ca (cost=0.56..2.78 rows=1 width=8)
Index Cond: (id = tr.credit_account_id)
-> Hash (cost=107523.84..107523.84 rows=66489 width=36)
-> Subquery Scan on pay (cost=106027.84..107523.84 rows=66489 width=36)
-> HashAggregate (cost=106027.84..106858.95 rows=66489 width=36)
Group Key: pr.ops_assignee_decision_id
-> Hash Join (cost=56229.03..105695.40 rows=66489 width=11)
Hash Cond: (pr.ops_payment_register_id = p.register_id)
-> Merge Join (cost=55810.82..102878.57 rows=462328 width=15)
Merge Cond: (pr.ops_assignee_decision_id = d.id)
-> Index Scan using ops_payment_register_decision_ops_assignee_decision_id_idx on ops_payment_register_decision pr (cost=0.43..384918.99 rows=4580534 width=15)
-> Sort (cost=55810.32..57029.14 rows=487530 width=4)
Sort Key: d.id
-> CTE Scan on target_decisions d (cost=0.00..9750.60 rows=487530 width=4)
