explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R9JR

Settings
# exclusive inclusive rows x rows loops node
1. 2.812 766,249.630 ↓ 81.0 162 1

Sort (cost=402,541.38..402,541.39 rows=2 width=10,852) (actual time=766,249.534..766,249.630 rows=162 loops=1)

  • Output: outer_sql.id, outer_sql.message_id, outer_sql.client_cd, outer_sql.transaction_event_id, outer_sql.transaction_id, outer_sql.oms_reference_id, outer_sql.block_reference_id, outer_sql.allocation_reference_id, outer_sql.original_reference_id, outer_sql.orig_oms_reference_id, outer_sql.rebook_in, outer_sql.oms_system_cd, outer_sql.account_cd, outer_sql.message_type_cd, outer_sql.trade_desk_cd, outer_sql.trader_cd, outer_sql.trade_type_cd, outer_sql.trade_dt, outer_sql.settlement_dt, outer_sql.trade_execution_date_tm, outer_sql.exchange_country_cd, outer_sql.client_exchange_cd, outer_sql.delivery_type_cd, outer_sql.trade_currency_cd, outer_sql.trade_pr, outer_sql.client_exec_broker_cd, outer_sql.exec_instructions_tx, outer_sql.instrument_type_cd, outer_sql.instrument_desc, outer_sql.ticker, outer_sql.cusip, outer_sql.sedol, outer_sql.isin, outer_sql.yellow_key, outer_sql.cross_account_cd, outer_sql.step_out_broker_cd, outer_sql.step_out_in, outer_sql.credit_broker_cd, outer_sql.trade_qty, outer_sql.gross_am, outer_sql.net_am, outer_sql.settlement_currency_cd, outer_sql.commission_am, outer_sql.commission_rt, outer_sql.commission_sharing_in, outer_sql.commission_legal_entity_cd, outer_sql.directed_broker_in, outer_sql.commission_reason_cd, outer_sql.commission_hard_soft_cd, outer_sql.local_tax_am, outer_sql.local_fee_am, outer_sql.stamp_duty_am, outer_sql.levy_am, outer_sql.transaction_tax_am, outer_sql.other_charges_am, outer_sql.original_face_am, outer_sql.accrued_income_am, outer_sql.factor_rt, outer_sql.issue_country_cd, outer_sql.coupon_rt, outer_sql.yield_pc, outer_sql.trade_spread_am, outer_sql.maturity_dt, outer_sql.issue_dt, outer_sql.contract_multiplier_am, outer_sql.expiry_dt, outer_sql.strike_pr, outer_sql.lot_id, outer_sql.lot_size_am, outer_sql.premium_discount_am, outer_sql.ipo_in, outer_sql.ticket_fund_cd, outer_sql.yield_type_cd, outer_sql.account_group_cd, outer_sql.deposit_role_cd, outer_sql.maturity_am, outer_sql.deposit_taker_cd, outer_sql.clearing_house_cd, outer_sql.client_clearing_broker_cd, outer_sql.clearing_house_fee_am, outer_sql.clearing_house_fee_ccy_cd, outer_sql.clearing_broker_fee_am, outer_sql.clearing_broker_fee_ccy_cd, outer_sql.exchange_rt, outer_sql.value_dt, outer_sql.buy_currency_cd, outer_sql.buy_currency_am, outer_sql.sell_currency_cd, outer_sql.sell_currency_am, outer_sql.fx_strategy_cd, outer_sql.net_settlement_in, outer_sql.open_in, outer_sql.ndf_in, outer_sql.ndf_settlement_currency_cd, outer_sql.fixing_dt, outer_sql.final_close_in, outer_sql.gain_loss_cd, outer_sql.gain_loss_am, outer_sql.third_party_in, outer_sql.fx_indicator_range_cd, outer_sql.fx_indicator_range_tm, outer_sql.limit_pr, outer_sql.open_trade_id, outer_sql.reporting_jurisdication_tx, outer_sql.uti_prefix_cd, outer_sql.uti_id, outer_sql.swap_in, outer_sql.swap_link_id, outer_sql.allocation_cnt, outer_sql.status_source_system_cd, outer_sql.source_system_status_cd, outer_sql.match_status, outer_sql.settle_status, outer_sql.cancel_status, outer_sql.status_event_date_tm, outer_sql.status_desc, outer_sql.receivers_bic_cd, outer_sql.senders_bic_cd, outer_sql.senders_nm, outer_sql.fx_instruction_cd, outer_sql.fx_instruction_ccy_cd, outer_sql.fx_instruction_cancel_in, outer_sql.exec_broker_account_cd, outer_sql.exec_broker_cd, outer_sql.exec_broker_nm, outer_sql.exec_broker_type_cd, outer_sql.clearing_broker_account_cd, outer_sql.clearing_broker_nm, outer_sql.clearing_broker_cd, outer_sql.clearing_broker_type_cd, outer_sql.account_type_cd, outer_sql.exchange_cd, outer_sql.isitc_asset_class_cd, outer_sql.isitc_instrument_type_cd, outer_sql.custodian_account_cd, outer_sql.place_of_settlement_cd, outer_sql.create_user_id, outer_sql.update_user_id, outer_sql.created_at, outer_sql.updated_at, outer_sql.third_party_account_cd, outer_sql.place_of_settlement_type_cd, outer_sql.original_allocation_ref_id, outer_sql.sequence_nbr, outer_sql.block_net_am, outer_sql.block_quantity, outer_sql.block_commission_rt, outer_sql.block_required_ind, outer_sql.block_original_face_am, outer_sql.block_gross_am, outer_sql.block_accrued_income_am, outer_sql.block_commission_am, outer_sql.block_record_in, outer_sql.scope_of_operation, outer_sql.fund_manager, outer_sql.delivery_agent, outer_sql.delivery_agent_acct, outer_sql.delivery_agent_name, outer_sql.receiving_agent, outer_sql.receiving_agent_acct, outer_sql.receiving_agent_name, outer_sql.beneficiary_institution, outer_sql.beneficiary_name, outer_sql.option_type_cd, outer_sql.option_sub_type_cd, outer_sql.otc_fl, outer_sql.cls_in, outer_sql.counterparty_cd, outer_sql.counterparty_nm, outer_sql.block_status, outer_sql.nominal_am, outer_sql.parent_tckt_nbr, outer_sql.linked_allocation_reference_id, outer_sql.research_commission_am, outer_sql.research_commission_rt, outer_sql.split_fl, outer_sql.sec_fees, outer_sql.trans_tax, outer_sql.charges_fees, outer_sql.local_taxes_reporting, outer_sql.local_fees_reporting, outer_sql.other_charges_reporting, outer_sql.issuer_charges_reporting
  • Sort Key: outer_sql.trade_dt DESC
  • Sort Method: quicksort Memory: 189kB
2. 0.549 766,246.818 ↓ 81.0 162 1

Subquery Scan on outer_sql (cost=399,281.13..402,541.35 rows=2 width=10,852) (actual time=71,203.456..766,246.818 rows=162 loops=1)

  • Output: outer_sql.id, outer_sql.message_id, outer_sql.client_cd, outer_sql.transaction_event_id, outer_sql.transaction_id, outer_sql.oms_reference_id, outer_sql.block_reference_id, outer_sql.allocation_reference_id, outer_sql.original_reference_id, outer_sql.orig_oms_reference_id, outer_sql.rebook_in, outer_sql.oms_system_cd, outer_sql.account_cd, outer_sql.message_type_cd, outer_sql.trade_desk_cd, outer_sql.trader_cd, outer_sql.trade_type_cd, outer_sql.trade_dt, outer_sql.settlement_dt, outer_sql.trade_execution_date_tm, outer_sql.exchange_country_cd, outer_sql.client_exchange_cd, outer_sql.delivery_type_cd, outer_sql.trade_currency_cd, outer_sql.trade_pr, outer_sql.client_exec_broker_cd, outer_sql.exec_instructions_tx, outer_sql.instrument_type_cd, outer_sql.instrument_desc, outer_sql.ticker, outer_sql.cusip, outer_sql.sedol, outer_sql.isin, outer_sql.yellow_key, outer_sql.cross_account_cd, outer_sql.step_out_broker_cd, outer_sql.step_out_in, outer_sql.credit_broker_cd, outer_sql.trade_qty, outer_sql.gross_am, outer_sql.net_am, outer_sql.settlement_currency_cd, outer_sql.commission_am, outer_sql.commission_rt, outer_sql.commission_sharing_in, outer_sql.commission_legal_entity_cd, outer_sql.directed_broker_in, outer_sql.commission_reason_cd, outer_sql.commission_hard_soft_cd, outer_sql.local_tax_am, outer_sql.local_fee_am, outer_sql.stamp_duty_am, outer_sql.levy_am, outer_sql.transaction_tax_am, outer_sql.other_charges_am, outer_sql.original_face_am, outer_sql.accrued_income_am, outer_sql.factor_rt, outer_sql.issue_country_cd, outer_sql.coupon_rt, outer_sql.yield_pc, outer_sql.trade_spread_am, outer_sql.maturity_dt, outer_sql.issue_dt, outer_sql.contract_multiplier_am, outer_sql.expiry_dt, outer_sql.strike_pr, outer_sql.lot_id, outer_sql.lot_size_am, outer_sql.premium_discount_am, outer_sql.ipo_in, outer_sql.ticket_fund_cd, outer_sql.yield_type_cd, outer_sql.account_group_cd, outer_sql.deposit_role_cd, outer_sql.maturity_am, outer_sql.deposit_taker_cd, outer_sql.clearing_house_cd, outer_sql.client_clearing_broker_cd, outer_sql.clearing_house_fee_am, outer_sql.clearing_house_fee_ccy_cd, outer_sql.clearing_broker_fee_am, outer_sql.clearing_broker_fee_ccy_cd, outer_sql.exchange_rt, outer_sql.value_dt, outer_sql.buy_currency_cd, outer_sql.buy_currency_am, outer_sql.sell_currency_cd, outer_sql.sell_currency_am, outer_sql.fx_strategy_cd, outer_sql.net_settlement_in, outer_sql.open_in, outer_sql.ndf_in, outer_sql.ndf_settlement_currency_cd, outer_sql.fixing_dt, outer_sql.final_close_in, outer_sql.gain_loss_cd, outer_sql.gain_loss_am, outer_sql.third_party_in, outer_sql.fx_indicator_range_cd, outer_sql.fx_indicator_range_tm, outer_sql.limit_pr, outer_sql.open_trade_id, outer_sql.reporting_jurisdication_tx, outer_sql.uti_prefix_cd, outer_sql.uti_id, outer_sql.swap_in, outer_sql.swap_link_id, outer_sql.allocation_cnt, outer_sql.status_source_system_cd, outer_sql.source_system_status_cd, outer_sql.match_status, outer_sql.settle_status, outer_sql.cancel_status, outer_sql.status_event_date_tm, outer_sql.status_desc, outer_sql.receivers_bic_cd, outer_sql.senders_bic_cd, outer_sql.senders_nm, outer_sql.fx_instruction_cd, outer_sql.fx_instruction_ccy_cd, outer_sql.fx_instruction_cancel_in, outer_sql.exec_broker_account_cd, outer_sql.exec_broker_cd, outer_sql.exec_broker_nm, outer_sql.exec_broker_type_cd, outer_sql.clearing_broker_account_cd, outer_sql.clearing_broker_nm, outer_sql.clearing_broker_cd, outer_sql.clearing_broker_type_cd, outer_sql.account_type_cd, outer_sql.exchange_cd, outer_sql.isitc_asset_class_cd, outer_sql.isitc_instrument_type_cd, outer_sql.custodian_account_cd, outer_sql.place_of_settlement_cd, outer_sql.create_user_id, outer_sql.update_user_id, outer_sql.created_at, outer_sql.updated_at, outer_sql.third_party_account_cd, outer_sql.place_of_settlement_type_cd, outer_sql.original_allocation_ref_id, outer_sql.sequence_nbr, outer_sql.block_net_am, outer_sql.block_quantity, outer_sql.block_commission_rt, outer_sql.block_required_ind, outer_sql.block_original_face_am, outer_sql.block_gross_am, outer_sql.block_accrued_income_am, outer_sql.block_commission_am, outer_sql.block_record_in, outer_sql.scope_of_operation, outer_sql.fund_manager, outer_sql.delivery_agent, outer_sql.delivery_agent_acct, outer_sql.delivery_agent_name, outer_sql.receiving_agent, outer_sql.receiving_agent_acct, outer_sql.receiving_agent_name, outer_sql.beneficiary_institution, outer_sql.beneficiary_name, outer_sql.option_type_cd, outer_sql.option_sub_type_cd, outer_sql.otc_fl, outer_sql.cls_in, outer_sql.counterparty_cd, outer_sql.counterparty_nm, outer_sql.block_status, outer_sql.nominal_am, outer_sql.parent_tckt_nbr, outer_sql.linked_allocation_reference_id, outer_sql.research_commission_am, outer_sql.research_commission_rt, outer_sql.split_fl, outer_sql.sec_fees, outer_sql.trans_tax, outer_sql.charges_fees, outer_sql.local_taxes_reporting, outer_sql.local_fees_reporting, outer_sql.other_charges_reporting, outer_sql.issuer_charges_reporting
  • Filter: ((outer_sql.settle_status)::text = ANY ('{NO,YES}'::text[]))
3.          

CTE settled_sql

4. 8.136 356.894 ↓ 1.0 111,928 1

Append (cost=3,236.39..95,249.26 rows=110,882 width=89) (actual time=21.793..356.894 rows=111,928 loops=1)

5. 9.188 29.539 ↓ 174.0 174 1

Bitmap Heap Scan on tdm.trade_events te_1_1 (cost=3,236.39..27,626.49 rows=1 width=89) (actual time=21.791..29.539 rows=174 loops=1)

  • Output: te_1_1.transaction_id, te_1_1.id, te_1_1.isitc_asset_class_cd, te_1_1.transaction_event_id, te_1_1.isitc_instrument_type_cd, te_1_1.source_system_status_cd, te_1_1.allocation_reference_id, te_1_1.status_event_date_tm
  • Recheck Cond: (((te_1_1.isitc_asset_class_cd)::text = 'FX'::text) AND (((te_1_1.source_system_status_cd)::text = 'NDFCloseSuppAck'::text) OR ((te_1_1.source_system_status_cd)::text = 'SETTLED'::text)))
  • Filter: (((te_1_1.fx_strategy_cd)::text = 'ANDF'::text) AND ((te_1_1.open_in)::text = 'N'::text))
  • Rows Removed by Filter: 4806
  • Heap Blocks: exact=3000
6. 1.091 20.351 ↓ 0.0 0 1

BitmapAnd (cost=3,236.39..3,236.39 rows=8,977 width=0) (actual time=20.351..20.351 rows=0 loops=1)

7. 4.154 4.154 ↑ 1.1 36,517 1

Bitmap Index Scan on idx_isitc_asset_class_cd (cost=0.00..712.50 rows=38,411 width=0) (actual time=4.154..4.154 rows=36,517 loops=1)

  • Index Cond: ((te_1_1.isitc_asset_class_cd)::text = 'FX'::text)
8. 0.003 15.106 ↓ 0.0 0 1

BitmapOr (cost=2,523.64..2,523.64 rows=113,438 width=0) (actual time=15.106..15.106 rows=0 loops=1)

9. 0.019 0.019 ↓ 0.0 0 1

Bitmap Index Scan on index_trade_events_on_source_system_status_cd (cost=0.00..4.43 rows=1 width=0) (actual time=0.019..0.019 rows=0 loops=1)

  • Index Cond: ((te_1_1.source_system_status_cd)::text = 'NDFCloseSuppAck'::text)
10. 15.084 15.084 ↑ 1.0 112,457 1

Bitmap Index Scan on index_trade_events_on_source_system_status_cd (cost=0.00..2,519.21 rows=113,438 width=0) (actual time=15.084..15.084 rows=112,457 loops=1)

  • Index Cond: ((te_1_1.source_system_status_cd)::text = 'SETTLED'::text)
11. 300.887 319.219 ↓ 1.0 111,754 1

Bitmap Heap Scan on tdm.trade_events te_1_2 (cost=2,491.75..66,513.94 rows=110,881 width=89) (actual time=23.679..319.219 rows=111,754 loops=1)

  • Output: te_1_2.transaction_id, te_1_2.id, te_1_2.isitc_asset_class_cd, te_1_2.transaction_event_id, te_1_2.isitc_instrument_type_cd, te_1_2.source_system_status_cd, te_1_2.allocation_reference_id, te_1_2.status_event_date_tm
  • Recheck Cond: ((te_1_2.source_system_status_cd)::text = 'SettledACK'::text)
  • Heap Blocks: exact=33331
12. 18.332 18.332 ↓ 1.0 111,754 1

Bitmap Index Scan on index_trade_events_on_source_system_status_cd (cost=0.00..2,464.03 rows=110,881 width=0) (actual time=18.332..18.332 rows=111,754 loops=1)

  • Index Cond: ((te_1_2.source_system_status_cd)::text = 'SettledACK'::text)
13.          

CTE not_settled_sql

14. 2.156 4,317.837 ↓ 12.9 9,919 1

Subquery Scan on outer_sql_1 (cost=160,511.74..165,492.95 rows=766 width=89) (actual time=4,303.255..4,317.837 rows=9,919 loops=1)

  • Output: outer_sql_1.transaction_id, outer_sql_1.id, outer_sql_1.isitc_asset_class_cd, outer_sql_1.transaction_event_id, outer_sql_1.isitc_instrument_type_cd, outer_sql_1.source_system_status_cd, outer_sql_1.allocation_reference_id, outer_sql_1.rnk
  • Filter: (outer_sql_1.rnk = 1)
  • Rows Removed by Filter: 9541
15. 10.007 4,315.681 ↑ 7.9 19,460 1

WindowAgg (cost=160,511.74..163,577.10 rows=153,268 width=98) (actual time=4,303.187..4,315.681 rows=19,460 loops=1)

  • Output: te_1_3.transaction_id, te_1_3.id, te_1_3.isitc_asset_class_cd, te_1_3.transaction_event_id, te_1_3.isitc_instrument_type_cd, te_1_3.source_system_status_cd, te_1_3.allocation_reference_id, row_number() OVER (?), cd1.code_value
16. 62.550 4,305.674 ↑ 7.9 19,460 1

Sort (cost=160,511.74..160,894.91 rows=153,268 width=90) (actual time=4,303.151..4,305.674 rows=19,460 loops=1)

  • Output: te_1_3.allocation_reference_id, cd1.code_value, te_1_3.transaction_id, te_1_3.id, te_1_3.isitc_asset_class_cd, te_1_3.transaction_event_id, te_1_3.isitc_instrument_type_cd, te_1_3.source_system_status_cd
  • Sort Key: te_1_3.allocation_reference_id, cd1.code_value
  • Sort Method: quicksort Memory: 3322kB
17. 154.803 4,243.124 ↑ 7.9 19,460 1

Merge Anti Join (cost=133,337.97..139,450.00 rows=153,268 width=90) (actual time=3,029.200..4,243.124 rows=19,460 loops=1)

  • Output: te_1_3.allocation_reference_id, cd1.code_value, te_1_3.transaction_id, te_1_3.id, te_1_3.isitc_asset_class_cd, te_1_3.transaction_event_id, te_1_3.isitc_instrument_type_cd, te_1_3.source_system_status_cd
  • Merge Cond: ((te_1_3.allocation_reference_id)::text = (te_2.allocation_reference_id)::text)
18. 2,670.577 3,283.509 ↓ 1.1 351,966 1

Sort (cost=115,763.66..116,530.00 rows=306,536 width=90) (actual time=2,286.375..3,283.509 rows=351,966 loops=1)

  • Output: te_1_3.transaction_id, te_1_3.id, te_1_3.isitc_asset_class_cd, te_1_3.transaction_event_id, te_1_3.isitc_instrument_type_cd, te_1_3.source_system_status_cd, te_1_3.allocation_reference_id, cd1.code_value
  • Sort Key: te_1_3.allocation_reference_id
  • Sort Method: external merge Disk: 36600kB
19. 90.403 612.932 ↓ 1.1 351,966 1

Hash Join (cost=2.71..72,111.00 rows=306,536 width=90) (actual time=0.196..612.932 rows=351,966 loops=1)

  • Output: te_1_3.transaction_id, te_1_3.id, te_1_3.isitc_asset_class_cd, te_1_3.transaction_event_id, te_1_3.isitc_instrument_type_cd, te_1_3.source_system_status_cd, te_1_3.allocation_reference_id, cd1.code_value
  • Hash Cond: ((te_1_3.source_system_status_cd)::text = (cd1.code_name)::text)
20. 522.437 522.437 ↓ 1.0 372,130 1

Seq Scan on tdm.trade_events te_1_3 (cost=0.00..67,345.48 rows=370,909 width=81) (actual time=0.092..522.437 rows=372,130 loops=1)

  • Output: te_1_3.transaction_id, te_1_3.id, te_1_3.isitc_asset_class_cd, te_1_3.transaction_event_id, te_1_3.isitc_instrument_type_cd, te_1_3.source_system_status_cd, te_1_3.allocation_reference_id
  • Filter: ((te_1_3.source_system_status_cd)::text <> ALL ('{CANCEL,SETTLED}'::text[]))
  • Rows Removed by Filter: 113242
21. 0.006 0.092 ↑ 1.0 10 1

Hash (cost=2.59..2.59 rows=10 width=29) (actual time=0.092..0.092 rows=10 loops=1)

  • Output: cd1.code_value, cd1.code_name, codes_2.code_name
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.018 0.086 ↑ 1.0 10 1

Hash Join (cost=1.35..2.59 rows=10 width=29) (actual time=0.079..0.086 rows=10 loops=1)

  • Output: cd1.code_value, cd1.code_name, codes_2.code_name
  • Hash Cond: ((cd1.code_name)::text = (codes_2.code_name)::text)
23. 0.021 0.021 ↑ 1.0 10 1

Seq Scan on tdm.codes cd1 (cost=0.00..1.10 rows=10 width=19) (actual time=0.019..0.021 rows=10 loops=1)

  • Output: cd1.id, cd1.code_name, cd1.code_value, cd1.group_name, cd1.group_order, cd1.created_at, cd1.updated_at
24. 0.004 0.047 ↑ 1.0 10 1

Hash (cost=1.23..1.23 rows=10 width=10) (actual time=0.047..0.047 rows=10 loops=1)

  • Output: codes_2.code_name
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.036 0.043 ↑ 1.0 10 1

HashAggregate (cost=1.12..1.23 rows=10 width=10) (actual time=0.040..0.043 rows=10 loops=1)

  • Output: codes_2.code_name
  • Group Key: (codes_2.code_name)::text
26. 0.007 0.007 ↑ 1.0 10 1

Seq Scan on tdm.codes codes_2 (cost=0.00..1.10 rows=10 width=10) (actual time=0.003..0.007 rows=10 loops=1)

  • Output: codes_2.code_name, codes_2.code_name
27. 12.186 804.812 ↓ 1.0 111,928 1

Materialize (cost=17,574.31..18,128.72 rows=110,882 width=98) (actual time=742.769..804.812 rows=111,928 loops=1)

  • Output: te_2.allocation_reference_id
28. 763.874 792.626 ↓ 1.0 111,928 1

Sort (cost=17,574.31..17,851.52 rows=110,882 width=98) (actual time=742.738..792.626 rows=111,928 loops=1)

  • Output: te_2.allocation_reference_id
  • Sort Key: te_2.allocation_reference_id
  • Sort Method: external merge Disk: 2624kB
29. 28.752 28.752 ↓ 1.0 111,928 1

CTE Scan on settled_sql te_2 (cost=0.00..2,217.64 rows=110,882 width=98) (actual time=0.011..28.752 rows=111,928 loops=1)

  • Output: te_2.allocation_reference_id
30.          

CTE match_status_sql

31. 22.654 3,485.871 ↓ 91.9 121,734 1

Append (cost=101,690.22..137,894.69 rows=1,325 width=41) (actual time=2,222.146..3,485.871 rows=121,734 loops=1)

32. 61.937 3,430.119 ↓ 91.8 121,560 1

Subquery Scan on *SELECT* 1 (cost=101,690.22..110,295.02 rows=1,324 width=41) (actual time=2,222.145..3,430.119 rows=121,560 loops=1)

  • Output: "*SELECT* 1".code_value, "*SELECT* 1".code_name, "*SELECT* 1".allocation_reference_id, "*SELECT* 1".rnk
  • Filter: ("*SELECT* 1".rnk = 1)
  • Rows Removed by Filter: 118652
33. 261.773 3,368.182 ↑ 1.1 240,212 1

WindowAgg (cost=101,690.22..106,985.48 rows=264,763 width=41) (actual time=2,222.132..3,368.182 rows=240,212 loops=1)

  • Output: cds.code_value, cds.code_name, ts.allocation_reference_id, row_number() OVER (?)
34. 2,271.718 3,106.409 ↑ 1.1 240,212 1

Sort (cost=101,690.22..102,352.13 rows=264,763 width=33) (actual time=2,222.124..3,106.409 rows=240,212 loops=1)

  • Output: cds.code_value, ts.allocation_reference_id, cds.code_name
  • Sort Key: ts.allocation_reference_id, cds.code_value
  • Sort Method: external merge Disk: 10792kB
35. 115.327 834.691 ↑ 1.1 240,212 1

Hash Join (cost=1.21..70,601.06 rows=264,763 width=33) (actual time=0.048..834.691 rows=240,212 loops=1)

  • Output: cds.code_value, ts.allocation_reference_id, cds.code_name
  • Hash Cond: ((ts.source_system_status_cd)::text = (cds.code_name)::text)
36. 719.346 719.346 ↑ 1.0 485,372 1

Seq Scan on tdm.trade_events ts (cost=0.00..66,131.98 rows=485,398 width=24) (actual time=0.019..719.346 rows=485,372 loops=1)

  • Output: ts.allocation_reference_id, ts.source_system_status_cd
37. 0.005 0.018 ↑ 1.0 6 1

Hash (cost=1.14..1.14 rows=6 width=19) (actual time=0.018..0.018 rows=6 loops=1)

  • Output: cds.code_value, cds.code_name
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 0.013 0.013 ↑ 1.0 6 1

Seq Scan on tdm.codes cds (cost=0.00..1.14 rows=6 width=19) (actual time=0.009..0.013 rows=6 loops=1)

  • Output: cds.code_value, cds.code_name
  • Filter: ((cds.code_value)::text = ANY ('{CONFIRMED,UNCONFIRMED,NOMATCHING}'::text[]))
  • Rows Removed by Filter: 4
39. 0.057 33.098 ↓ 174.0 174 1

Subquery Scan on *SELECT* 2_1 (cost=27,599.63..27,599.67 rows=1 width=86) (actual time=32.946..33.098 rows=174 loops=1)

  • Output: "*SELECT* 2_1"."varchar", "*SELECT* 2_1".varchar_1, "*SELECT* 2_1".allocation_reference_id, "*SELECT* 2_1".rnk
  • Filter: ("*SELECT* 2_1".rnk = 1)
40. 0.120 33.041 ↓ 174.0 174 1

WindowAgg (cost=27,599.63..27,599.66 rows=1 width=98) (actual time=32.915..33.041 rows=174 loops=1)

  • Output: 'CONFIRMED'::character varying, 'MANUALLY MATCHED'::character varying, ts1.allocation_reference_id, row_number() OVER (?), ts1.status_event_date_tm, ts1.id
41. 2.238 32.921 ↓ 174.0 174 1

Sort (cost=27,599.63..27,599.64 rows=1 width=26) (actual time=32.905..32.921 rows=174 loops=1)

  • Output: ts1.allocation_reference_id, ts1.status_event_date_tm, ts1.id
  • Sort Key: ts1.allocation_reference_id, ts1.status_event_date_tm DESC, ts1.id DESC
  • Sort Method: quicksort Memory: 38kB
42. 9.321 30.683 ↓ 174.0 174 1

Bitmap Heap Scan on tdm.trade_events ts1 (cost=3,231.96..27,599.62 rows=1 width=26) (actual time=22.918..30.683 rows=174 loops=1)

  • Output: ts1.allocation_reference_id, ts1.status_event_date_tm, ts1.id
  • Recheck Cond: (((ts1.isitc_asset_class_cd)::text = 'FX'::text) AND ((ts1.source_system_status_cd)::text = 'SETTLED'::text))
  • Filter: (((ts1.fx_strategy_cd)::text = 'ANDF'::text) AND ((ts1.open_in)::text = 'N'::text))
  • Rows Removed by Filter: 4806
  • Heap Blocks: exact=3000
43. 1.209 21.362 ↓ 0.0 0 1

BitmapAnd (cost=3,231.96..3,231.96 rows=8,977 width=0) (actual time=21.362..21.362 rows=0 loops=1)

44. 4.493 4.493 ↑ 1.1 36,517 1

Bitmap Index Scan on idx_isitc_asset_class_cd (cost=0.00..712.50 rows=38,411 width=0) (actual time=4.493..4.493 rows=36,517 loops=1)

  • Index Cond: ((ts1.isitc_asset_class_cd)::text = 'FX'::text)
45. 15.660 15.660 ↑ 1.0 112,457 1

Bitmap Index Scan on index_trade_events_on_source_system_status_cd (cost=0.00..2,519.21 rows=113,438 width=0) (actual time=15.660..15.660 rows=112,457 loops=1)

  • Index Cond: ((ts1.source_system_status_cd)::text = 'SETTLED'::text)
46.          

CTE cancel_sql

47. 2.598 2.598 ↑ 1.1 1,190 1

Index Scan using idx_msg_type_cd on tdm.trade_events te_3 (cost=0.42..265.19 rows=1,343 width=43) (actual time=0.102..2.598 rows=1,190 loops=1)

  • Output: te_3.allocation_reference_id, te_3.original_allocation_ref_id, te_3.created_at, te_3.updated_at
  • Index Cond: ((te_3.message_type_cd)::text = 'CANCEL'::text)
48.          

CTE ndf_cancel_sql

49. 1.346 1.346 ↓ 0.0 0 1

Index Scan using idx_msg_type_cd on tdm.trade_events te_4 (cost=0.42..278.62 rows=1 width=14) (actual time=1.346..1.346 rows=0 loops=1)

  • Output: te_4.allocation_reference_id
  • Index Cond: ((te_4.message_type_cd)::text = 'CANCEL'::text)
  • Filter: (((te_4.source_system_status_cd)::text = 'SETTLED'::text) AND ((te_4.isitc_asset_class_cd)::text = 'FX'::text) AND ((te_4.fx_strategy_cd)::text = 'ANDF'::text) AND ((te_4.open_in)::text = 'N'::text))
  • Rows Removed by Filter: 1190
50. 0.192 766,246.269 ↓ 81.0 162 1

Append (cost=100.42..3,360.62 rows=2 width=10,852) (actual time=71,203.439..766,246.269 rows=162 loops=1)

51. 19.880 761,924.089 ↓ 162.0 162 1

Nested Loop Semi Join (cost=100.42..3,135.13 rows=1 width=4,395) (actual time=71,203.433..761,924.089 rows=162 loops=1)

  • Output: te.id, te.message_id, te.client_cd, te.transaction_event_id, te.transaction_id, te.oms_reference_id, te.block_reference_id, nvl2((cs.allocation_reference_id)::text, ((te.allocation_reference_id)::text || 'C'::text), (te.allocation_reference_id)::text), te.original_reference_id, te.orig_oms_reference_id, te.rebook_in, te.oms_system_cd, te.account_cd, CASE WHEN ((te.source_system_status_cd)::text = 'SETTLED'::text) THEN nvl2((ncs.allocation_reference_id)::text, 'CANCEL'::text, 'NEW'::text) ELSE nvl2((cs.allocation_reference_id)::text, 'CANCEL'::text, 'NEW'::text) END, te.trade_desk_cd, te.trader_cd, te.trade_type_cd, te.trade_dt, te.settlement_dt, to_char(te.trade_execution_date_tm, 'mm/dd/yyyy hh24:mi:ss'::text), te.exchange_country_cd, te.client_exchange_cd, te.delivery_type_cd, te.trade_currency_cd, te.trade_pr, te.client_exec_broker_cd, te.exec_instructions_tx, te.instrument_type_cd, te.instrument_desc, te.ticker, te.cusip, te.sedol, te.isin, te.yellow_key, te.cross_account_cd, te.step_out_broker_cd, te.step_out_in, te.credit_broker_cd, CASE WHEN ((te.split_fl)::text = 'Y'::text) THEN te.nominal_am ELSE te.trade_qty END, te.gross_am, te.net_am, te.settlement_currency_cd, te.commission_am, te.commission_rt, te.commission_sharing_in, te.commission_legal_entity_cd, te.directed_broker_in, te.commission_reason_cd, te.commission_hard_soft_cd, CASE WHEN ((te.source_system_status_cd)::text <> ALL ('{RECEIVED,NOMATCHING,CANCEL}'::text[])) THEN '0'::numeric ELSE nvl(te.local_tax_am, '0'::numeric) END, te.local_fee_am, CASE WHEN ((te.source_system_status_cd)::text <> ALL ('{RECEIVED,NOMATCHING,CANCEL}'::text[])) THEN '0'::numeric ELSE nvl(te.stamp_duty_am, '0'::numeric) END, CASE WHEN ((te.source_system_status_cd)::text <> ALL ('{RECEIVED,NOMATCHING,CANCEL}'::text[])) THEN '0'::numeric ELSE nvl(te.levy_am, '0'::numeric) END, te.transaction_tax_am, te.other_charges_am, te.original_face_am, te.accrued_income_am, te.factor_rt, te.issue_country_cd, te.coupon_rt, te.yield_pc, te.trade_spread_am, te.maturity_dt, te.issue_dt, te.contract_multiplier_am, te.expiry_dt, te.strike_pr, te.lot_id, te.lot_size_am, te.premium_discount_am, te.ipo_in, te.ticket_fund_cd, te.yield_type_cd, te.account_group_cd, te.deposit_role_cd, te.maturity_am, te.deposit_taker_cd, te.clearing_house_cd, te.client_clearing_broker_cd, te.clearing_house_fee_am, te.clearing_house_fee_ccy_cd, te.clearing_broker_fee_am, te.clearing_broker_fee_ccy_cd, te.exchange_rt, te.value_dt, te.buy_currency_cd, te.buy_currency_am, te.sell_currency_cd, te.sell_currency_am, te.fx_strategy_cd, te.net_settlement_in, te.open_in, te.ndf_in, te.ndf_settlement_currency_cd, te.fixing_dt, te.final_close_in, te.gain_loss_cd, te.gain_loss_am, te.third_party_in, te.fx_indicator_range_cd, te.fx_indicator_range_tm, te.limit_pr, te.open_trade_id, te.reporting_jurisdication_tx, te.uti_prefix_cd, te.uti_id, te.swap_in, te.swap_link_id, te.allocation_cnt, te.status_source_system_cd, te.source_system_status_cd, msq.code_value, 'YES'::character varying, CASE WHEN ((te.source_system_status_cd)::text = 'SETTLED'::text) THEN nvl2((ncs.allocation_reference_id)::text, 'YES'::text, 'NO'::text) ELSE nvl2((cs.allocation_reference_id)::text, 'YES'::text, 'NO'::text) END, to_char(te.status_event_date_tm, 'mm/dd/yyyy hh24:mi:ss'::text), te.status_desc, te.receivers_bic_cd, te.senders_bic_cd, te.senders_nm, te.fx_instruction_cd, te.fx_instruction_ccy_cd, te.fx_instruction_cancel_in, te.exec_broker_account_cd, te.exec_broker_cd, te.exec_broker_nm, te.exec_broker_type_cd, te.clearing_broker_account_cd, te.clearing_broker_nm, te.clearing_broker_cd, te.clearing_broker_type_cd, te.account_type_cd, te.exchange_cd, ss.isitc_asset_class_cd, ss.isitc_instrument_type_cd, te.custodian_account_cd, te.place_of_settlement_cd, te.create_user_id, te.update_user_id, to_char((nvl2((cs.allocation_reference_id)::text, (cs.created_at)::text, (te.created_at)::text))::timestamp with time zone, 'mm/dd/yyyy hh24:mi:ss'::text), to_char((nvl2((cs.allocation_reference_id)::text, (cs.updated_at)::text, (te.updated_at)::text))::timestamp with time zone, 'mm/dd/yyyy hh24:mi:ss'::text), tm.third_party_account_cd, te.place_of_settlement_type_cd, nvl2((cs.allocation_reference_id)::text, (cs.original_allocation_ref_id)::text, NULL::text), te.sequence_nbr, te.block_net_am, te.block_quantity, te.block_commission_rt, te.block_required_ind, te.block_original_face_am, te.block_gross_am, te.block_accrued_income_am, te.block_commission_am, te.block_record_in, te.scope_of_operation, te.fund_manager, te.delivery_agent, te.delivery_agent_acct, te.delivery_agent_name, te.receiving_agent, te.receiving_agent_acct, te.receiving_agent_name, te.beneficiary_institution, te.beneficiary_name, te.option_type_cd, te.option_sub_type_cd, te.otc_fl, te.cls_in, te.counterparty_cd, te.counterparty_nm, CASE WHEN (((te.block_record_in)::text = 'Y'::text) AND ((te.source_system_status_cd)::text = ANY ('{TOL_MATCH,FORCE_MATCH,EXACT_MATCH,"MANUALLY MATCHED",NOMATCHING}'::text[]))) THEN 'CONFIRMED'::text WHEN ((te.block_record_in)::text = 'N'::text) THEN 'UNCONFIRMED'::text ELSE 'Null'::text END, te.nominal_am, te.parent_tckt_nbr, te.linked_allocation_reference_id, te.research_commission_am, te.research_commission_rt, te.split_fl, CASE WHEN (((te.source_system_status_cd)::text = ANY ('{RECEIVED,NOMATCHING,CANCEL}'::text[])) AND ((te.settlement_currency_cd)::text = 'USD'::text)) THEN nvl(te.transaction_tax_am, '0'::numeric) WHEN (((te.source_system_status_cd)::text = ANY ('{RECEIVED,NOMATCHING,CANCEL}'::text[])) AND ((te.settlement_currency_cd)::text <> 'USD'::text)) THEN '0'::numeric WHEN (((te.source_system_status_cd)::text <> ALL ('{RECEIVED,NOMATCHING,CANCEL}'::text[])) AND ((te.settlement_currency_cd)::text = 'USD'::text)) THEN nvl(te.local_fee_am, '0'::numeric) WHEN (((te.source_system_status_cd)::text <> ALL ('{RECEIVED,NOMATCHING,CANCEL}'::text[])) AND ((te.settlement_currency_cd)::text <> 'USD'::text)) THEN '0'::numeric ELSE '0'::numeric END, CASE WHEN (((te.source_system_status_cd)::text = ANY ('{RECEIVED,NOMATCHING,CANCEL}'::text[])) AND ((te.settlement_currency_cd)::text <> 'USD'::text)) THEN nvl(te.transaction_tax_am, '0'::numeric) WHEN (((te.source_system_status_cd)::text = ANY ('{RECEIVED,NOMATCHING,CANCEL}'::text[])) AND ((te.settlement_currency_cd)::text = 'USD'::text)) THEN '0'::numeric WHEN (((te.source_system_status_cd)::text <> ALL ('{RECEIVED,NOMATCHING,CANCEL}'::text[])) AND ((te.settlement_currency_cd)::text <> 'USD'::text)) THEN nvl(te.local_fee_am, '0'::numeric) WHEN (((te.source_system_status_cd)::text <> ALL ('{RECEIVED,NOMATCHING,CANCEL}'::text[])) AND ((te.settlement_currency_cd)::text = 'USD'::text)) THEN '0'::numeric ELSE '0'::numeric END, CASE WHEN ((te.source_system_status_cd)::text = ANY ('{RECEIVED,NOMATCHING,CANCEL}'::text[])) THEN ((((nvl(te.local_tax_am, '0'::numeric) + nvl(te.stamp_duty_am, '0'::numeric)) + nvl(te.other_charges_am, '0'::numeric)) + nvl(te.levy_am, '0'::numeric)) + nvl(te.local_fee_am, '0'::numeric)) WHEN (((te.source_system_status_cd)::text <> ALL ('{RECEIVED,NOMATCHING,CANCEL}'::text[])) AND ((te.settlement_currency_cd)::text = 'USD'::text)) THEN (nvl(te.local_tax_am, '0'::numeric) + nvl(te.other_charges_am, '0'::numeric)) WHEN (((te.source_system_status_cd)::text <> ALL ('{RECEIVED,NOMATCHING,CANCEL}'::text[])) AND ((te.settlement_currency_cd)::text <> 'USD'::text)) THEN ((nvl(te.local_tax_am, '0'::numeric) + nvl(te.other_charges_am, '0'::numeric)) + nvl(te.local_fee_am, '0'::numeric)) ELSE '0'::numeric END, CASE WHEN ((te.source_system_status_cd)::text = ANY ('{RECEIVED,NOMATCHING,CANCEL}'::text[])) THEN (nvl(te.stamp_duty_am, '0'::numeric) + nvl(te.transaction_tax_am, '0'::numeric)) WHEN ((te.source_system_status_cd)::text <> ALL ('{RECEIVED,NOMATCHING,CANCEL}'::text[])) THEN nvl(te.local_fee_am, '0'::numeric) ELSE '0'::numeric END, CASE WHEN ((te.source_system_status_cd)::text = ANY ('{RECEIVED,NOMATCHING,CANCEL}'::text[])) THEN (nvl(te.levy_am, '0'::numeric) + nvl(te.local_tax_am, '0'::numeric)) WHEN ((te.source_system_status_cd)::text <> ALL ('{RECEIVED,NOMATCHING,CANCEL}'::text[])) THEN '0'::numeric ELSE '0'::numeric END, CASE WHEN ((te.source_system_status_cd)::text = ANY ('{RECEIVED,NOMATCHING,CANCEL}'::text[])) THEN nvl(te.local_fee_am, '0'::numeric) WHEN ((te.source_system_status_cd)::text <> ALL ('{RECEIVED,NOMATCHING,CANCEL}'::text[])) THEN nvl(te.local_tax_am, '0'::numeric) ELSE '0'::numeric END, CASE WHEN ((te.source_system_status_cd)::text = ANY ('{RECEIVED,NOMATCHING,CANCEL}'::text[])) THEN nvl(te.other_charges_am, '0'::numeric) WHEN ((te.source_system_status_cd)::text <> ALL ('{RECEIVED,NOMATCHING,CANCEL}'::text[])) THEN nvl(te.other_charges_am, '0'::numeric) ELSE '0'::numeric END
  • Join Filter: ((te.source_system_status_cd)::text = (codes.code_name)::text)
  • Rows Removed by Join Filter: 972
52. 1.606 761,902.427 ↓ 162.0 162 1

Nested Loop Left Join (cost=100.42..3,133.62 rows=1 width=4,105) (actual time=71,203.143..761,902.427 rows=162 loops=1)

  • Output: te.id, te.message_id, te.client_cd, te.transaction_event_id, te.transaction_id, te.oms_reference_id, te.block_reference_id, te.allocation_reference_id, te.original_reference_id, te.orig_oms_reference_id, te.rebook_in, te.oms_system_cd, te.account_cd, te.source_system_status_cd, te.trade_desk_cd, te.trader_cd, te.trade_type_cd, te.trade_dt, te.settlement_dt, te.trade_execution_date_tm, te.exchange_country_cd, te.client_exchange_cd, te.delivery_type_cd, te.trade_currency_cd, te.trade_pr, te.client_exec_broker_cd, te.exec_instructions_tx, te.instrument_type_cd, te.instrument_desc, te.ticker, te.cusip, te.sedol, te.isin, te.yellow_key, te.cross_account_cd, te.step_out_broker_cd, te.step_out_in, te.credit_broker_cd, te.split_fl, te.nominal_am, te.trade_qty, te.gross_am, te.net_am, te.settlement_currency_cd, te.commission_am, te.commission_rt, te.commission_sharing_in, te.commission_legal_entity_cd, te.directed_broker_in, te.commission_reason_cd, te.commission_hard_soft_cd, te.local_tax_am, te.local_fee_am, te.stamp_duty_am, te.levy_am, te.transaction_tax_am, te.other_charges_am, te.original_face_am, te.accrued_income_am, te.factor_rt, te.issue_country_cd, te.coupon_rt, te.yield_pc, te.trade_spread_am, te.maturity_dt, te.issue_dt, te.contract_multiplier_am, te.expiry_dt, te.strike_pr, te.lot_id, te.lot_size_am, te.premium_discount_am, te.ipo_in, te.ticket_fund_cd, te.yield_type_cd, te.account_group_cd, te.deposit_role_cd, te.maturity_am, te.deposit_taker_cd, te.clearing_house_cd, te.client_clearing_broker_cd, te.clearing_house_fee_am, te.clearing_house_fee_ccy_cd, te.clearing_broker_fee_am, te.clearing_broker_fee_ccy_cd, te.exchange_rt, te.value_dt, te.buy_currency_cd, te.buy_currency_am, te.sell_currency_cd, te.sell_currency_am, te.fx_strategy_cd, te.net_settlement_in, te.open_in, te.ndf_in, te.ndf_settlement_currency_cd, te.fixing_dt, te.final_close_in, te.gain_loss_cd, te.gain_loss_am, te.third_party_in, te.fx_indicator_range_cd, te.fx_indicator_range_tm, te.limit_pr, te.open_trade_id, te.reporting_jurisdication_tx, te.uti_prefix_cd, te.uti_id, te.swap_in, te.swap_link_id, te.allocation_cnt, te.status_source_system_cd, te.status_event_date_tm, te.status_desc, te.receivers_bic_cd, te.senders_bic_cd, te.senders_nm, te.fx_instruction_cd, te.fx_instruction_ccy_cd, te.fx_instruction_cancel_in, te.exec_broker_account_cd, te.exec_broker_cd, te.exec_broker_nm, te.exec_broker_type_cd, te.clearing_broker_account_cd, te.clearing_broker_nm, te.clearing_broker_cd, te.clearing_broker_type_cd, te.account_type_cd, te.exchange_cd, te.custodian_account_cd, te.place_of_settlement_cd, te.create_user_id, te.update_user_id, te.created_at, te.updated_at, te.place_of_settlement_type_cd, te.sequence_nbr, te.block_net_am, te.block_quantity, te.block_commission_rt, te.block_required_ind, te.block_original_face_am, te.block_gross_am, te.block_accrued_income_am, te.block_commission_am, te.block_record_in, te.scope_of_operation, te.fund_manager, te.delivery_agent, te.delivery_agent_acct, te.delivery_agent_name, te.receiving_agent, te.receiving_agent_acct, te.receiving_agent_name, te.beneficiary_institution, te.beneficiary_name, te.option_type_cd, te.option_sub_type_cd, te.otc_fl, te.cls_in, te.counterparty_cd, te.counterparty_nm, te.parent_tckt_nbr, te.linked_allocation_reference_id, te.research_commission_am, te.research_commission_rt, ss.isitc_asset_class_cd, ss.isitc_instrument_type_cd, tm.third_party_account_cd, cs.allocation_reference_id, cs.created_at, cs.updated_at, cs.original_allocation_ref_id, ncs.allocation_reference_id, msq.code_value
  • Join Filter: ((ncs.allocation_reference_id)::text = (te.allocation_reference_id)::text)
  • Filter: (CASE WHEN ((te.source_system_status_cd)::text = 'SETTLED'::text) THEN nvl2((ncs.allocation_reference_id)::text, 'YES'::text, 'NO'::text) ELSE nvl2((cs.allocation_reference_id)::text, 'YES'::text, 'NO'::text) END = 'NO'::text)
  • Rows Removed by Filter: 48
53. 27.028 761,899.351 ↓ 210.0 210 1

Nested Loop Left Join (cost=100.42..3,133.57 rows=1 width=4,007) (actual time=71,201.759..761,899.351 rows=210 loops=1)

  • Output: te.id, te.message_id, te.client_cd, te.transaction_event_id, te.transaction_id, te.oms_reference_id, te.block_reference_id, te.allocation_reference_id, te.original_reference_id, te.orig_oms_reference_id, te.rebook_in, te.oms_system_cd, te.account_cd, te.source_system_status_cd, te.trade_desk_cd, te.trader_cd, te.trade_type_cd, te.trade_dt, te.settlement_dt, te.trade_execution_date_tm, te.exchange_country_cd, te.client_exchange_cd, te.delivery_type_cd, te.trade_currency_cd, te.trade_pr, te.client_exec_broker_cd, te.exec_instructions_tx, te.instrument_type_cd, te.instrument_desc, te.ticker, te.cusip, te.sedol, te.isin, te.yellow_key, te.cross_account_cd, te.step_out_broker_cd, te.step_out_in, te.credit_broker_cd, te.split_fl, te.nominal_am, te.trade_qty, te.gross_am, te.net_am, te.settlement_currency_cd, te.commission_am, te.commission_rt, te.commission_sharing_in, te.commission_legal_entity_cd, te.directed_broker_in, te.commission_reason_cd, te.commission_hard_soft_cd, te.local_tax_am, te.local_fee_am, te.stamp_duty_am, te.levy_am, te.transaction_tax_am, te.other_charges_am, te.original_face_am, te.accrued_income_am, te.factor_rt, te.issue_country_cd, te.coupon_rt, te.yield_pc, te.trade_spread_am, te.maturity_dt, te.issue_dt, te.contract_multiplier_am, te.expiry_dt, te.strike_pr, te.lot_id, te.lot_size_am, te.premium_discount_am, te.ipo_in, te.ticket_fund_cd, te.yield_type_cd, te.account_group_cd, te.deposit_role_cd, te.maturity_am, te.deposit_taker_cd, te.clearing_house_cd, te.client_clearing_broker_cd, te.clearing_house_fee_am, te.clearing_house_fee_ccy_cd, te.clearing_broker_fee_am, te.clearing_broker_fee_ccy_cd, te.exchange_rt, te.value_dt, te.buy_currency_cd, te.buy_currency_am, te.sell_currency_cd, te.sell_currency_am, te.fx_strategy_cd, te.net_settlement_in, te.open_in, te.ndf_in, te.ndf_settlement_currency_cd, te.fixing_dt, te.final_close_in, te.gain_loss_cd, te.gain_loss_am, te.third_party_in, te.fx_indicator_range_cd, te.fx_indicator_range_tm, te.limit_pr, te.open_trade_id, te.reporting_jurisdication_tx, te.uti_prefix_cd, te.uti_id, te.swap_in, te.swap_link_id, te.allocation_cnt, te.status_source_system_cd, te.status_event_date_tm, te.status_desc, te.receivers_bic_cd, te.senders_bic_cd, te.senders_nm, te.fx_instruction_cd, te.fx_instruction_ccy_cd, te.fx_instruction_cancel_in, te.exec_broker_account_cd, te.exec_broker_cd, te.exec_broker_nm, te.exec_broker_type_cd, te.clearing_broker_account_cd, te.clearing_broker_nm, te.clearing_broker_cd, te.clearing_broker_type_cd, te.account_type_cd, te.exchange_cd, te.custodian_account_cd, te.place_of_settlement_cd, te.create_user_id, te.update_user_id, te.created_at, te.updated_at, te.place_of_settlement_type_cd, te.sequence_nbr, te.block_net_am, te.block_quantity, te.block_commission_rt, te.block_required_ind, te.block_original_face_am, te.block_gross_am, te.block_accrued_income_am, te.block_commission_am, te.block_record_in, te.scope_of_operation, te.fund_manager, te.delivery_agent, te.delivery_agent_acct, te.delivery_agent_name, te.receiving_agent, te.receiving_agent_acct, te.receiving_agent_name, te.beneficiary_institution, te.beneficiary_name, te.option_type_cd, te.option_sub_type_cd, te.otc_fl, te.cls_in, te.counterparty_cd, te.counterparty_nm, te.parent_tckt_nbr, te.linked_allocation_reference_id, te.research_commission_am, te.research_commission_rt, ss.isitc_asset_class_cd, ss.isitc_instrument_type_cd, tm.third_party_account_cd, cs.allocation_reference_id, cs.created_at, cs.updated_at, cs.original_allocation_ref_id, msq.code_value
  • Join Filter: ((cs.original_allocation_ref_id)::text = (te.allocation_reference_id)::text)
  • Rows Removed by Join Filter: 221292
54. 5.794 761,848.143 ↓ 186.0 186 1

Nested Loop Left Join (cost=100.42..3,089.93 rows=1 width=3,795) (actual time=71,198.603..761,848.143 rows=186 loops=1)

  • Output: te.id, te.message_id, te.client_cd, te.transaction_event_id, te.transaction_id, te.oms_reference_id, te.block_reference_id, te.allocation_reference_id, te.original_reference_id, te.orig_oms_reference_id, te.rebook_in, te.oms_system_cd, te.account_cd, te.source_system_status_cd, te.trade_desk_cd, te.trader_cd, te.trade_type_cd, te.trade_dt, te.settlement_dt, te.trade_execution_date_tm, te.exchange_country_cd, te.client_exchange_cd, te.delivery_type_cd, te.trade_currency_cd, te.trade_pr, te.client_exec_broker_cd, te.exec_instructions_tx, te.instrument_type_cd, te.instrument_desc, te.ticker, te.cusip, te.sedol, te.isin, te.yellow_key, te.cross_account_cd, te.step_out_broker_cd, te.step_out_in, te.credit_broker_cd, te.split_fl, te.nominal_am, te.trade_qty, te.gross_am, te.net_am, te.settlement_currency_cd, te.commission_am, te.commission_rt, te.commission_sharing_in, te.commission_legal_entity_cd, te.directed_broker_in, te.commission_reason_cd, te.commission_hard_soft_cd, te.local_tax_am, te.local_fee_am, te.stamp_duty_am, te.levy_am, te.transaction_tax_am, te.other_charges_am, te.original_face_am, te.accrued_income_am, te.factor_rt, te.issue_country_cd, te.coupon_rt, te.yield_pc, te.trade_spread_am, te.maturity_dt, te.issue_dt, te.contract_multiplier_am, te.expiry_dt, te.strike_pr, te.lot_id, te.lot_size_am, te.premium_discount_am, te.ipo_in, te.ticket_fund_cd, te.yield_type_cd, te.account_group_cd, te.deposit_role_cd, te.maturity_am, te.deposit_taker_cd, te.clearing_house_cd, te.client_clearing_broker_cd, te.clearing_house_fee_am, te.clearing_house_fee_ccy_cd, te.clearing_broker_fee_am, te.clearing_broker_fee_ccy_cd, te.exchange_rt, te.value_dt, te.buy_currency_cd, te.buy_currency_am, te.sell_currency_cd, te.sell_currency_am, te.fx_strategy_cd, te.net_settlement_in, te.open_in, te.ndf_in, te.ndf_settlement_currency_cd, te.fixing_dt, te.final_close_in, te.gain_loss_cd, te.gain_loss_am, te.third_party_in, te.fx_indicator_range_cd, te.fx_indicator_range_tm, te.limit_pr, te.open_trade_id, te.reporting_jurisdication_tx, te.uti_prefix_cd, te.uti_id, te.swap_in, te.swap_link_id, te.allocation_cnt, te.status_source_system_cd, te.status_event_date_tm, te.status_desc, te.receivers_bic_cd, te.senders_bic_cd, te.senders_nm, te.fx_instruction_cd, te.fx_instruction_ccy_cd, te.fx_instruction_cancel_in, te.exec_broker_account_cd, te.exec_broker_cd, te.exec_broker_nm, te.exec_broker_type_cd, te.clearing_broker_account_cd, te.clearing_broker_nm, te.clearing_broker_cd, te.clearing_broker_type_cd, te.account_type_cd, te.exchange_cd, te.custodian_account_cd, te.place_of_settlement_cd, te.create_user_id, te.update_user_id, te.created_at, te.updated_at, te.place_of_settlement_type_cd, te.sequence_nbr, te.block_net_am, te.block_quantity, te.block_commission_rt, te.block_required_ind, te.block_original_face_am, te.block_gross_am, te.block_accrued_income_am, te.block_commission_am, te.block_record_in, te.scope_of_operation, te.fund_manager, te.delivery_agent, te.delivery_agent_acct, te.delivery_agent_name, te.receiving_agent, te.receiving_agent_acct, te.receiving_agent_name, te.beneficiary_institution, te.beneficiary_name, te.option_type_cd, te.option_sub_type_cd, te.otc_fl, te.cls_in, te.counterparty_cd, te.counterparty_nm, te.parent_tckt_nbr, te.linked_allocation_reference_id, te.research_commission_am, te.research_commission_rt, ss.isitc_asset_class_cd, ss.isitc_instrument_type_cd, tm.third_party_account_cd, msq.code_value
  • Join Filter: ((tm.account_id)::text = ((te.account_cd)::bpchar)::text)
  • Rows Removed by Join Filter: 6696
55. 10,348.780 761,839.373 ↓ 186.0 186 1

Nested Loop (cost=0.42..2,946.96 rows=1 width=3,737) (actual time=71,196.597..761,839.373 rows=186 loops=1)

  • Output: te.id, te.message_id, te.client_cd, te.transaction_event_id, te.transaction_id, te.oms_reference_id, te.block_reference_id, te.allocation_reference_id, te.original_reference_id, te.orig_oms_reference_id, te.rebook_in, te.oms_system_cd, te.account_cd, te.source_system_status_cd, te.trade_desk_cd, te.trader_cd, te.trade_type_cd, te.trade_dt, te.settlement_dt, te.trade_execution_date_tm, te.exchange_country_cd, te.client_exchange_cd, te.delivery_type_cd, te.trade_currency_cd, te.trade_pr, te.client_exec_broker_cd, te.exec_instructions_tx, te.instrument_type_cd, te.instrument_desc, te.ticker, te.cusip, te.sedol, te.isin, te.yellow_key, te.cross_account_cd, te.step_out_broker_cd, te.step_out_in, te.credit_broker_cd, te.split_fl, te.nominal_am, te.trade_qty, te.gross_am, te.net_am, te.settlement_currency_cd, te.commission_am, te.commission_rt, te.commission_sharing_in, te.commission_legal_entity_cd, te.directed_broker_in, te.commission_reason_cd, te.commission_hard_soft_cd, te.local_tax_am, te.local_fee_am, te.stamp_duty_am, te.levy_am, te.transaction_tax_am, te.other_charges_am, te.original_face_am, te.accrued_income_am, te.factor_rt, te.issue_country_cd, te.coupon_rt, te.yield_pc, te.trade_spread_am, te.maturity_dt, te.issue_dt, te.contract_multiplier_am, te.expiry_dt, te.strike_pr, te.lot_id, te.lot_size_am, te.premium_discount_am, te.ipo_in, te.ticket_fund_cd, te.yield_type_cd, te.account_group_cd, te.deposit_role_cd, te.maturity_am, te.deposit_taker_cd, te.clearing_house_cd, te.client_clearing_broker_cd, te.clearing_house_fee_am, te.clearing_house_fee_ccy_cd, te.clearing_broker_fee_am, te.clearing_broker_fee_ccy_cd, te.exchange_rt, te.value_dt, te.buy_currency_cd, te.buy_currency_am, te.sell_currency_cd, te.sell_currency_am, te.fx_strategy_cd, te.net_settlement_in, te.open_in, te.ndf_in, te.ndf_settlement_currency_cd, te.fixing_dt, te.final_close_in, te.gain_loss_cd, te.gain_loss_am, te.third_party_in, te.fx_indicator_range_cd, te.fx_indicator_range_tm, te.limit_pr, te.open_trade_id, te.reporting_jurisdication_tx, te.uti_prefix_cd, te.uti_id, te.swap_in, te.swap_link_id, te.allocation_cnt, te.status_source_system_cd, te.status_event_date_tm, te.status_desc, te.receivers_bic_cd, te.senders_bic_cd, te.senders_nm, te.fx_instruction_cd, te.fx_instruction_ccy_cd, te.fx_instruction_cancel_in, te.exec_broker_account_cd, te.exec_broker_cd, te.exec_broker_nm, te.exec_broker_type_cd, te.clearing_broker_account_cd, te.clearing_broker_nm, te.clearing_broker_cd, te.clearing_broker_type_cd, te.account_type_cd, te.exchange_cd, te.custodian_account_cd, te.place_of_settlement_cd, te.create_user_id, te.update_user_id, te.created_at, te.updated_at, te.place_of_settlement_type_cd, te.sequence_nbr, te.block_net_am, te.block_quantity, te.block_commission_rt, te.block_required_ind, te.block_original_face_am, te.block_gross_am, te.block_accrued_income_am, te.block_commission_am, te.block_record_in, te.scope_of_operation, te.fund_manager, te.delivery_agent, te.delivery_agent_acct, te.delivery_agent_name, te.receiving_agent, te.receiving_agent_acct, te.receiving_agent_name, te.beneficiary_institution, te.beneficiary_name, te.option_type_cd, te.option_sub_type_cd, te.otc_fl, te.cls_in, te.counterparty_cd, te.counterparty_nm, te.parent_tckt_nbr, te.linked_allocation_reference_id, te.research_commission_am, te.research_commission_rt, ss.isitc_asset_class_cd, ss.isitc_instrument_type_cd, msq.code_value
  • Join Filter: (((te.transaction_id)::text = (ss.transaction_id)::text) AND ((te.transaction_event_id)::text = (ss.transaction_event_id)::text))
  • Rows Removed by Join Filter: 94198738
56. 236.312 9,494.389 ↓ 47,914.0 47,914 1

Nested Loop (cost=0.42..443.81 rows=1 width=3,601) (actual time=2,223.488..9,494.389 rows=47,914 loops=1)

  • Output: te.id, te.message_id, te.client_cd, te.transaction_event_id, te.transaction_id, te.oms_reference_id, te.block_reference_id, te.allocation_reference_id, te.original_reference_id, te.orig_oms_reference_id, te.rebook_in, te.oms_system_cd, te.account_cd, te.source_system_status_cd, te.trade_desk_cd, te.trader_cd, te.trade_type_cd, te.trade_dt, te.settlement_dt, te.trade_execution_date_tm, te.exchange_country_cd, te.client_exchange_cd, te.delivery_type_cd, te.trade_currency_cd, te.trade_pr, te.client_exec_broker_cd, te.exec_instructions_tx, te.instrument_type_cd, te.instrument_desc, te.ticker, te.cusip, te.sedol, te.isin, te.yellow_key, te.cross_account_cd, te.step_out_broker_cd, te.step_out_in, te.credit_broker_cd, te.split_fl, te.nominal_am, te.trade_qty, te.gross_am, te.net_am, te.settlement_currency_cd, te.commission_am, te.commission_rt, te.commission_sharing_in, te.commission_legal_entity_cd, te.directed_broker_in, te.commission_reason_cd, te.commission_hard_soft_cd, te.local_tax_am, te.local_fee_am, te.stamp_duty_am, te.levy_am, te.transaction_tax_am, te.other_charges_am, te.original_face_am, te.accrued_income_am, te.factor_rt, te.issue_country_cd, te.coupon_rt, te.yield_pc, te.trade_spread_am, te.maturity_dt, te.issue_dt, te.contract_multiplier_am, te.expiry_dt, te.strike_pr, te.lot_id, te.lot_size_am, te.premium_discount_am, te.ipo_in, te.ticket_fund_cd, te.yield_type_cd, te.account_group_cd, te.deposit_role_cd, te.maturity_am, te.deposit_taker_cd, te.clearing_house_cd, te.client_clearing_broker_cd, te.clearing_house_fee_am, te.clearing_house_fee_ccy_cd, te.clearing_broker_fee_am, te.clearing_broker_fee_ccy_cd, te.exchange_rt, te.value_dt, te.buy_currency_cd, te.buy_currency_am, te.sell_currency_cd, te.sell_currency_am, te.fx_strategy_cd, te.net_settlement_in, te.open_in, te.ndf_in, te.ndf_settlement_currency_cd, te.fixing_dt, te.final_close_in, te.gain_loss_cd, te.gain_loss_am, te.third_party_in, te.fx_indicator_range_cd, te.fx_indicator_range_tm, te.limit_pr, te.open_trade_id, te.reporting_jurisdication_tx, te.uti_prefix_cd, te.uti_id, te.swap_in, te.swap_link_id, te.allocation_cnt, te.status_source_system_cd, te.status_event_date_tm, te.status_desc, te.receivers_bic_cd, te.senders_bic_cd, te.senders_nm, te.fx_instruction_cd, te.fx_instruction_ccy_cd, te.fx_instruction_cancel_in, te.exec_broker_account_cd, te.exec_broker_cd, te.exec_broker_nm, te.exec_broker_type_cd, te.clearing_broker_account_cd, te.clearing_broker_nm, te.clearing_broker_cd, te.clearing_broker_type_cd, te.account_type_cd, te.exchange_cd, te.custodian_account_cd, te.place_of_settlement_cd, te.create_user_id, te.update_user_id, te.created_at, te.updated_at, te.place_of_settlement_type_cd, te.sequence_nbr, te.block_net_am, te.block_quantity, te.block_commission_rt, te.block_required_ind, te.block_original_face_am, te.block_gross_am, te.block_accrued_income_am, te.block_commission_am, te.block_record_in, te.scope_of_operation, te.fund_manager, te.delivery_agent, te.delivery_agent_acct, te.delivery_agent_name, te.receiving_agent, te.receiving_agent_acct, te.receiving_agent_name, te.beneficiary_institution, te.beneficiary_name, te.option_type_cd, te.option_sub_type_cd, te.otc_fl, te.cls_in, te.counterparty_cd, te.counterparty_nm, te.parent_tckt_nbr, te.linked_allocation_reference_id, te.research_commission_am, te.research_commission_rt, msq.code_value
57. 3,658.313 3,658.313 ↓ 6,086.7 121,734 1

CTE Scan on match_status_sql msq (cost=0.00..31.47 rows=20 width=130) (actual time=2,222.160..3,658.313 rows=121,734 loops=1)

  • Output: msq.code_value, msq.code_name, msq.allocation_reference_id, msq.rnk
  • Filter: ((msq.code_value)::text = ANY ('{UNCONFIRMED,CONFIRMED,NOMATCHING}'::text[]))
58. 5,599.764