explain.depesz.com

PostgreSQL's explain analyze made readable

Result: K5lo : trade_event

Settings
# exclusive inclusive rows x rows loops node
1. 2,778.093 44,905.002 ↓ 30.4 155,652 1

Hash Left Join (cost=2,240,756.39..2,267,819.70 rows=5,114 width=1,809) (actual time=29,196.446..44,905.002 rows=155,652 loops=1)

  • Output: trade_events.id, trade_events.block_gross_amount, trade_events.block_net_amount, trade_events.block_commission_am, trade_events.allocation_quantity, trade_events.allocation_gross_amount, trade_events.allocation_net_amount, trade_events.commission_am, trade_events.exchange_rate, trade_events.currency_purchased_amount, trade_events.currency_sold_amount, trade_events.transaction_id, COALESCE(CASE WHEN ((trade_events.instrument_type_cd)::text = 'EQS'::text) THEN trade_events.message_id ELSE trade_events.sender_ref END, CASE WHEN ((trade_events.entity_type_cd)::text = 'OTCTrade'::text) THEN trade_events.message_id ELSE trade_events.message_group_id END), COALESCE(trade_events.trade_date, to_date("left"(COALESCE(NULLIF((trade_events.trade_date_time)::text, ''::text), (tm.trade_date_time)::text), 8), 'YYYYMMDD'::text)), trade_events.sent_by, 'USA'::text, CASE WHEN ((trade_events.block_record)::text = 'Y'::text) THEN 'Block-no-safe'::text WHEN ((trade_events.instrument_type_cd)::text = 'EQS'::text) THEN (trade_events.portfolio)::text ELSE COALESCE(NULLIF((trade_events.account_code)::text, ''::text), (tm.account_code)::text) END, CASE WHEN ((trade_events.instrument_type_cd)::text = 'EQS'::text) THEN (trade_events.instrument_type_cd)::text ELSE COALESCE(NULLIF((trade_events.instrument_type)::text, ''::text), (tm.instrument_type)::text) END, CASE WHEN ((trade_events.instrument_type_cd)::text = 'EQS'::text) THEN (trade_events.product_subtype)::text ELSE COALESCE(NULLIF((trade_events.description)::text, ''::text), (tm.description)::text) END, trade_events.id4, 'SEDOL'::text, CASE WHEN ((trade_events.instrument_type_cd)::text = 'EQS'::text) THEN trade_events.message_id ELSE trade_events.sender_ref END, CASE WHEN ((trade_events.instrument_type_cd)::text = 'EQS'::text) THEN (trade_events.id4)::text ELSE COALESCE(NULLIF((trade_events.sedol)::text, ''::text), (tm.sedol)::text) END, COALESCE(NULLIF((tm.block_reference)::text, ''::text), (trade_events.block_reference)::text), trade_events.message_group_id, COALESCE(NULLIF((tm.asset_class)::text, ''::text), (trade_events.asset_class)::text), trade_events.orig_oms_reference_id, COALESCE(NULLIF((trade_events.cusip)::text, ''::text), (tm.cusip)::text), COALESCE(NULLIF((trade_events.isin)::text, ''::text), (tm.isin)::text), COALESCE(NULLIF((trade_events.ticker)::text, ''::text), (tm.ticker)::text), ''::text, CASE WHEN ((trade_events.instrument_type_cd)::text = 'EQS'::text) THEN (trade_events.trade_id)::text ELSE COALESCE(NULLIF((trade_events.transaction_id)::text, ''::text), (tm.transaction_id)::text) END, CASE WHEN ((trade_events.transaction_type)::text = 'ContractCreated'::text) THEN (trade_events.upfront_amount)::text WHEN ((trade_events.transaction_type)::text = 'ContractPartialTermination'::text) THEN (trade_events.early_termination_amt)::text ELSE CASE WHEN ((tm.instrument_type_cd)::text = ANY ('{CURR,CFWD,FXRATE}'::text[])) THEN COALESCE(NULLIF((trade_events.currency_purchased_amount)::text, ''::text), (tm.currency_purchased_amount)::text) WHEN ((trade_events.block_record)::text = 'Y'::text) THEN COALESCE(NULLIF((trade_events.block_net_amount)::text, ''::text), (tm.block_net_amount)::text) WHEN ((trade_events.block_record)::text <> 'Y'::text) THEN COALESCE(NULLIF((trade_events.allocation_net_amount)::text, ''::text), (tm.allocation_net_amount)::text) ELSE NULL::text END END, CASE WHEN ((trade_events.instrument_type_cd)::text = 'EQS'::text) THEN 'Received'::character varying WHEN (((trade_events.msg_type)::text = 'CANCEL'::text) AND ((trade_events.service_nm)::text = 'BAAS'::text) AND ((trade_events.block_required_ind)::text = 'N'::text) AND ((trade_events.block_record)::text = 'N'::text)) THEN 'TMV-Captured'::character varying WHEN (((trade_events.msg_type)::text = 'CANCEL'::text) AND ((trade_events.service_nm)::text = 'BAAS'::text) AND ((trade_events.trade_status)::text <> 'TRP-NAK'::text)) THEN 'TMV-BlockCancelled'::character varying ELSE trade_events.trade_status END, COALESCE(trade_events.trade_date, to_date("left"(COALESCE(NULLIF((trade_events.trade_date_time)::text, ''::text), (tm.trade_date_time)::text), 8), 'YYYYMMDD'::text)), COALESCE(NULLIF((trade_events.swap_currency)::text, ''::text), NULLIF((trade_events.trade_currency)::text, ''::text), (tm.trade_currency)::text), CASE WHEN ((trade_events.instrument_type_cd)::text = 'EQS'::text) THEN (trade_events.notional)::text ELSE CASE WHEN ((tm.instrument_type_cd)::text = ANY ('{CURR,CFWD,FXRATE}'::text[])) THEN COALESCE(NULLIF((trade_events.currency_purchased_amount)::text, ''::text), (tm.currency_purchased_amount)::text) WHEN ((trade_events.block_record)::text = 'Y'::text) THEN COALESCE(NULLIF((trade_events.block_net_amount)::text, ''::text), (tm.block_net_amount)::text) WHEN ((trade_events.block_record)::text <> 'Y'::text) THEN COALESCE(NULLIF((trade_events.allocation_net_amount)::text, ''::text), (tm.allocation_net_amount)::text) ELSE NULL::text END END, CASE WHEN ((tm.instrument_type_cd)::text = ANY ('{CURR,CFWD,FXRATE}'::text[])) THEN COALESCE(NULLIF((trade_events.currency_sold_amount)::text, ''::text), (tm.currency_sold_amount)::text) WHEN (COALESCE(NULLIF((trade_events.block_record)::text, ''::text), (tm.block_record)::text) = 'Y'::text) THEN COALESCE(NULLIF((trade_events.units)::text, ''::text), NULLIF((tm.units)::text, ''::text), NULLIF((trade_events.block_quantity)::text, ''::text), NULLIF((tm.block_quantity)::text, ''::text)) ELSE COALESCE(NULLIF((trade_events.units)::text, ''::text), NULLIF((tm.units)::text, ''::text), NULLIF((trade_events.allocation_quantity)::text, ''::text), (tm.allocation_quantity)::text) END, COALESCE(NULLIF((trade_events.swap_return_leg_underlying_id)::text, ''::text), (tm.swap_return_leg_underlying_id)::text), trade_events.msg_type, NULLIF(COALESCE(NULLIF((trade_events.trade_desk)::text, ''::text), (tm.trade_desk)::text), ''::text), ''::text, CASE WHEN ((trade_events.instrument_type_cd)::text = 'EQS'::text) THEN (trade_events.broker)::text ELSE COALESCE(NULLIF((trade_events.exec_broker_cd)::text, ''::text), (tm.exec_broker_cd)::text) END, COALESCE(NULLIF((trade_events.exec_broker)::text, ''::text), (tm.exec_broker)::text), COALESCE(NULLIF((trade_events.clearing_broker_cd)::text, ''::text), (tm.clearing_broker_cd)::text), CASE WHEN ((trade_events.instrument_type_cd)::text = 'EQS'::text) THEN NULL::text ELSE CASE WHEN ((trade_events.block_record)::text = 'Y'::text) THEN COALESCE(NULLIF((trade_events.block_commission_am)::text, ''::text), (tm.block_commission_am)::text) WHEN ((trade_events.block_record)::text <> 'Y'::text) THEN COALESCE(NULLIF((trade_events.commission_am)::text, ''::text), (tm.commission_am)::text) ELSE NULL::text END END, trade_events.sent_by, trade_events.client_cd, tm.long_short_cd, trade_events.xref_deliver_time, trade_events.trade_id, trade_events.receive_tm, ''::text, COALESCE(NULLIF((tm.transaction_type)::text, ''::text), (trade_events.transaction_type)::text), COALESCE(NULLIF((trade_events.exchange_country)::text, ''::text), (tm.exchange_country)::text), to_number(COALESCE(NULLIF(CASE WHEN ((tm.instrument_type_cd)::text = ANY ('{CURR,CFWD,FXRATE}'::text[])) THEN COALESCE(NULLIF((trade_events.exchange_rate)::text, ''::text), (tm.exchange_rate)::text) ELSE COALESCE(NULLIF((trade_events.price)::text, ''::text), (tm.price)::text) END, ''::text), '0'::text), '999999999999.99'::text), CASE WHEN (((tm.settlement_date)::text = ''::text) OR (tm.settlement_date IS NULL) OR (length((tm.settlement_date)::text) < 8)) THEN NULL::date ELSE to_date("left"((tm.settlement_date)::text, 8), 'YYYYMMDD'::text) END, CASE WHEN ((trade_events.instrument_type_cd)::text = 'EQS'::text) THEN ''::character varying WHEN (COALESCE(NULLIF((trade_events.block_record)::text, ''::text), (tm.block_record)::text) = 'Y'::text) THEN 'Block'::character varying ELSE 'Allocation'::character varying END, tm.msg_type, trade_events.entity_type_cd, cx.comment, cx.commented_by, CASE WHEN (((cx.comment)::text = ''::text) OR (cx.comment IS NULL)) THEN NULL::timestamp with time zone ELSE nvl((cx.commented_at)::timestamp with time zone, (cx.created_at)::timestamp with time zone) END, COALESCE(NULLIF((trade_events.exec_broker_type_cd)::text, ''::text), (tm.exec_broker_type_cd)::text), trade_events.block_required_ind, (date_part('epoch'::text, now()))::integer
  • Hash Cond: (((CASE WHEN ((trade_events.instrument_type_cd)::text = 'EQS'::text) THEN 'Received'::character varying WHEN (((trade_events.msg_type)::text = 'CANCEL'::text) AND ((trade_events.service_nm)::text = 'BAAS'::text) AND ((trade_events.block_required_ind)::text = 'N'::text) AND ((trade_events.block_record)::text = 'N'::text)) THEN 'TMV-Captured'::character varying WHEN (((trade_events.msg_type)::text = 'CANCEL'::text) AND ((trade_events.service_nm)::text = 'BAAS'::text)) THEN 'TMV-BlockCancelled'::character varying ELSE trade_events.trade_status END)::text = (cx.trade_status)::text) AND ((trade_events.transaction_id)::text = (cx.transaction_id)::text))
2.          

CTE trade_eve

3. 217.898 37,207.490 ↓ 30.4 155,652 1

Subquery Scan on t (cost=2,196,879.58..2,240,348.16 rows=5,114 width=1,315) (actual time=29,172.803..37,207.490 rows=155,652 loops=1)

  • Output: t.id, t.message_group_id, t.transaction_id, t.payload_type_code, t.client_cd, t.source_system_cd, t.client_ref_id, t.instrument_type_cd, t.tran_type_cd, t.service_nm, t.entity_type_cd, t.event_class_cd, t.event_lifecycle_cd, t.transaction_type, t.conversation_id, t.message_id, t.trade_id, t.sent_by, t.send_to, t.id2, t.id3, t.id4, t.id5, t.trade_date, t.client_trade_direction, t.upfront_amount, t.return_leg_effective_date, t.return_leg_final_valuation_date, t.return_leg_maturity_date, t.finance_leg_effective_date, t.finance_leg_maturity_date, t.swap_final_payment_date, t.spread, t.swap_return_leg_underlying_id, t.swap_finance_leg_underlying_id, t.swap_finance_leg_interest_tenor, t.product_type, t.product_subtype, t.price_determination_method, t.units, t.price, t.notional, t.finance_leg_currency, t.return_leg_currency, t.swap_currency, t.business_day_convention, t.finance_leg_business_center, t.return_leg_business_center, t.im_amount, t.portfolio, t.roll_convention, t.notional_reset, t.day_count_fraction, t.reset_frequency, t.settlement_date_lag_days, t.broker, t.executing_broker, t.receive_tm, t.created_at, t.updated_at, t.early_termination_amt, t.xref_deliver_time, t.instrument_type, t.cleared_deal_id, t.gtm_id, t.apx_id, t.accounting_id, t.usi_id, t.upi_id, t.xef_id, t.id1, t.id6, t.id7, t.id8, t.oms_id, t.cl_security_id, t.upfront_direction, t.upfront_settlement_currency, t.stub_rate, t.finance_leg_underlying_id, t.finance_leg_interest_tenor, t.client_im_direction, t.im_calculation, t.transferor, t.transferee, t.remaining_party, t.novated_amount, t.novated_currency, t.payment_direction, t.early_term_return_leg_price, t.early_term_breakage_fee, t.currency, t.change_in_number_of_units, t.outstanding_number_of_units, t.change_in_number_of_notional, t.outstanding_notional_amount, t.upfront_settlement_date, t.return_leg_final_value_date, t.stub_rate_start_date, t.stub_rate_end_date, t.novation_date, t.novation_contract_date, t.payment_date, t.effective_date, t.sef_id, t.comments, t.commented_by, t.commented_at, t.trp_link_id, t.oms, t.trade_desk, t.trader, t.msg_type, t.message_type, t.trade_type, t.trade_date_time, t.settlement_date, t.trans_date_time, t.exchange_country, t.exchange, t.oms_reference, t.asset_class, t.block_reference, t.block_quantity, t.trade_currency, t.block_gross_amount, t.block_net_amount, t.block_accrued_income_amount, t.exec_broker, t.block_step_out, t.block_commission_type_code, t.block_commission_reason_code, t.block_commission_hard_soft, t.description, t.cusip, t.sedol, t.allocation_count, t.allocation_reference, t.account_code, t.allocation_quantity, t.allocation_gross_amount, t.allocation_net_amount, t.settlement_currency, t.allocation_commission_indicator, t.allocation_commission_sharing, t.allocation_directed_broker_ind, t.allocation_commission_reason_code, t.allocation_commission_hard_soft, t.allocation_accrued_income_amount, t.country_of_issue, t.coupon_rate, t.maturity_date, t.exchange_rate, t.value_date, t.currency_purchased, t.currency_purchased_amount, t.currency_sold, t.currency_sold_amount, t.fx_strategy_code, t.ipo_indicator, t.nb_shrs, t.order_type, t.placement_code, t.trade_venue, t.rebook_indicator, t.deposit_role, t.net_settlement_indicator, t.open_indicator, t.ndf_indicator, t.fixing_date, t.final_close_indicator, t.fx_forward_pts, t.fx_indicator_range, t.fx_indicator_range_time, t.fx_session, t.fx_source_system, t.fx_spot_price, t.leg_indicator, t.tlm_basket_id, t.clearing_flag, t.sef_flag, t.reporting_jurisdiction, t.uti_prefix, t.uti, t.swap_flag, t.swap_link_identifier, t.yellow_key, t.net_trd_ind, t.exec_drty_price, t.acct_major_cd, t.original_transaction_id, t.block_record, t.account_id, t.trade_status, t.exec_broker_cd, t.sender_ref, t.isin, t.ticker, t.account_type_cd, t.block_commission_am, t.block_commission_rt, t.block_original_face_am, t.block_required_ind, t.bloomberg_id, t.broker_of_credit_cd, t.broker_of_credit_type_cd, t.buy_ccy_instrument_id, t.call_put_cd, t.clearing_broker_account_cd, t.clearing_broker_cd, t.clearing_broker_nm, t.clearing_broker_type_cd, t.client_id, t.client_instrument_id, t.client_instrument_id_in, t.client_proprietary, t.commission_am, t.commission_legal_entity_cd, t.commission_rt, t.contract_multiplier_am, t.cross_account_cd, t.custodian_account_cd, t.delivery_type_cd, t.exec_broker_account_cd, t.exec_broker_nm, t.exec_broker_type_cd, t.exec_instructions_tx, t.execution_commission_am, t.execution_commission_rt, t.expiry_dt, t.factor_rt, t.fx_back_to_back_broker_cd, t.fx_back_to_back_broker_type_cd, t.instrument_alternate_ids, t.issue_dt, t.levy_am, t.local_fee_am, t.local_tax_am, t.lot_id, t.lot_size_am, t.mortgage_fcta_in, t.mortgage_pair_off_in, t.mortgage_pool_no, t.mortgage_tbac_in, t.mortgage_turn_in, t.mt_type_cd, t.ndf_settlement_currency_cd, t.number_of_shares_ct, t.occ_id, t.open_trade_id, t.option_style_cd, t.option_sub_type_cd, t.option_type_cd, t.orig_oms_reference_id, t.original_allocation_reference_id, t.original_face_am, t.original_reference_id, t.other_charges_am, t.place_of_safe_keeping_cd, t.place_of_settlement_cd, t.premium_discount_am, t.primary_instrument_id, t.primary_instrument_id_type_cd, t.processing_region_cd, t.receivers_cd, t.repo_deal_ref_id, t.repo_method_of_interest_cd, t.repo_rate_type_cd, t.repo_termination_dt, t.repo_tran_type_cd, t.research_commission_am, t.research_commission_rt, t.sell_ccy_instrument_id, t.senders_cd, t.senders_nm, t.sequence_nbr, t.settlement_instrument_id, t.source_system_status_cd, t.stamp_duty_am, t.status_source_system_cd, t.step_out_broker_cd, t.step_out_broker_type_cd, t.strike_pr, t.syndicate_broker, t.syndicate_broker_cd, t.traded_instrument_id, t.transaction_event_id, t.transaction_tax_am, t.underlying_instrument_alternate_ids, t.underlying_instrument_desc, t.unitized_bond_fl, t.yield_pc, t.long_short_cd, t.broker_of_credit_code_type, t.allocation_transaction_id, t.cls, t.receive_t, t.rn
  • Filter: (t.rn = 1)
  • Rows Removed by Filter: 809456
4. 3,314.052 36,989.592 ↑ 1.1 965,108 1

WindowAgg (cost=2,196,879.58..2,227,563.28 rows=1,022,790 width=1,347) (actual time=29,172.769..36,989.592 rows=965,108 loops=1)

  • Output: trade_events_1.id, trade_events_1.message_group_id, trade_events_1.transaction_id, trade_events_1.payload_type_code, trade_events_1.client_cd, trade_events_1.source_system_cd, trade_events_1.client_ref_id, trade_events_1.instrument_type_cd, trade_events_1.tran_type_cd, trade_events_1.service_nm, trade_events_1.entity_type_cd, trade_events_1.event_class_cd, trade_events_1.event_lifecycle_cd, trade_events_1.transaction_type, trade_events_1.conversation_id, trade_events_1.message_id, trade_events_1.trade_id, trade_events_1.sent_by, trade_events_1.send_to, trade_events_1.id2, trade_events_1.id3, trade_events_1.id4, trade_events_1.id5, trade_events_1.trade_date, trade_events_1.client_trade_direction, trade_events_1.upfront_amount, trade_events_1.return_leg_effective_date, trade_events_1.return_leg_final_valuation_date, trade_events_1.return_leg_maturity_date, trade_events_1.finance_leg_effective_date, trade_events_1.finance_leg_maturity_date, trade_events_1.swap_final_payment_date, trade_events_1.spread, trade_events_1.swap_return_leg_underlying_id, trade_events_1.swap_finance_leg_underlying_id, trade_events_1.swap_finance_leg_interest_tenor, trade_events_1.product_type, trade_events_1.product_subtype, trade_events_1.price_determination_method, trade_events_1.units, trade_events_1.price, trade_events_1.notional, trade_events_1.finance_leg_currency, trade_events_1.return_leg_currency, trade_events_1.swap_currency, trade_events_1.business_day_convention, trade_events_1.finance_leg_business_center, trade_events_1.return_leg_business_center, trade_events_1.im_amount, trade_events_1.portfolio, trade_events_1.roll_convention, trade_events_1.notional_reset, trade_events_1.day_count_fraction, trade_events_1.reset_frequency, trade_events_1.settlement_date_lag_days, trade_events_1.broker, trade_events_1.executing_broker, trade_events_1.receive_tm, trade_events_1.created_at, trade_events_1.updated_at, trade_events_1.early_termination_amt, trade_events_1.xref_deliver_time, trade_events_1.instrument_type, trade_events_1.cleared_deal_id, trade_events_1.gtm_id, trade_events_1.apx_id, trade_events_1.accounting_id, trade_events_1.usi_id, trade_events_1.upi_id, trade_events_1.xef_id, trade_events_1.id1, trade_events_1.id6, trade_events_1.id7, trade_events_1.id8, trade_events_1.oms_id, trade_events_1.cl_security_id, trade_events_1.upfront_direction, trade_events_1.upfront_settlement_currency, trade_events_1.stub_rate, trade_events_1.finance_leg_underlying_id, trade_events_1.finance_leg_interest_tenor, trade_events_1.client_im_direction, trade_events_1.im_calculation, trade_events_1.transferor, trade_events_1.transferee, trade_events_1.remaining_party, trade_events_1.novated_amount, trade_events_1.novated_currency, trade_events_1.payment_direction, trade_events_1.early_term_return_leg_price, trade_events_1.early_term_breakage_fee, trade_events_1.currency, trade_events_1.change_in_number_of_units, trade_events_1.outstanding_number_of_units, trade_events_1.change_in_number_of_notional, trade_events_1.outstanding_notional_amount, trade_events_1.upfront_settlement_date, trade_events_1.return_leg_final_value_date, trade_events_1.stub_rate_start_date, trade_events_1.stub_rate_end_date, trade_events_1.novation_date, trade_events_1.novation_contract_date, trade_events_1.payment_date, trade_events_1.effective_date, trade_events_1.sef_id, trade_events_1.comments, trade_events_1.commented_by, trade_events_1.commented_at, trade_events_1.trp_link_id, trade_events_1.oms, trade_events_1.trade_desk, trade_events_1.trader, trade_events_1.msg_type, trade_events_1.msg_type, trade_events_1.trade_type, CASE WHEN (((trade_events_1.trade_date_time)::text = ''::text) OR (trade_events_1.trade_date_time IS NULL) OR (length((trade_events_1.trade_date_time)::text) < 8)) THEN NULL::character varying ELSE trade_events_1.trade_date_time END, trade_events_1.settlement_date, trade_events_1.trans_date_time, trade_events_1.exchange_country, trade_events_1.exchange, trade_events_1.oms_reference, trade_events_1.asset_class, trade_events_1.block_reference, trade_events_1.block_quantity, trade_events_1.trade_currency, trade_events_1.block_gross_amount, trade_events_1.block_net_amount, trade_events_1.block_accrued_income_amount, trade_events_1.exec_broker, trade_events_1.block_step_out, trade_events_1.block_commission_type_code, trade_events_1.block_commission_reason_code, trade_events_1.block_commission_hard_soft, trade_events_1.description, trade_events_1.cusip, trade_events_1.sedol, trade_events_1.allocation_count, trade_events_1.allocation_reference, trade_events_1.account_code, trade_events_1.allocation_quantity, trade_events_1.allocation_gross_amount, trade_events_1.allocation_net_amount, trade_events_1.settlement_currency, trade_events_1.allocation_commission_indicator, trade_events_1.allocation_commission_sharing, trade_events_1.allocation_directed_broker_ind, trade_events_1.allocation_commission_reason_code, trade_events_1.allocation_commission_hard_soft, trade_events_1.allocation_accrued_income_amount, trade_events_1.country_of_issue, trade_events_1.coupon_rate, trade_events_1.maturity_date, trade_events_1.exchange_rate, trade_events_1.value_date, trade_events_1.currency_purchased, trade_events_1.currency_purchased_amount, trade_events_1.currency_sold, trade_events_1.currency_sold_amount, trade_events_1.fx_strategy_code, trade_events_1.ipo_indicator, trade_events_1.nb_shrs, trade_events_1.order_type, trade_events_1.placement_code, trade_events_1.trade_venue, trade_events_1.rebook_indicator, trade_events_1.deposit_role, trade_events_1.net_settlement_indicator, trade_events_1.open_indicator, trade_events_1.ndf_indicator, trade_events_1.fixing_date, trade_events_1.final_close_indicator, trade_events_1.fx_forward_pts, trade_events_1.fx_indicator_range, trade_events_1.fx_indicator_range_time, trade_events_1.fx_session, trade_events_1.fx_source_system, trade_events_1.fx_spot_price, trade_events_1.leg_indicator, trade_events_1.tlm_basket_id, trade_events_1.clearing_flag, trade_events_1.sef_flag, trade_events_1.reporting_jurisdiction, trade_events_1.uti_prefix, trade_events_1.uti, trade_events_1.swap_flag, trade_events_1.swap_link_identifier, trade_events_1.yellow_key, trade_events_1.net_trd_ind, trade_events_1.exec_drty_price, trade_events_1.acct_major_cd, trade_events_1.original_transaction_id, trade_events_1.block_record, trade_events_1.account_id, trade_events_1.trade_status, trade_events_1.exec_broker_cd, trade_events_1.sender_ref, trade_events_1.isin, trade_events_1.ticker, trade_events_1.account_type_cd, trade_events_1.block_commission_am, trade_events_1.block_commission_rt, trade_events_1.block_original_face_am, trade_events_1.block_required_ind, trade_events_1.bloomberg_id, trade_events_1.broker_of_credit_cd, trade_events_1.broker_of_credit_type_cd, trade_events_1.buy_ccy_instrument_id, trade_events_1.call_put_cd, trade_events_1.clearing_broker_account_cd, trade_events_1.clearing_broker_cd, trade_events_1.clearing_broker_nm, trade_events_1.clearing_broker_type_cd, trade_events_1.client_id, trade_events_1.client_instrument_id, trade_events_1.client_instrument_id_in, trade_events_1.client_proprietary, trade_events_1.commission_am, trade_events_1.commission_legal_entity_cd, trade_events_1.commission_rt, trade_events_1.contract_multiplier_am, trade_events_1.cross_account_cd, trade_events_1.custodian_account_cd, trade_events_1.delivery_type_cd, trade_events_1.exec_broker_account_cd, trade_events_1.exec_broker_nm, trade_events_1.exec_broker_type_cd, trade_events_1.exec_instructions_tx, trade_events_1.execution_commission_am, trade_events_1.execution_commission_rt, trade_events_1.expiry_dt, trade_events_1.factor_rt, trade_events_1.fx_back_to_back_broker_cd, trade_events_1.fx_back_to_back_broker_type_cd, trade_events_1.instrument_alternate_ids, trade_events_1.issue_dt, trade_events_1.levy_am, trade_events_1.local_fee_am, trade_events_1.local_tax_am, trade_events_1.lot_id, trade_events_1.lot_size_am, trade_events_1.mortgage_fcta_in, trade_events_1.mortgage_pair_off_in, trade_events_1.mortgage_pool_no, trade_events_1.mortgage_tbac_in, trade_events_1.mortgage_turn_in, trade_events_1.mt_type_cd, trade_events_1.ndf_settlement_currency_cd, trade_events_1.number_of_shares_ct, trade_events_1.occ_id, trade_events_1.open_trade_id, trade_events_1.option_style_cd, trade_events_1.option_sub_type_cd, trade_events_1.option_type_cd, trade_events_1.orig_oms_reference_id, trade_events_1.original_allocation_reference_id, trade_events_1.original_face_am, trade_events_1.original_reference_id, trade_events_1.other_charges_am, trade_events_1.place_of_safe_keeping_cd, trade_events_1.place_of_settlement_cd, trade_events_1.premium_discount_am, trade_events_1.primary_instrument_id, trade_events_1.primary_instrument_id_type_cd, trade_events_1.processing_region_cd, trade_events_1.receivers_cd, trade_events_1.repo_deal_ref_id, trade_events_1.repo_method_of_interest_cd, trade_events_1.repo_rate_type_cd, trade_events_1.repo_termination_dt, trade_events_1.repo_tran_type_cd, trade_events_1.research_commission_am, trade_events_1.research_commission_rt, trade_events_1.sell_ccy_instrument_id, trade_events_1.senders_cd, trade_events_1.senders_nm, trade_events_1.sequence_nbr, trade_events_1.settlement_instrument_id, trade_events_1.source_system_status_cd, trade_events_1.stamp_duty_am, trade_events_1.status_source_system_cd, trade_events_1.step_out_broker_cd, trade_events_1.step_out_broker_type_cd, trade_events_1.strike_pr, trade_events_1.syndicate_broker, trade_events_1.syndicate_broker_cd, trade_events_1.traded_instrument_id, trade_events_1.transaction_event_id, trade_events_1.transaction_tax_am, trade_events_1.underlying_instrument_alternate_ids, trade_events_1.underlying_instrument_desc, trade_events_1.unitized_bond_fl, trade_events_1.yield_pc, trade_events_1.long_short_cd, trade_events_1.broker_of_credit_code_type, trade_events_1.allocation_transaction_id, trade_events_1.cls, date_trunc('day'::text, trade_events_1.receive_tm), row_number() OVER (?), ((trade_events_1.transaction_id)::text)
5. 27,720.325 33,675.540 ↑ 1.1 965,108 1

Sort (cost=2,196,879.58..2,199,436.56 rows=1,022,790 width=1,308) (actual time=29,172.514..33,675.540 rows=965,108 loops=1)

  • Output: trade_events_1.id, ((trade_events_1.transaction_id)::text), trade_events_1.message_group_id, trade_events_1.transaction_id, trade_events_1.payload_type_code, trade_events_1.client_cd, trade_events_1.source_system_cd, trade_events_1.client_ref_id, trade_events_1.instrument_type_cd, trade_events_1.tran_type_cd, trade_events_1.service_nm, trade_events_1.entity_type_cd, trade_events_1.event_class_cd, trade_events_1.event_lifecycle_cd, trade_events_1.transaction_type, trade_events_1.conversation_id, trade_events_1.message_id, trade_events_1.trade_id, trade_events_1.sent_by, trade_events_1.send_to, trade_events_1.id2, trade_events_1.id3, trade_events_1.id4, trade_events_1.id5, trade_events_1.trade_date, trade_events_1.client_trade_direction, trade_events_1.upfront_amount, trade_events_1.return_leg_effective_date, trade_events_1.return_leg_final_valuation_date, trade_events_1.return_leg_maturity_date, trade_events_1.finance_leg_effective_date, trade_events_1.finance_leg_maturity_date, trade_events_1.swap_final_payment_date, trade_events_1.spread, trade_events_1.swap_return_leg_underlying_id, trade_events_1.swap_finance_leg_underlying_id, trade_events_1.swap_finance_leg_interest_tenor, trade_events_1.product_type, trade_events_1.product_subtype, trade_events_1.price_determination_method, trade_events_1.units, trade_events_1.price, trade_events_1.notional, trade_events_1.finance_leg_currency, trade_events_1.return_leg_currency, trade_events_1.swap_currency, trade_events_1.business_day_convention, trade_events_1.finance_leg_business_center, trade_events_1.return_leg_business_center, trade_events_1.im_amount, trade_events_1.portfolio, trade_events_1.roll_convention, trade_events_1.notional_reset, trade_events_1.day_count_fraction, trade_events_1.reset_frequency, trade_events_1.settlement_date_lag_days, trade_events_1.broker, trade_events_1.executing_broker, trade_events_1.receive_tm, trade_events_1.created_at, trade_events_1.updated_at, trade_events_1.early_termination_amt, trade_events_1.xref_deliver_time, trade_events_1.instrument_type, trade_events_1.cleared_deal_id, trade_events_1.gtm_id, trade_events_1.apx_id, trade_events_1.accounting_id, trade_events_1.usi_id, trade_events_1.upi_id, trade_events_1.xef_id, trade_events_1.id1, trade_events_1.id6, trade_events_1.id7, trade_events_1.id8, trade_events_1.oms_id, trade_events_1.cl_security_id, trade_events_1.upfront_direction, trade_events_1.upfront_settlement_currency, trade_events_1.stub_rate, trade_events_1.finance_leg_underlying_id, trade_events_1.finance_leg_interest_tenor, trade_events_1.client_im_direction, trade_events_1.im_calculation, trade_events_1.transferor, trade_events_1.transferee, trade_events_1.remaining_party, trade_events_1.novated_amount, trade_events_1.novated_currency, trade_events_1.payment_direction, trade_events_1.early_term_return_leg_price, trade_events_1.early_term_breakage_fee, trade_events_1.currency, trade_events_1.change_in_number_of_units, trade_events_1.outstanding_number_of_units, trade_events_1.change_in_number_of_notional, trade_events_1.outstanding_notional_amount, trade_events_1.upfront_settlement_date, trade_events_1.return_leg_final_value_date, trade_events_1.stub_rate_start_date, trade_events_1.stub_rate_end_date, trade_events_1.novation_date, trade_events_1.novation_contract_date, trade_events_1.payment_date, trade_events_1.effective_date, trade_events_1.sef_id, trade_events_1.comments, trade_events_1.commented_by, trade_events_1.commented_at, trade_events_1.trp_link_id, trade_events_1.oms, trade_events_1.trade_desk, trade_events_1.trader, trade_events_1.msg_type, trade_events_1.trade_type, trade_events_1.trade_date_time, trade_events_1.settlement_date, trade_events_1.trans_date_time, trade_events_1.exchange_country, trade_events_1.exchange, trade_events_1.oms_reference, trade_events_1.asset_class, trade_events_1.block_reference, trade_events_1.block_quantity, trade_events_1.trade_currency, trade_events_1.block_gross_amount, trade_events_1.block_net_amount, trade_events_1.block_accrued_income_amount, trade_events_1.exec_broker, trade_events_1.block_step_out, trade_events_1.block_commission_type_code, trade_events_1.block_commission_reason_code, trade_events_1.block_commission_hard_soft, trade_events_1.description, trade_events_1.cusip, trade_events_1.sedol, trade_events_1.allocation_count, trade_events_1.allocation_reference, trade_events_1.account_code, trade_events_1.allocation_quantity, trade_events_1.allocation_gross_amount, trade_events_1.allocation_net_amount, trade_events_1.settlement_currency, trade_events_1.allocation_commission_indicator, trade_events_1.allocation_commission_sharing, trade_events_1.allocation_directed_broker_ind, trade_events_1.allocation_commission_reason_code, trade_events_1.allocation_commission_hard_soft, trade_events_1.allocation_accrued_income_amount, trade_events_1.country_of_issue, trade_events_1.coupon_rate, trade_events_1.maturity_date, trade_events_1.exchange_rate, trade_events_1.value_date, trade_events_1.currency_purchased, trade_events_1.currency_purchased_amount, trade_events_1.currency_sold, trade_events_1.currency_sold_amount, trade_events_1.fx_strategy_code, trade_events_1.ipo_indicator, trade_events_1.nb_shrs, trade_events_1.order_type, trade_events_1.placement_code, trade_events_1.trade_venue, trade_events_1.rebook_indicator, trade_events_1.deposit_role, trade_events_1.net_settlement_indicator, trade_events_1.open_indicator, trade_events_1.ndf_indicator, trade_events_1.fixing_date, trade_events_1.final_close_indicator, trade_events_1.fx_forward_pts, trade_events_1.fx_indicator_range, trade_events_1.fx_indicator_range_time, trade_events_1.fx_session, trade_events_1.fx_source_system, trade_events_1.fx_spot_price, trade_events_1.leg_indicator, trade_events_1.tlm_basket_id, trade_events_1.clearing_flag, trade_events_1.sef_flag, trade_events_1.reporting_jurisdiction, trade_events_1.uti_prefix, trade_events_1.uti, trade_events_1.swap_flag, trade_events_1.swap_link_identifier, trade_events_1.yellow_key, trade_events_1.net_trd_ind, trade_events_1.exec_drty_price, trade_events_1.acct_major_cd, trade_events_1.original_transaction_id, trade_events_1.block_record, trade_events_1.account_id, trade_events_1.trade_status, trade_events_1.exec_broker_cd, trade_events_1.sender_ref, trade_events_1.isin, trade_events_1.ticker, trade_events_1.account_type_cd, trade_events_1.block_commission_am, trade_events_1.block_commission_rt, trade_events_1.block_original_face_am, trade_events_1.block_required_ind, trade_events_1.bloomberg_id, trade_events_1.broker_of_credit_cd, trade_events_1.broker_of_credit_type_cd, trade_events_1.buy_ccy_instrument_id, trade_events_1.call_put_cd, trade_events_1.clearing_broker_account_cd, trade_events_1.clearing_broker_cd, trade_events_1.clearing_broker_nm, trade_events_1.clearing_broker_type_cd, trade_events_1.client_id, trade_events_1.client_instrument_id, trade_events_1.client_instrument_id_in, trade_events_1.client_proprietary, trade_events_1.commission_am, trade_events_1.commission_legal_entity_cd, trade_events_1.commission_rt, trade_events_1.contract_multiplier_am, trade_events_1.cross_account_cd, trade_events_1.custodian_account_cd, trade_events_1.delivery_type_cd, trade_events_1.exec_broker_account_cd, trade_events_1.exec_broker_nm, trade_events_1.exec_broker_type_cd, trade_events_1.exec_instructions_tx, trade_events_1.execution_commission_am, trade_events_1.execution_commission_rt, trade_events_1.expiry_dt, trade_events_1.factor_rt, trade_events_1.fx_back_to_back_broker_cd, trade_events_1.fx_back_to_back_broker_type_cd, trade_events_1.instrument_alternate_ids, trade_events_1.issue_dt, trade_events_1.levy_am, trade_events_1.local_fee_am, trade_events_1.local_tax_am, trade_events_1.lot_id, trade_events_1.lot_size_am, trade_events_1.mortgage_fcta_in, trade_events_1.mortgage_pair_off_in, trade_events_1.mortgage_pool_no, trade_events_1.mortgage_tbac_in, trade_events_1.mortgage_turn_in, trade_events_1.mt_type_cd, trade_events_1.ndf_settlement_currency_cd, trade_events_1.number_of_shares_ct, trade_events_1.occ_id, trade_events_1.open_trade_id, trade_events_1.option_style_cd, trade_events_1.option_sub_type_cd, trade_events_1.option_type_cd, trade_events_1.orig_oms_reference_id, trade_events_1.original_allocation_reference_id, trade_events_1.original_face_am, trade_events_1.original_reference_id, trade_events_1.other_charges_am, trade_events_1.place_of_safe_keeping_cd, trade_events_1.place_of_settlement_cd, trade_events_1.premium_discount_am, trade_events_1.primary_instrument_id, trade_events_1.primary_instrument_id_type_cd, trade_events_1.processing_region_cd, trade_events_1.receivers_cd, trade_events_1.repo_deal_ref_id, trade_events_1.repo_method_of_interest_cd, trade_events_1.repo_rate_type_cd, trade_events_1.repo_termination_dt, trade_events_1.repo_tran_type_cd, trade_events_1.research_commission_am, trade_events_1.research_commission_rt, trade_events_1.sell_ccy_instrument_id, trade_events_1.senders_cd, trade_events_1.senders_nm, trade_events_1.sequence_nbr, trade_events_1.settlement_instrument_id, trade_events_1.source_system_status_cd, trade_events_1.stamp_duty_am, trade_events_1.status_source_system_cd, trade_events_1.step_out_broker_cd, trade_events_1.step_out_broker_type_cd, trade_events_1.strike_pr, trade_events_1.syndicate_broker, trade_events_1.syndicate_broker_cd, trade_events_1.traded_instrument_id, trade_events_1.transaction_event_id, trade_events_1.transaction_tax_am, trade_events_1.underlying_instrument_alternate_ids, trade_events_1.underlying_instrument_desc, trade_events_1.unitized_bond_fl, trade_events_1.yield_pc, trade_events_1.long_short_cd, trade_events_1.broker_of_credit_code_type, trade_events_1.allocation_transaction_id, trade_events_1.cls
  • Sort Key: ((trade_events_1.transaction_id)::text), trade_events_1.id DESC
  • Sort Method: external merge Disk: 1165744kB
6. 5,955.215 5,955.215 ↑ 1.1 965,108 1

Seq Scan on eps.trade_events trade_events_1 (cost=0.00..343,352.78 rows=1,022,790 width=1,308) (actual time=0.075..5,955.215 rows=965,108 loops=1)

  • Output: trade_events_1.id, (trade_events_1.transaction_id)::text, trade_events_1.message_group_id, trade_events_1.transaction_id, trade_events_1.payload_type_code, trade_events_1.client_cd, trade_events_1.source_system_cd, trade_events_1.client_ref_id, trade_events_1.instrument_type_cd, trade_events_1.tran_type_cd, trade_events_1.service_nm, trade_events_1.entity_type_cd, trade_events_1.event_class_cd, trade_events_1.event_lifecycle_cd, trade_events_1.transaction_type, trade_events_1.conversation_id, trade_events_1.message_id, trade_events_1.trade_id, trade_events_1.sent_by, trade_events_1.send_to, trade_events_1.id2, trade_events_1.id3, trade_events_1.id4, trade_events_1.id5, trade_events_1.trade_date, trade_events_1.client_trade_direction, trade_events_1.upfront_amount, trade_events_1.return_leg_effective_date, trade_events_1.return_leg_final_valuation_date, trade_events_1.return_leg_maturity_date, trade_events_1.finance_leg_effective_date, trade_events_1.finance_leg_maturity_date, trade_events_1.swap_final_payment_date, trade_events_1.spread, trade_events_1.swap_return_leg_underlying_id, trade_events_1.swap_finance_leg_underlying_id, trade_events_1.swap_finance_leg_interest_tenor, trade_events_1.product_type, trade_events_1.product_subtype, trade_events_1.price_determination_method, trade_events_1.units, trade_events_1.price, trade_events_1.notional, trade_events_1.finance_leg_currency, trade_events_1.return_leg_currency, trade_events_1.swap_currency, trade_events_1.business_day_convention, trade_events_1.finance_leg_business_center, trade_events_1.return_leg_business_center, trade_events_1.im_amount, trade_events_1.portfolio, trade_events_1.roll_convention, trade_events_1.notional_reset, trade_events_1.day_count_fraction, trade_events_1.reset_frequency, trade_events_1.settlement_date_lag_days, trade_events_1.broker, trade_events_1.executing_broker, trade_events_1.receive_tm, trade_events_1.created_at, trade_events_1.updated_at, trade_events_1.early_termination_amt, trade_events_1.xref_deliver_time, trade_events_1.instrument_type, trade_events_1.cleared_deal_id, trade_events_1.gtm_id, trade_events_1.apx_id, trade_events_1.accounting_id, trade_events_1.usi_id, trade_events_1.upi_id, trade_events_1.xef_id, trade_events_1.id1, trade_events_1.id6, trade_events_1.id7, trade_events_1.id8, trade_events_1.oms_id, trade_events_1.cl_security_id, trade_events_1.upfront_direction, trade_events_1.upfront_settlement_currency, trade_events_1.stub_rate, trade_events_1.finance_leg_underlying_id, trade_events_1.finance_leg_interest_tenor, trade_events_1.client_im_direction, trade_events_1.im_calculation, trade_events_1.transferor, trade_events_1.transferee, trade_events_1.remaining_party, trade_events_1.novated_amount, trade_events_1.novated_currency, trade_events_1.payment_direction, trade_events_1.early_term_return_leg_price, trade_events_1.early_term_breakage_fee, trade_events_1.currency, trade_events_1.change_in_number_of_units, trade_events_1.outstanding_number_of_units, trade_events_1.change_in_number_of_notional, trade_events_1.outstanding_notional_amount, trade_events_1.upfront_settlement_date, trade_events_1.return_leg_final_value_date, trade_events_1.stub_rate_start_date, trade_events_1.stub_rate_end_date, trade_events_1.novation_date, trade_events_1.novation_contract_date, trade_events_1.payment_date, trade_events_1.effective_date, trade_events_1.sef_id, trade_events_1.comments, trade_events_1.commented_by, trade_events_1.commented_at, trade_events_1.trp_link_id, trade_events_1.oms, trade_events_1.trade_desk, trade_events_1.trader, trade_events_1.msg_type, trade_events_1.trade_type, trade_events_1.trade_date_time, trade_events_1.settlement_date, trade_events_1.trans_date_time, trade_events_1.exchange_country, trade_events_1.exchange, trade_events_1.oms_reference, trade_events_1.asset_class, trade_events_1.block_reference, trade_events_1.block_quantity, trade_events_1.trade_currency, trade_events_1.block_gross_amount, trade_events_1.block_net_amount, trade_events_1.block_accrued_income_amount, trade_events_1.exec_broker, trade_events_1.block_step_out, trade_events_1.block_commission_type_code, trade_events_1.block_commission_reason_code, trade_events_1.block_commission_hard_soft, trade_events_1.description, trade_events_1.cusip, trade_events_1.sedol, trade_events_1.allocation_count, trade_events_1.allocation_reference, trade_events_1.account_code, trade_events_1.allocation_quantity, trade_events_1.allocation_gross_amount, trade_events_1.allocation_net_amount, trade_events_1.settlement_currency, trade_events_1.allocation_commission_indicator, trade_events_1.allocation_commission_sharing, trade_events_1.allocation_directed_broker_ind, trade_events_1.allocation_commission_reason_code, trade_events_1.allocation_commission_hard_soft, trade_events_1.allocation_accrued_income_amount, trade_events_1.country_of_issue, trade_events_1.coupon_rate, trade_events_1.maturity_date, trade_events_1.exchange_rate, trade_events_1.value_date, trade_events_1.currency_purchased, trade_events_1.currency_purchased_amount, trade_events_1.currency_sold, trade_events_1.currency_sold_amount, trade_events_1.fx_strategy_code, trade_events_1.ipo_indicator, trade_events_1.nb_shrs, trade_events_1.order_type, trade_events_1.placement_code, trade_events_1.trade_venue, trade_events_1.rebook_indicator, trade_events_1.deposit_role, trade_events_1.net_settlement_indicator, trade_events_1.open_indicator, trade_events_1.ndf_indicator, trade_events_1.fixing_date, trade_events_1.final_close_indicator, trade_events_1.fx_forward_pts, trade_events_1.fx_indicator_range, trade_events_1.fx_indicator_range_time, trade_events_1.fx_session, trade_events_1.fx_source_system, trade_events_1.fx_spot_price, trade_events_1.leg_indicator, trade_events_1.tlm_basket_id, trade_events_1.clearing_flag, trade_events_1.sef_flag, trade_events_1.reporting_jurisdiction, trade_events_1.uti_prefix, trade_events_1.uti, trade_events_1.swap_flag, trade_events_1.swap_link_identifier, trade_events_1.yellow_key, trade_events_1.net_trd_ind, trade_events_1.exec_drty_price, trade_events_1.acct_major_cd, trade_events_1.original_transaction_id, trade_events_1.block_record, trade_events_1.account_id, trade_events_1.trade_status, trade_events_1.exec_broker_cd, trade_events_1.sender_ref, trade_events_1.isin, trade_events_1.ticker, trade_events_1.account_type_cd, trade_events_1.block_commission_am, trade_events_1.block_commission_rt, trade_events_1.block_original_face_am, trade_events_1.block_required_ind, trade_events_1.bloomberg_id, trade_events_1.broker_of_credit_cd, trade_events_1.broker_of_credit_type_cd, trade_events_1.buy_ccy_instrument_id, trade_events_1.call_put_cd, trade_events_1.clearing_broker_account_cd, trade_events_1.clearing_broker_cd, trade_events_1.clearing_broker_nm, trade_events_1.clearing_broker_type_cd, trade_events_1.client_id, trade_events_1.client_instrument_id, trade_events_1.client_instrument_id_in, trade_events_1.client_proprietary, trade_events_1.commission_am, trade_events_1.commission_legal_entity_cd, trade_events_1.commission_rt, trade_events_1.contract_multiplier_am, trade_events_1.cross_account_cd, trade_events_1.custodian_account_cd, trade_events_1.delivery_type_cd, trade_events_1.exec_broker_account_cd, trade_events_1.exec_broker_nm, trade_events_1.exec_broker_type_cd, trade_events_1.exec_instructions_tx, trade_events_1.execution_commission_am, trade_events_1.execution_commission_rt, trade_events_1.expiry_dt, trade_events_1.factor_rt, trade_events_1.fx_back_to_back_broker_cd, trade_events_1.fx_back_to_back_broker_type_cd, trade_events_1.instrument_alternate_ids, trade_events_1.issue_dt, trade_events_1.levy_am, trade_events_1.local_fee_am, trade_events_1.local_tax_am, trade_events_1.lot_id, trade_events_1.lot_size_am, trade_events_1.mortgage_fcta_in, trade_events_1.mortgage_pair_off_in, trade_events_1.mortgage_pool_no, trade_events_1.mortgage_tbac_in, trade_events_1.mortgage_turn_in, trade_events_1.mt_type_cd, trade_events_1.ndf_settlement_currency_cd, trade_events_1.number_of_shares_ct, trade_events_1.occ_id, trade_events_1.open_trade_id, trade_events_1.option_style_cd, trade_events_1.option_sub_type_cd, trade_events_1.option_type_cd, trade_events_1.orig_oms_reference_id, trade_events_1.original_allocation_reference_id, trade_events_1.original_face_am, trade_events_1.original_reference_id, trade_events_1.other_charges_am, trade_events_1.place_of_safe_keeping_cd, trade_events_1.place_of_settlement_cd, trade_events_1.premium_discount_am, trade_events_1.primary_instrument_id, trade_events_1.primary_instrument_id_type_cd, trade_events_1.processing_region_cd, trade_events_1.receivers_cd, trade_events_1.repo_deal_ref_id, trade_events_1.repo_method_of_interest_cd, trade_events_1.repo_rate_type_cd, trade_events_1.repo_termination_dt, trade_events_1.repo_tran_type_cd, trade_events_1.research_commission_am, trade_events_1.research_commission_rt, trade_events_1.sell_ccy_instrument_id, trade_events_1.senders_cd, trade_events_1.senders_nm, trade_events_1.sequence_nbr, trade_events_1.settlement_instrument_id, trade_events_1.source_system_status_cd, trade_events_1.stamp_duty_am, trade_events_1.status_source_system_cd, trade_events_1.step_out_broker_cd, trade_events_1.step_out_broker_type_cd, trade_events_1.strike_pr, trade_events_1.syndicate_broker, trade_events_1.syndicate_broker_cd, trade_events_1.traded_instrument_id, trade_events_1.transaction_event_id, trade_events_1.transaction_tax_am, trade_events_1.underlying_instrument_alternate_ids, trade_events_1.underlying_instrument_desc, trade_events_1.unitized_bond_fl, trade_events_1.yield_pc, trade_events_1.long_short_cd, trade_events_1.broker_of_credit_code_type, trade_events_1.allocation_transaction_id, trade_events_1.cls
  • Filter: (((trade_events_1.sender_ref)::text !~ '(-[0-9]{3}C?$)'::text) AND (((trade_events_1.block_required_ind)::text <> 'N'::text) OR ((trade_events_1.block_record)::text <> 'Y'::text)) AND (trade_events_1.created_at >= (current_date - '14 days'::interval)) AND (((trade_events_1.block_required_ind)::text <> 'N'::text) OR ((trade_events_1.block_record)::text <> 'N'::text) OR ((trade_events_1.service_nm)::text <> 'BAAS'::text) OR ((trade_events_1.msg_type)::text <> 'NEW'::text)))
  • Rows Removed by Filter: 30385
7. 773.316 42,104.882 ↓ 30.4 155,652 1

Nested Loop Left Join (cost=0.42..25,126.79 rows=5,114 width=1,983) (actual time=29,173.154..42,104.882 rows=155,652 loops=1)

  • Output: trade_events.id, trade_events.block_gross_amount, trade_events.block_net_amount, trade_events.block_commission_am, trade_events.allocation_quantity, trade_events.allocation_gross_amount, trade_events.allocation_net_amount, trade_events.commission_am, trade_events.exchange_rate, trade_events.currency_purchased_amount, trade_events.currency_sold_amount, trade_events.transaction_id, trade_events.instrument_type_cd, trade_events.message_id, trade_events.sender_ref, trade_events.entity_type_cd, trade_events.message_group_id, trade_events.trade_date, trade_events.trade_date_time, trade_events.sent_by, trade_events.block_record, trade_events.portfolio, trade_events.account_code, trade_events.instrument_type, trade_events.product_subtype, trade_events.description, trade_events.id4, trade_events.sedol, trade_events.block_reference, trade_events.asset_class, trade_events.orig_oms_reference_id, trade_events.cusip, trade_events.isin, trade_events.ticker, trade_events.trade_id, trade_events.transaction_type, trade_events.upfront_amount, trade_events.early_termination_amt, trade_events.msg_type, trade_events.service_nm, trade_events.block_required_ind, trade_events.trade_status, trade_events.swap_currency, trade_events.trade_currency, trade_events.notional, trade_events.units, trade_events.block_quantity, trade_events.swap_return_leg_underlying_id, trade_events.trade_desk, trade_events.broker, trade_events.exec_broker_cd, trade_events.exec_broker, trade_events.clearing_broker_cd, trade_events.client_cd, trade_events.xref_deliver_time, trade_events.receive_tm, trade_events.exchange_country, trade_events.price, trade_events.exec_broker_type_cd, tm.trade_date_time, tm.account_code, tm.instrument_type, tm.description, tm.sedol, tm.block_reference, tm.asset_class, tm.cusip, tm.isin, tm.ticker, tm.transaction_id, tm.instrument_type_cd, tm.currency_purchased_amount, tm.block_net_amount, tm.allocation_net_amount, tm.trade_currency, tm.currency_sold_amount, tm.block_record, tm.units, tm.block_quantity, tm.allocation_quantity, tm.swap_return_leg_underlying_id, tm.trade_desk, tm.exec_broker_cd, tm.exec_broker, tm.clearing_broker_cd, tm.block_commission_am, tm.commission_am, tm.long_short_cd, tm.transaction_type, tm.exchange_country, tm.exchange_rate, tm.price, tm.settlement_date, tm.msg_type, tm.exec_broker_type_cd
8. 38,996.786 38,996.786 ↓ 30.4 155,652 1

CTE Scan on trade_eve trade_events (cost=0.00..102.28 rows=5,114 width=1,788) (actual time=29,172.890..38,996.786 rows=155,652 loops=1)

  • Output: trade_events.id, trade_events.message_group_id, trade_events.transaction_id, trade_events.payload_type_code, trade_events.client_cd, trade_events.source_system_cd, trade_events.client_ref_id, trade_events.instrument_type_cd, trade_events.tran_type_cd, trade_events.service_nm, trade_events.entity_type_cd, trade_events.event_class_cd, trade_events.event_lifecycle_cd, trade_events.transaction_type, trade_events.conversation_id, trade_events.message_id, trade_events.trade_id, trade_events.sent_by, trade_events.send_to, trade_events.id2, trade_events.id3, trade_events.id4, trade_events.id5, trade_events.trade_date, trade_events.client_trade_direction, trade_events.upfront_amount, trade_events.return_leg_effective_date, trade_events.return_leg_final_valuation_date, trade_events.return_leg_maturity_date, trade_events.finance_leg_effective_date, trade_events.finance_leg_maturity_date, trade_events.swap_final_payment_date, trade_events.spread, trade_events.swap_return_leg_underlying_id, trade_events.swap_finance_leg_underlying_id, trade_events.swap_finance_leg_interest_tenor, trade_events.product_type, trade_events.product_subtype, trade_events.price_determination_method, trade_events.units, trade_events.price, trade_events.notional, trade_events.finance_leg_currency, trade_events.return_leg_currency, trade_events.swap_currency, trade_events.business_day_convention, trade_events.finance_leg_business_center, trade_events.return_leg_business_center, trade_events.im_amount, trade_events.portfolio, trade_events.roll_convention, trade_events.notional_reset, trade_events.day_count_fraction, trade_events.reset_frequency, trade_events.settlement_date_lag_days, trade_events.broker, trade_events.executing_broker, trade_events.receive_tm, trade_events.created_at, trade_events.updated_at, trade_events.early_termination_amt, trade_events.xref_deliver_time, trade_events.instrument_type, trade_events.cleared_deal_id, trade_events.gtm_id, trade_events.apx_id, trade_events.accounting_id, trade_events.usi_id, trade_events.upi_id, trade_events.xef_id, trade_events.id1, trade_events.id6, trade_events.id7, trade_events.id8, trade_events.oms_id, trade_events.cl_security_id, trade_events.upfront_direction, trade_events.upfront_settlement_currency, trade_events.stub_rate, trade_events.finance_leg_underlying_id, trade_events.finance_leg_interest_tenor, trade_events.client_im_direction, trade_events.im_calculation, trade_events.transferor, trade_events.transferee, trade_events.remaining_party, trade_events.novated_amount, trade_events.novated_currency, trade_events.payment_direction, trade_events.early_term_return_leg_price, trade_events.early_term_breakage_fee, trade_events.currency, trade_events.change_in_number_of_units, trade_events.outstanding_number_of_units, trade_events.change_in_number_of_notional, trade_events.outstanding_notional_amount, trade_events.upfront_settlement_date, trade_events.return_leg_final_value_date, trade_events.stub_rate_start_date, trade_events.stub_rate_end_date, trade_events.novation_date, trade_events.novation_contract_date, trade_events.payment_date, trade_events.effective_date, trade_events.sef_id, trade_events.comments, trade_events.commented_by, trade_events.commented_at, trade_events.trp_link_id, trade_events.oms, trade_events.trade_desk, trade_events.trader, trade_events.msg_type, trade_events.message_type, trade_events.trade_type, trade_events.trade_date_time, trade_events.settlement_date, trade_events.trans_date_time, trade_events.exchange_country, trade_events.exchange, trade_events.oms_reference, trade_events.asset_class, trade_events.block_reference, trade_events.block_quantity, trade_events.trade_currency, trade_events.block_gross_amount, trade_events.block_net_amount, trade_events.block_accrued_income_amount, trade_events.exec_broker, trade_events.block_step_out, trade_events.block_commission_type_code, trade_events.block_commission_reason_code, trade_events.block_commission_hard_soft, trade_events.description, trade_events.cusip, trade_events.sedol, trade_events.allocation_count, trade_events.allocation_reference, trade_events.account_code, trade_events.allocation_quantity, trade_events.allocation_gross_amount, trade_events.allocation_net_amount, trade_events.settlement_currency, trade_events.allocation_commission_indicator, trade_events.allocation_commission_sharing, trade_events.allocation_directed_broker_ind, trade_events.allocation_commission_reason_code, trade_events.allocation_commission_hard_soft, trade_events.allocation_accrued_income_amount, trade_events.country_of_issue, trade_events.coupon_rate, trade_events.maturity_date, trade_events.exchange_rate, trade_events.value_date, trade_events.currency_purchased, trade_events.currency_purchased_amount, trade_events.currency_sold, trade_events.currency_sold_amount, trade_events.fx_strategy_code, trade_events.ipo_indicator, trade_events.nb_shrs, trade_events.order_type, trade_events.placement_code, trade_events.trade_venue, trade_events.rebook_indicator, trade_events.deposit_role, trade_events.net_settlement_indicator, trade_events.open_indicator, trade_events.ndf_indicator, trade_events.fixing_date, trade_events.final_close_indicator, trade_events.fx_forward_pts, trade_events.fx_indicator_range, trade_events.fx_indicator_range_time, trade_events.fx_session, trade_events.fx_source_system, trade_events.fx_spot_price, trade_events.leg_indicator, trade_events.tlm_basket_id, trade_events.clearing_flag, trade_events.sef_flag, trade_events.reporting_jurisdiction, trade_events.uti_prefix, trade_events.uti, trade_events.swap_flag, trade_events.swap_link_identifier, trade_events.yellow_key, trade_events.net_trd_ind, trade_events.exec_drty_price, trade_events.acct_major_cd, trade_events.original_transaction_id, trade_events.block_record, trade_events.account_id, trade_events.trade_status, trade_events.exec_broker_cd, trade_events.sender_ref, trade_events.isin, trade_events.ticker, trade_events.account_type_cd, trade_events.block_commission_am, trade_events.block_commission_rt, trade_events.block_original_face_am, trade_events.block_required_ind, trade_events.bloomberg_id, trade_events.broker_of_credit_cd, trade_events.broker_of_credit_type_cd, trade_events.buy_ccy_instrument_id, trade_events.call_put_cd, trade_events.clearing_broker_account_cd, trade_events.clearing_broker_cd, trade_events.clearing_broker_nm, trade_events.clearing_broker_type_cd, trade_events.client_id, trade_events.client_instrument_id, trade_events.client_instrument_id_in, trade_events.client_proprietary, trade_events.commission_am, trade_events.commission_legal_entity_cd, trade_events.commission_rt, trade_events.contract_multiplier_am, trade_events.cross_account_cd, trade_events.custodian_account_cd, trade_events.delivery_type_cd, trade_events.exec_broker_account_cd, trade_events.exec_broker_nm, trade_events.exec_broker_type_cd, trade_events.exec_instructions_tx, trade_events.execution_commission_am, trade_events.execution_commission_rt, trade_events.expiry_dt, trade_events.factor_rt, trade_events.fx_back_to_back_broker_cd, trade_events.fx_back_to_back_broker_type_cd, trade_events.instrument_alternate_ids, trade_events.issue_dt, trade_events.levy_am, trade_events.local_fee_am, trade_events.local_tax_am, trade_events.lot_id, trade_events.lot_size_am, trade_events.mortgage_fcta_in, trade_events.mortgage_pair_off_in, trade_events.mortgage_pool_no, trade_events.mortgage_tbac_in, trade_events.mortgage_turn_in, trade_events.mt_type_cd, trade_events.ndf_settlement_currency_cd, trade_events.number_of_shares_ct, trade_events.occ_id, trade_events.open_trade_id, trade_events.option_style_cd, trade_events.option_sub_type_cd, trade_events.option_type_cd, trade_events.orig_oms_reference_id, trade_events.original_allocation_reference_id, trade_events.original_face_am, trade_events.original_reference_id, trade_events.other_charges_am, trade_events.place_of_safe_keeping_cd, trade_events.place_of_settlement_cd, trade_events.premium_discount_am, trade_events.primary_instrument_id, trade_events.primary_instrument_id_type_cd, trade_events.processing_region_cd, trade_events.receivers_cd, trade_events.repo_deal_ref_id, trade_events.repo_method_of_interest_cd, trade_events.repo_rate_type_cd, trade_events.repo_termination_dt, trade_events.repo_tran_type_cd, trade_events.research_commission_am, trade_events.research_commission_rt, trade_events.sell_ccy_instrument_id, trade_events.senders_cd, trade_events.senders_nm, trade_events.sequence_nbr, trade_events.settlement_instrument_id, trade_events.source_system_status_cd, trade_events.stamp_duty_am, trade_events.status_source_system_cd, trade_events.step_out_broker_cd, trade_events.step_out_broker_type_cd, trade_events.strike_pr, trade_events.syndicate_broker, trade_events.syndicate_broker_cd, trade_events.traded_instrument_id, trade_events.transaction_event_id, trade_events.transaction_tax_am, trade_events.underlying_instrument_alternate_ids, trade_events.underlying_instrument_desc, trade_events.unitized_bond_fl, trade_events.yield_pc, trade_events.long_short_cd, trade_events.broker_of_credit_code_type, trade_events.allocation_transaction_id, trade_events.cls, trade_events.receive_t, trade_events.rn
9. 2,334.780 2,334.780 ↑ 1.0 1 155,652

Index Scan using idx_mv_tm_tid on eps.mv_trade_master tm (cost=0.42..4.88 rows=1 width=195) (actual time=0.014..0.015 rows=1 loops=155,652)

  • Output: tm.id, tm.message_group_id, tm.transaction_id, tm.payload_type_code, tm.client_cd, tm.swift_message, tm.source_system_cd, tm.cls, tm.client_ref_id, tm.instrument_type_cd, tm.tran_type_cd, tm.service_nm, tm.entity_type_cd, tm.event_class_cd, tm.event_lifecycle_cd, tm.transaction_type, tm.conversation_id, tm.message_id, tm.trade_id, tm.sent_by, tm.send_to, tm.id2, tm.id3, tm.id4, tm.id5, tm.trade_date, tm.client_trade_direction, tm.upfront_amount, tm.return_leg_effective_date, tm.return_leg_final_valuation_date, tm.return_leg_maturity_date, tm.finance_leg_effective_date, tm.finance_leg_maturity_date, tm.swap_final_payment_date, tm.spread, tm.swap_return_leg_underlying_id, tm.swap_finance_leg_underlying_id, tm.swap_finance_leg_interest_tenor, tm.product_type, tm.product_subtype, tm.price_determination_method, tm.units, tm.price, tm.notional, tm.finance_leg_currency, tm.return_leg_currency, tm.swap_currency, tm.business_day_convention, tm.finance_leg_business_center, tm.return_leg_business_center, tm.im_amount, tm.portfolio, tm.roll_convention, tm.notional_reset, tm.day_count_fraction, tm.reset_frequency, tm.settlement_date_lag_days, tm.broker, tm.executing_broker, tm.receive_tm, tm.created_at, tm.updated_at, tm.early_termination_amt, tm.xref_deliver_time, tm.instrument_type, tm.cleared_deal_id, tm.gtm_id, tm.apx_id, tm.accounting_id, tm.usi_id, tm.upi_id, tm.xef_id, tm.id1, tm.id6, tm.id7, tm.id8, tm.oms_id, tm.cl_security_id, tm.upfront_direction, tm.upfront_settlement_currency, tm.stub_rate, tm.finance_leg_underlying_id, tm.finance_leg_interest_tenor, tm.client_im_direction, tm.im_calculation, tm.transferor, tm.transferee, tm.remaining_party, tm.novated_amount, tm.novated_currency, tm.payment_direction, tm.early_term_return_leg_price, tm.early_term_breakage_fee, tm.currency, tm.change_in_number_of_units, tm.outstanding_number_of_units, tm.change_in_number_of_notional, tm.outstanding_notional_amount, tm.upfront_settlement_date, tm.return_leg_final_value_date, tm.stub_rate_start_date, tm.stub_rate_end_date, tm.novation_date, tm.novation_contract_date, tm.payment_date, tm.effective_date, tm.sef_id, tm.comments, tm.commented_by, tm.commented_at, tm.trp_link_id, tm.oms, tm.trade_desk, tm.trader, tm.msg_type, tm.trade_type, tm.trade_date_time, tm.settlement_date, tm.trans_date_time, tm.exchange_country, tm.exchange, tm.oms_reference, tm.block_reference, tm.block_quantity, tm.trade_currency, tm.block_gross_amount, tm.block_net_amount, tm.block_accrued_income_amount, tm.exec_broker, tm.block_step_out, tm.block_commission_type_code, tm.block_commission_reason_code, tm.block_commission_hard_soft, tm.description, tm.cusip, tm.sedol, tm.allocation_count, tm.allocation_reference, tm.account_code, tm.allocation_quantity, tm.allocation_gross_amount, tm.allocation_net_amount, tm.settlement_currency, tm.allocation_commission_indicator, tm.allocation_commission_sharing, tm.allocation_directed_broker_ind, tm.allocation_commission_reason_code, tm.allocation_commission_hard_soft, tm.allocation_accrued_income_amount, tm.country_of_issue, tm.coupon_rate, tm.maturity_date, tm.exchange_rate, tm.value_date, tm.currency_purchased, tm.currency_purchased_amount, tm.currency_sold, tm.currency_sold_amount, tm.fx_strategy_code, tm.ipo_indicator, tm.nb_shrs, tm.order_type, tm.placement_code, tm.trade_venue, tm.rebook_indicator, tm.deposit_role, tm.net_settlement_indicator, tm.open_indicator, tm.ndf_indicator, tm.fixing_date, tm.final_close_indicator, tm.fx_forward_pts, tm.fx_indicator_range, tm.fx_indicator_range_time, tm.fx_session, tm.fx_source_system, tm.fx_spot_price, tm.leg_indicator, tm.tlm_basket_id, tm.clearing_flag, tm.sef_flag, tm.reporting_jurisdiction, tm.uti_prefix, tm.uti, tm.swap_flag, tm.swap_link_identifier, tm.yellow_key, tm.net_trd_ind, tm.exec_drty_price, tm.acct_major_cd, tm.original_transaction_id, tm.block_record, tm.account_id, tm.trade_status, tm.exec_broker_cd, tm.sender_ref, tm.isin, tm.ticker, tm.account_type_cd, tm.block_commission_am, tm.block_commission_rt, tm.block_original_face_am, tm.block_required_ind, tm.bloomberg_id, tm.broker_of_credit_cd, tm.broker_of_credit_type_cd, tm.buy_ccy_instrument_id, tm.call_put_cd, tm.clearing_broker_account_cd, tm.clearing_broker_cd, tm.clearing_broker_nm, tm.clearing_broker_type_cd, tm.client_id, tm.client_instrument_id, tm.client_instrument_id_in, tm.client_proprietary, tm.commission_am, tm.commission_legal_entity_cd, tm.commission_rt, tm.contract_multiplier_am, tm.cross_account_cd, tm.custodian_account_cd, tm.delivery_type_cd, tm.exec_broker_account_cd, tm.exec_broker_nm, tm.exec_broker_type_cd, tm.exec_instructions_tx, tm.execution_commission_am, tm.execution_commission_rt, tm.expiry_dt, tm.factor_rt, tm.fx_back_to_back_broker_cd, tm.fx_back_to_back_broker_type_cd, tm.instrument_alternate_ids, tm.issue_dt, tm.levy_am, tm.local_fee_am, tm.local_tax_am, tm.lot_id, tm.lot_size_am, tm.mortgage_fcta_in, tm.mortgage_pair_off_in, tm.mortgage_pool_no, tm.mortgage_tbac_in, tm.mortgage_turn_in, tm.mt_type_cd, tm.ndf_settlement_currency_cd, tm.number_of_shares_ct, tm.occ_id, tm.open_trade_id, tm.option_style_cd, tm.option_sub_type_cd, tm.option_type_cd, tm.orig_oms_reference_id, tm.original_allocation_reference_id, tm.original_face_am, tm.original_reference_id, tm.other_charges_am, tm.place_of_safe_keeping_cd, tm.place_of_settlement_cd, tm.premium_discount_am, tm.primary_instrument_id, tm.primary_instrument_id_type_cd, tm.processing_region_cd, tm.receivers_cd, tm.repo_deal_ref_id, tm.repo_method_of_interest_cd, tm.repo_rate_type_cd, tm.repo_termination_dt, tm.repo_tran_type_cd, tm.research_commission_am, tm.research_commission_rt, tm.sell_ccy_instrument_id, tm.senders_cd, tm.senders_nm, tm.sequence_nbr, tm.settlement_instrument_id, tm.source_system_status_cd, tm.stamp_duty_am, tm.status_source_system_cd, tm.step_out_broker_cd, tm.step_out_broker_type_cd, tm.strike_pr, tm.syndicate_broker, tm.syndicate_broker_cd, tm.traded_instrument_id, tm.transaction_event_id, tm.transaction_tax_am, tm.underlying_instrument_alternate_ids, tm.underlying_instrument_desc, tm.unitized_bond_fl, tm.yield_pc, tm.long_short_cd, tm.asset_class, tm.broker_of_credit_code_type, tm.rn
  • Index Cond: ((trade_events.transaction_id)::text = (tm.transaction_id)::text)
10. 0.762 22.027 ↓ 137.4 2,336 1

Hash (cost=407.56..407.56 rows=17 width=71) (actual time=22.027..22.027 rows=2,336 loops=1)

  • Output: cx.comment, cx.commented_by, cx.commented_at, cx.created_at, cx.transaction_id, cx.trade_status
  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 277kB
11. 0.405 21.265 ↓ 137.4 2,336 1

Subquery Scan on cx (cost=290.10..407.56 rows=17 width=71) (actual time=18.704..21.265 rows=2,336 loops=1)

  • Output: cx.comment, cx.commented_by, cx.commented_at, cx.created_at, cx.transaction_id, cx.trade_status
  • Filter: (cx.rn = 1)
  • Rows Removed by Filter: 1042
12. 1.827 20.860 ↓ 1.0 3,378 1

WindowAgg (cost=290.10..365.61 rows=3,356 width=127) (actual time=18.670..20.860 rows=3,378 loops=1)

  • Output: comments.id, comments.transaction_id, comments.trade_status, comments.comment, NULL::character varying, comments.commented_by, comments.commented_at, comments.created_at, NULL::timestamp without time zone, row_number() OVER (?)
13. 17.939 19.033 ↓ 1.0 3,378 1

Sort (cost=290.10..298.49 rows=3,356 width=79) (actual time=18.629..19.033 rows=3,378 loops=1)

  • Output: comments.id, comments.transaction_id, comments.trade_status, comments.comment, comments.commented_by, comments.commented_at, comments.created_at
  • Sort Key: comments.transaction_id, comments.trade_status, comments.id DESC
  • Sort Method: quicksort Memory: 605kB
14. 1.094 1.094 ↓ 1.0 3,378 1

Seq Scan on eps.comments (cost=0.00..93.56 rows=3,356 width=79) (actual time=0.037..1.094 rows=3,378 loops=1)

  • Output: comments.id, comments.transaction_id, comments.trade_status, comments.comment, comments.commented_by, comments.commented_at, comments.created_at