explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Brm : Optimization for: plan #flyR

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 11,073.074 143,965.160 ↓ 24.0 24 1

Nested Loop Left Join (cost=385,214.67..633,291.25 rows=1 width=3,961) (actual time=6,601.743..143,965.160 rows=24 loops=1)

  • Output: COALESCE((SubPlan 1), '0'::numeric), COALESCE((SubPlan 2), '0'::numeric), COALESCE((SubPlan 3), '0'::numeric), COALESCE((SubPlan 4), '0'::numeric), COALESCE((SubPlan 5), '0'::numeric), COALESCE((SubPlan 6), '0'::numeric), availabilities.csavl_pk, availabilities.lock_num, availabilities.zero_out_status, availabilities.avail_error_status, availabilities.opn_quan_amount, availabilities.opn_quan_av_rdcod_fk, availabilities.opn_quan_compind, availabilities.opn_quan_rduom_fk, availabilities.max_opn_act_quan_amount, availabilities.max_opn_act_quan_av_rdcod_fk, availabilities.max_opn_act_quan_compind, availabilities.max_opn_act_quan_rduom_fk, availabilities.max_opn_quan_amount, availabilities.max_opn_quan_av_rdcod_fk, availabilities.max_opn_quan_compind, availabilities.max_opn_quan_rduom_fk, availabilities.min_opn_act_quan_amount, availabilities.min_opn_act_quan_av_rdcod_fk, availabilities.min_opn_act_quan_compind, availabilities.min_opn_act_quan_rduom_fk, availabilities.min_opn_quan_amount, availabilities.min_opn_quan_av_rdcod_fk, availabilities.min_opn_quan_compind, availabilities.min_opn_quan_rduom_fk, availabilities.deriv_max_qty_av_rdcod_fk, availabilities.deriv_max_qty_compind, availabilities.deriv_max_qty_rduom_fk, availabilities.deriv_max_quan_amount, availabilities.deriv_min_qty_av_rdcod_fk, availabilities.deriv_min_qty_compind, availabilities.deriv_min_qty_rduom_fk, availabilities.deriv_min_quan_amount, availabilities.assigned_operator_scusr_fk, availabilities.operator_notes, availabilities.flexible_field_1, availabilities.flexible_field_2, availabilities.flexible_field_3, availabilities.flexible_field_4, availabilities.flexible_field_5, availabilities.flexible_field_6, availabilities.flexible_field_7, availabilities.flexible_field_8, availabilities.flexible_field_9, availabilities.flexible_field_10, availabilities.deleted_row, commitments.cscmt_pk, commitments.lock_num, commitments.buy_sell_ind, commitments.chain_num, commitments.chain_pref_rdchp_fk, commitments.commitment_num, commitments.orig_quan_amount, commitments.orig_quan_max_amount, commitments.orig_quan_rduom_fk, commitments.downwards_tolerance, commitments.upwards_tolerance, commitments.sg_escalator_expected_val, commitments.tolerance_uom_rduom_fk, commitments.expected_quantity_amount, commitments.owning_company_rdcmb_fk, commitments.counterparty_rdcmb_fk, commitments.deal_broker_rdcod_fk, commitments.payment_secur_rdcod_fk, commitments.grade_rdgrd_fk, commitments.grade_spec_rdgrd_fk, commitments.customs_status_rdcod_fk, commitments.charter_party_options_ind, commitments.evergreen_ind, commitments.inspection_cost_basis_rdcod_fk, commitments.dt_to_be_deemed, commitments.deemed_dt, commitments.pricing_summary, commitments.settlement_summary, commitments.alternate_delivery_terms, commitments.transport_mode_list, commitments.prov_pricing_basis_rdcod_fk, commitments.quality_measure_rdcod_fk, commitments.quantity_measure_rdcod_fk, commitments.pricing_dt_event_rddle_fk, commitments.alternate_locations, commitments.rddlt_deal_type, commitments.deleted_row, commitments.event_pricing_ind, delopt.csdpt_pk, delopt.lock_num, delopt.delivery_terms_rddet_fk, delopt.load_location_rdloc_fk, delopt.dschg_location_rdloc_fk, delopt.location_rdloc_fk, delopt.contract_start_dt, delopt.contract_end_dt, delopt.loading_start_dt, delopt.loading_end_dt, delopt.deliv_period_rddep_fk, delopt.transport_rdtra_fk, delopt.vessel_rdtra_fk, delopt.pipeline_net_rdtra_fk, delopt.transport_mode_rdcod_fk, delopt.bunker_recipient_rdbur_fk, delopt.seq_num, delopt.primary_terms_ind, delopt.deleted_row, delopt.expected_discharge_date, delopt.expected_loading_date, delopt.discharge_start_dt, delopt.discharge_end_dt, delopt.expected_contract_date, deals.csdea_pk, deals.lock_num, deals.deal_reference, deals.deal_done_dt, deals.spot_term_ind, deals.trader_scusr_fk, deals.automatic_operations, deals.asgnd_operator_scusr_fk, deals.trader_notes, deals.linespace_ind, deals.source_platform, deals.deleted_row, (max(csims_import_msgs.generation_num))
  • Join Filter: (csims_import_msgs.avail_csavl_fk = availabilities.csavl_pk)
  • Rows Removed by Join Filter: 33314808
2. 0.148 1,546.854 ↓ 24.0 24 1

Nested Loop (cost=32.50..106,005.50 rows=1 width=3,769) (actual time=1,545.661..1,546.854 rows=24 loops=1)

  • Output: commitments.cscmt_pk, commitments.lock_num, commitments.buy_sell_ind, commitments.chain_num, commitments.chain_pref_rdchp_fk, commitments.commitment_num, commitments.orig_quan_amount, commitments.orig_quan_max_amount, commitments.orig_quan_rduom_fk, commitments.downwards_tolerance, commitments.upwards_tolerance, commitments.sg_escalator_expected_val, commitments.tolerance_uom_rduom_fk, commitments.expected_quantity_amount, commitments.owning_company_rdcmb_fk, commitments.counterparty_rdcmb_fk, commitments.deal_broker_rdcod_fk, commitments.payment_secur_rdcod_fk, commitments.grade_rdgrd_fk, commitments.grade_spec_rdgrd_fk, commitments.customs_status_rdcod_fk, commitments.charter_party_options_ind, commitments.evergreen_ind, commitments.inspection_cost_basis_rdcod_fk, commitments.dt_to_be_deemed, commitments.deemed_dt, commitments.pricing_summary, commitments.settlement_summary, commitments.alternate_delivery_terms, commitments.transport_mode_list, commitments.prov_pricing_basis_rdcod_fk, commitments.quality_measure_rdcod_fk, commitments.quantity_measure_rdcod_fk, commitments.pricing_dt_event_rddle_fk, commitments.alternate_locations, commitments.rddlt_deal_type, commitments.deleted_row, commitments.event_pricing_ind, delopt.csdpt_pk, delopt.lock_num, delopt.delivery_terms_rddet_fk, delopt.load_location_rdloc_fk, delopt.dschg_location_rdloc_fk, delopt.location_rdloc_fk, delopt.contract_start_dt, delopt.contract_end_dt, delopt.loading_start_dt, delopt.loading_end_dt, delopt.deliv_period_rddep_fk, delopt.transport_rdtra_fk, delopt.vessel_rdtra_fk, delopt.pipeline_net_rdtra_fk, delopt.transport_mode_rdcod_fk, delopt.bunker_recipient_rdbur_fk, delopt.seq_num, delopt.primary_terms_ind, delopt.deleted_row, delopt.expected_discharge_date, delopt.expected_loading_date, delopt.discharge_start_dt, delopt.discharge_end_dt, delopt.expected_contract_date, deals.csdea_pk, deals.lock_num, deals.deal_reference, deals.deal_done_dt, deals.spot_term_ind, deals.trader_scusr_fk, deals.automatic_operations, deals.asgnd_operator_scusr_fk, deals.trader_notes, deals.linespace_ind, deals.source_platform, deals.deleted_row, availabilities.ledger_csled_fk, availabilities.csavl_pk, availabilities.lock_num, availabilities.zero_out_status, availabilities.avail_error_status, availabilities.opn_quan_amount, availabilities.opn_quan_av_rdcod_fk, availabilities.opn_quan_compind, availabilities.opn_quan_rduom_fk, availabilities.max_opn_act_quan_amount, availabilities.max_opn_act_quan_av_rdcod_fk, availabilities.max_opn_act_quan_compind, availabilities.max_opn_act_quan_rduom_fk, availabilities.max_opn_quan_amount, availabilities.max_opn_quan_av_rdcod_fk, availabilities.max_opn_quan_compind, availabilities.max_opn_quan_rduom_fk, availabilities.min_opn_act_quan_amount, availabilities.min_opn_act_quan_av_rdcod_fk, availabilities.min_opn_act_quan_compind, availabilities.min_opn_act_quan_rduom_fk, availabilities.min_opn_quan_amount, availabilities.min_opn_quan_av_rdcod_fk, availabilities.min_opn_quan_compind, availabilities.min_opn_quan_rduom_fk, availabilities.deriv_max_qty_av_rdcod_fk, availabilities.deriv_max_qty_compind, availabilities.deriv_max_qty_rduom_fk, availabilities.deriv_max_quan_amount, availabilities.deriv_min_qty_av_rdcod_fk, availabilities.deriv_min_qty_compind, availabilities.deriv_min_qty_rduom_fk, availabilities.deriv_min_quan_amount, availabilities.assigned_operator_scusr_fk, availabilities.operator_notes, availabilities.flexible_field_1, availabilities.flexible_field_2, availabilities.flexible_field_3, availabilities.flexible_field_4, availabilities.flexible_field_5, availabilities.flexible_field_6, availabilities.flexible_field_7, availabilities.flexible_field_8, availabilities.flexible_field_9, availabilities.flexible_field_10, availabilities.deleted_row
  • Join Filter: (csdpt_delivery_options.commitment_cscmt_fk = availabilities.commitment_cscmt_fk)
3. 0.179 1,546.442 ↓ 24.0 24 1

Nested Loop (cost=32.08..106,004.79 rows=1 width=953) (actual time=1,545.642..1,546.442 rows=24 loops=1)

  • Output: commitments.cscmt_pk, commitments.lock_num, commitments.buy_sell_ind, commitments.chain_num, commitments.chain_pref_rdchp_fk, commitments.commitment_num, commitments.orig_quan_amount, commitments.orig_quan_max_amount, commitments.orig_quan_rduom_fk, commitments.downwards_tolerance, commitments.upwards_tolerance, commitments.sg_escalator_expected_val, commitments.tolerance_uom_rduom_fk, commitments.expected_quantity_amount, commitments.owning_company_rdcmb_fk, commitments.counterparty_rdcmb_fk, commitments.deal_broker_rdcod_fk, commitments.payment_secur_rdcod_fk, commitments.grade_rdgrd_fk, commitments.grade_spec_rdgrd_fk, commitments.customs_status_rdcod_fk, commitments.charter_party_options_ind, commitments.evergreen_ind, commitments.inspection_cost_basis_rdcod_fk, commitments.dt_to_be_deemed, commitments.deemed_dt, commitments.pricing_summary, commitments.settlement_summary, commitments.alternate_delivery_terms, commitments.transport_mode_list, commitments.prov_pricing_basis_rdcod_fk, commitments.quality_measure_rdcod_fk, commitments.quantity_measure_rdcod_fk, commitments.pricing_dt_event_rddle_fk, commitments.alternate_locations, commitments.rddlt_deal_type, commitments.deleted_row, commitments.event_pricing_ind, csdpt_delivery_options.commitment_cscmt_fk, delopt.csdpt_pk, delopt.lock_num, delopt.delivery_terms_rddet_fk, delopt.load_location_rdloc_fk, delopt.dschg_location_rdloc_fk, delopt.location_rdloc_fk, delopt.contract_start_dt, delopt.contract_end_dt, delopt.loading_start_dt, delopt.loading_end_dt, delopt.deliv_period_rddep_fk, delopt.transport_rdtra_fk, delopt.vessel_rdtra_fk, delopt.pipeline_net_rdtra_fk, delopt.transport_mode_rdcod_fk, delopt.bunker_recipient_rdbur_fk, delopt.seq_num, delopt.primary_terms_ind, delopt.deleted_row, delopt.expected_discharge_date, delopt.expected_loading_date, delopt.discharge_start_dt, delopt.discharge_end_dt, delopt.expected_contract_date, delopt.commitment_cscmt_fk, deals.csdea_pk, deals.lock_num, deals.deal_reference, deals.deal_done_dt, deals.spot_term_ind, deals.trader_scusr_fk, deals.automatic_operations, deals.asgnd_operator_scusr_fk, deals.trader_notes, deals.linespace_ind, deals.source_platform, deals.deleted_row
  • Join Filter: ((csdpt_delivery_options.commitment_cscmt_fk = delopt.commitment_cscmt_fk) AND ((max(csdpt_delivery_options.seq_num)) = delopt.seq_num))
4. 236.304 1,545.951 ↓ 12.0 24 1

Merge Join (cost=31.65..106,003.68 rows=2 width=767) (actual time=1,545.614..1,545.951 rows=24 loops=1)

  • Output: commitments.cscmt_pk, commitments.lock_num, commitments.buy_sell_ind, commitments.chain_num, commitments.chain_pref_rdchp_fk, commitments.commitment_num, commitments.orig_quan_amount, commitments.orig_quan_max_amount, commitments.orig_quan_rduom_fk, commitments.downwards_tolerance, commitments.upwards_tolerance, commitments.sg_escalator_expected_val, commitments.tolerance_uom_rduom_fk, commitments.expected_quantity_amount, commitments.owning_company_rdcmb_fk, commitments.counterparty_rdcmb_fk, commitments.deal_broker_rdcod_fk, commitments.payment_secur_rdcod_fk, commitments.grade_rdgrd_fk, commitments.grade_spec_rdgrd_fk, commitments.customs_status_rdcod_fk, commitments.charter_party_options_ind, commitments.evergreen_ind, commitments.inspection_cost_basis_rdcod_fk, commitments.dt_to_be_deemed, commitments.deemed_dt, commitments.pricing_summary, commitments.settlement_summary, commitments.alternate_delivery_terms, commitments.transport_mode_list, commitments.prov_pricing_basis_rdcod_fk, commitments.quality_measure_rdcod_fk, commitments.quantity_measure_rdcod_fk, commitments.pricing_dt_event_rddle_fk, commitments.alternate_locations, commitments.rddlt_deal_type, commitments.deleted_row, commitments.event_pricing_ind, csdpt_delivery_options.commitment_cscmt_fk, (max(csdpt_delivery_options.seq_num)), deals.csdea_pk, deals.lock_num, deals.deal_reference, deals.deal_done_dt, deals.spot_term_ind, deals.trader_scusr_fk, deals.automatic_operations, deals.asgnd_operator_scusr_fk, deals.trader_notes, deals.linespace_ind, deals.source_platform, deals.deleted_row
  • Inner Unique: true
  • Merge Cond: (commitments.cscmt_pk = csdpt_delivery_options.commitment_cscmt_fk)
5. 0.057 0.128 ↓ 12.0 24 1

Sort (cost=31.22..31.23 rows=2 width=751) (actual time=0.110..0.128 rows=24 loops=1)

  • Output: commitments.cscmt_pk, commitments.lock_num, commitments.buy_sell_ind, commitments.chain_num, commitments.chain_pref_rdchp_fk, commitments.commitment_num, commitments.orig_quan_amount, commitments.orig_quan_max_amount, commitments.orig_quan_rduom_fk, commitments.downwards_tolerance, commitments.upwards_tolerance, commitments.sg_escalator_expected_val, commitments.tolerance_uom_rduom_fk, commitments.expected_quantity_amount, commitments.owning_company_rdcmb_fk, commitments.counterparty_rdcmb_fk, commitments.deal_broker_rdcod_fk, commitments.payment_secur_rdcod_fk, commitments.grade_rdgrd_fk, commitments.grade_spec_rdgrd_fk, commitments.customs_status_rdcod_fk, commitments.charter_party_options_ind, commitments.evergreen_ind, commitments.inspection_cost_basis_rdcod_fk, commitments.dt_to_be_deemed, commitments.deemed_dt, commitments.pricing_summary, commitments.settlement_summary, commitments.alternate_delivery_terms, commitments.transport_mode_list, commitments.prov_pricing_basis_rdcod_fk, commitments.quality_measure_rdcod_fk, commitments.quantity_measure_rdcod_fk, commitments.pricing_dt_event_rddle_fk, commitments.alternate_locations, commitments.rddlt_deal_type, commitments.deleted_row, commitments.event_pricing_ind, deals.csdea_pk, deals.lock_num, deals.deal_reference, deals.deal_done_dt, deals.spot_term_ind, deals.trader_scusr_fk, deals.automatic_operations, deals.asgnd_operator_scusr_fk, deals.trader_notes, deals.linespace_ind, deals.source_platform, deals.deleted_row
  • Sort Key: commitments.cscmt_pk
  • Sort Method: quicksort Memory: 37kB
6. 0.027 0.071 ↓ 12.0 24 1

Nested Loop (cost=0.85..31.21 rows=2 width=751) (actual time=0.036..0.071 rows=24 loops=1)

  • Output: commitments.cscmt_pk, commitments.lock_num, commitments.buy_sell_ind, commitments.chain_num, commitments.chain_pref_rdchp_fk, commitments.commitment_num, commitments.orig_quan_amount, commitments.orig_quan_max_amount, commitments.orig_quan_rduom_fk, commitments.downwards_tolerance, commitments.upwards_tolerance, commitments.sg_escalator_expected_val, commitments.tolerance_uom_rduom_fk, commitments.expected_quantity_amount, commitments.owning_company_rdcmb_fk, commitments.counterparty_rdcmb_fk, commitments.deal_broker_rdcod_fk, commitments.payment_secur_rdcod_fk, commitments.grade_rdgrd_fk, commitments.grade_spec_rdgrd_fk, commitments.customs_status_rdcod_fk, commitments.charter_party_options_ind, commitments.evergreen_ind, commitments.inspection_cost_basis_rdcod_fk, commitments.dt_to_be_deemed, commitments.deemed_dt, commitments.pricing_summary, commitments.settlement_summary, commitments.alternate_delivery_terms, commitments.transport_mode_list, commitments.prov_pricing_basis_rdcod_fk, commitments.quality_measure_rdcod_fk, commitments.quantity_measure_rdcod_fk, commitments.pricing_dt_event_rddle_fk, commitments.alternate_locations, commitments.rddlt_deal_type, commitments.deleted_row, commitments.event_pricing_ind, deals.csdea_pk, deals.lock_num, deals.deal_reference, deals.deal_done_dt, deals.spot_term_ind, deals.trader_scusr_fk, deals.automatic_operations, deals.asgnd_operator_scusr_fk, deals.trader_notes, deals.linespace_ind, deals.source_platform, deals.deleted_row
7. 0.023 0.023 ↑ 1.0 1 1

Index Scan using csdea_uk01 on csownerbs1.csdea_deals deals (cost=0.42..8.44 rows=1 width=454) (actual time=0.022..0.023 rows=1 loops=1)

  • Output: deals.csdea_pk, deals.lock_num, deals.last_update_user, deals.last_update_time, deals.deal_reference, deals.trader_scusr_fk, deals.deal_done_dt, deals.pending_deal_message_present, deals.deal_message, deals.deleted_row, deals.bo_system_rdcod_fk, deals.asgnd_operator_scusr_fk, deals.spot_term_ind, deals.deal_status_rdcod_fk, deals.counterparty_trader_name, deals.last_deal_change_dt, deals.prev_deal_ref, deals.pending_deal_message, deals.trader_notes, deals.deal_msg_version, deals.pending_deal_msg_version, deals.automatic_operations, deals.cross_purchase_sale, deals.cp_change_pending, deals.marine_deal_ind, deals.customer_po_number, deals.linespace_ind, deals.source_platform
  • Index Cond: ((deals.deal_reference)::text = 'E170028217'::text)
8. 0.021 0.021 ↓ 2.7 24 1

Index Scan using cscmt_uk02 on csownerbs1.cscmt_commitments commitments (cost=0.43..22.68 rows=9 width=305) (actual time=0.009..0.021 rows=24 loops=1)

  • Output: commitments.cscmt_pk, commitments.lock_num, commitments.last_update_user, commitments.last_update_time, commitments.created_by_scusr_fk, commitments.created_dt, commitments.deal_csdea_fk, commitments.commitment_num, commitments.owning_company_rdcmb_fk, commitments.counterparty_rdcmb_fk, commitments.buy_sell_ind, commitments.grade_rdgrd_fk, commitments.chain_pref_rdchp_fk, commitments.chain_num, commitments.house_deal_type, commitments.orig_quan_amount, commitments.orig_quan_rduom_fk, commitments.orig_quan_compind, commitments.orig_quan_max_compind, commitments.invoicing_uom_rduom_fk, commitments.preserved_commitment_num, commitments.deleted_row, commitments.ledger_csled_fk, commitments.rddlt_deal_type, commitments.tolerance_opt_rdcod_fk, commitments.quantity_basis_rdcod_fk, commitments.dt_to_be_deemed, commitments.deemed_dt, commitments.pricing_dt_event_rddle_fk, commitments.orig_quan_av_rdcod_fk, commitments.orig_quan_max_amount, commitments.orig_quan_max_rduom_fk, commitments.orig_quan_max_av_rdcod_fk, commitments.upwards_tolerance, commitments.downwards_tolerance, commitments.tolerance_uom_rduom_fk, commitments.payment_secur_rdcod_fk, commitments.tax_status_rdcod_fk, commitments.grade_spec_rdgrd_fk, commitments.term_deal_start_dt, commitments.term_deal_end_dt, commitments.earliest_contract_from_date, commitments.latest_contract_to_date, commitments.settlement_dt_event_rddle_fk, commitments.transport_mode_list, commitments.contract_start_date, commitments.contract_end_date, commitments.customs_status_rdcod_fk, commitments.inspection_cost_basis_rdcod_fk, commitments.deal_broker_rdcod_fk, commitments.evergreen_ind, commitments.charter_party_options_ind, commitments.alternate_delivery_terms, commitments.pricing_summary, commitments.settlement_summary, commitments.alternate_locations, commitments.alt_basis_rddle_fk, commitments.quantity_measure_rdcod_fk, commitments.quality_measure_rdcod_fk, commitments.gt_and_c_rdgtc_fk, commitments.cargo_density_basis, commitments.prov_pricing_basis_rdcod_fk, commitments.pricing_units_rdpun_fk, commitments.qualesc_1_rdcod_fk, commitments.qualesc_1_expected_val, commitments.qualesc_2_rdcod_fk, commitments.qualesc_2_expected_val, commitments.sg_escalator_base_val, commitments.sg_escalator_expected_val, commitments.expected_quantity_amount, commitments.expected_qty_rduom_fk, commitments.expected_qty_av_rdcod_fk, commitments.expected_qty_compind, commitments.event_pricing_ind
  • Index Cond: (commitments.deal_csdea_fk = deals.csdea_pk)
9. 783.922 1,309.519 ↑ 1.2 1,123,044 1

GroupAggregate (cost=0.43..89,422.51 rows=1,323,994 width=16) (actual time=0.013..1,309.519 rows=1,123,044 loops=1)

  • Output: csdpt_delivery_options.commitment_cscmt_fk, max(csdpt_delivery_options.seq_num)
  • Group Key: csdpt_delivery_options.commitment_cscmt_fk
10. 525.597 525.597 ↑ 1.2 1,123,058 1

Index Scan using csdpt_idx01 on csownerbs1.csdpt_delivery_options (cost=0.43..69,227.09 rows=1,391,095 width=16) (actual time=0.008..525.597 rows=1,123,058 loops=1)

  • Output: csdpt_delivery_options.csdpt_pk, csdpt_delivery_options.commitment_cscmt_fk, csdpt_delivery_options.transport_mode_rdcod_fk, csdpt_delivery_options.delivery_terms_rddet_fk, csdpt_delivery_options.primary_terms_ind, csdpt_delivery_options.lock_num, csdpt_delivery_options.last_update_user, csdpt_delivery_options.last_update_time, csdpt_delivery_options.pipeline_net_rdtra_fk, csdpt_delivery_options.deliv_period_rddep_fk, csdpt_delivery_options.vessel_rdtra_fk, csdpt_delivery_options.transport_rdtra_fk, csdpt_delivery_options.load_location_rdloc_fk, csdpt_delivery_options.dschg_location_rdloc_fk, csdpt_delivery_options.location_rdloc_fk, csdpt_delivery_options.contract_start_dt, csdpt_delivery_options.contract_end_dt, csdpt_delivery_options.loading_start_dt, csdpt_delivery_options.loading_end_dt, csdpt_delivery_options.discharge_start_dt, csdpt_delivery_options.discharge_end_dt, csdpt_delivery_options.cargo_number, csdpt_delivery_options.seq_num, csdpt_delivery_options.deleted_row, csdpt_delivery_options.expected_loading_date, csdpt_delivery_options.expected_discharge_date, csdpt_delivery_options.bunker_recipient_rdbur_fk, csdpt_delivery_options.expected_contract_date
  • Filter: (csdpt_delivery_options.primary_terms_ind = 'Y'::bpchar)
  • Rows Removed by Filter: 38304
11. 0.312 0.312 ↑ 1.0 1 24

Index Scan using csdpt_idx01 on csownerbs1.csdpt_delivery_options delopt (cost=0.43..0.54 rows=1 width=194) (actual time=0.012..0.013 rows=1 loops=24)

  • Output: delopt.csdpt_pk, delopt.commitment_cscmt_fk, delopt.transport_mode_rdcod_fk, delopt.delivery_terms_rddet_fk, delopt.primary_terms_ind, delopt.lock_num, delopt.last_update_user, delopt.last_update_time, delopt.pipeline_net_rdtra_fk, delopt.deliv_period_rddep_fk, delopt.vessel_rdtra_fk, delopt.transport_rdtra_fk, delopt.load_location_rdloc_fk, delopt.dschg_location_rdloc_fk, delopt.location_rdloc_fk, delopt.contract_start_dt, delopt.contract_end_dt, delopt.loading_start_dt, delopt.loading_end_dt, delopt.discharge_start_dt, delopt.discharge_end_dt, delopt.cargo_number, delopt.seq_num, delopt.deleted_row, delopt.expected_loading_date, delopt.expected_discharge_date, delopt.bunker_recipient_rdbur_fk, delopt.expected_contract_date
  • Index Cond: (delopt.commitment_cscmt_fk = commitments.cscmt_pk)
12. 0.264 0.264 ↑ 1.0 1 24

Index Scan using csavl_uk02 on csownerbs1.csavl_availabilities availabilities (cost=0.43..0.70 rows=1 width=2,840) (actual time=0.007..0.011 rows=1 loops=24)

  • Output: availabilities.csavl_pk, availabilities.lock_num, availabilities.last_update_user, availabilities.last_update_time, availabilities.created_by_scusr_fk, availabilities.created_dt, availabilities.commitment_cscmt_fk, availabilities.ledger_csled_fk, availabilities.zero_out_status, availabilities.deriv_max_quan_amount, availabilities.deriv_max_qty_rduom_fk, availabilities.deriv_max_qty_av_rdcod_fk, availabilities.deriv_max_qty_compind, availabilities.deriv_min_quan_amount, availabilities.deriv_min_qty_rduom_fk, availabilities.deriv_min_qty_av_rdcod_fk, availabilities.deriv_min_qty_compind, availabilities.opn_quan_amount, availabilities.opn_quan_rduom_fk, availabilities.opn_quan_av_rdcod_fk, availabilities.opn_quan_compind, availabilities.min_opn_quan_amount, availabilities.min_opn_quan_rduom_fk, availabilities.min_opn_quan_av_rdcod_fk, availabilities.min_opn_quan_compind, availabilities.max_opn_quan_amount, availabilities.max_opn_quan_rduom_fk, availabilities.max_opn_quan_av_rdcod_fk, availabilities.max_opn_quan_compind, availabilities.op_user_assigned_ind, availabilities.max_opn_act_quan_amount, availabilities.max_opn_act_quan_rduom_fk, availabilities.max_opn_act_quan_av_rdcod_fk, availabilities.max_opn_act_quan_compind, availabilities.min_opn_act_quan_amount, availabilities.min_opn_act_quan_rduom_fk, availabilities.min_opn_act_quan_av_rdcod_fk, availabilities.min_opn_act_quan_compind, availabilities.avail_error_status, availabilities.assigned_operator_scusr_fk, availabilities.create_generation_num, availabilities.update_generation_num, availabilities.avail_av_rdcod_fk, availabilities.deleted_row, availabilities.operator_notes, availabilities.flexible_field_1, availabilities.flexible_field_2, availabilities.flexible_field_3, availabilities.flexible_field_4, availabilities.flexible_field_5, availabilities.flexible_field_6, availabilities.flexible_field_7, availabilities.flexible_field_8, availabilities.flexible_field_9, availabilities.flexible_field_10, availabilities.msg_version, availabilities.bo_system_rdcod_fk, availabilities.expected_opn_quantity_amount, availabilities.expected_opn_qty_rduom_fk, availabilities.expected_opn_qty_av_rdcod_fk, availabilities.expected_opn_qty_compind, availabilities.barrels, availabilities.barrels_rduom_fk, availabilities.barrels_av_rdcod_fk, availabilities.barrels_compind, availabilities.metric_ton_vac, availabilities.mt_vac_rduom_fk, availabilities.mt_vac_av_rdcod_fk, availabilities.mt_vac_compind, availabilities.metric_ton_air, availabilities.mt_air_rduom_fk, availabilities.mt_air_av_rdcod_fk, availabilities.mt_air_compind, availabilities.cubic_metres, availabilities.cubic_metres_rduom_fk, availabilities.cubic_metres_av_rdcod_fk, availabilities.cubic_metres_compind, availabilities.barrels_at_60, availabilities.barrels_at_60_rduom_fk, availabilities.barrels_at_60_av_rdcod_fk, availabilities.barrels_at_60_compind, availabilities.cubic_metres_at_15, availabilities.cubic_metres_at_15_rduom_fk, availabilities.cubic_metres_at_15_av_rdcod_fk, availabilities.cubic_metres_at_15_compind, availabilities.is_manual_unzeroed_pipe_deal, availabilities.is_zero_out_outside_tolerance
  • Index Cond: (availabilities.commitment_cscmt_fk = delopt.commitment_cscmt_fk)
13. 27,828.072 131,342.832 ↓ 3.3 1,388,118 24

Finalize GroupAggregate (cost=385,182.16..516,286.83 rows=426,334 width=24) (actual time=1,813.768..5,472.618 rows=1,388,118 loops=24)

  • Output: csims_import_msgs.avail_csavl_fk, csims_import_msgs.commitment_num, max(csims_import_msgs.generation_num)
  • Group Key: csims_import_msgs.avail_csavl_fk, csims_import_msgs.commitment_num
14. 0.000 103,514.760 ↓ 2.3 1,969,098 24

Gather Merge (cost=385,182.16..505,628.48 rows=852,668 width=24) (actual time=1,813.763..4,313.115 rows=1,969,098 loops=24)

  • Output: csims_import_msgs.avail_csavl_fk, csims_import_msgs.commitment_num, (PARTIAL max(csims_import_msgs.generation_num))
  • Workers Planned: 2
  • Workers Launched: 2
15. 55,188.288 211,856.256 ↓ 1.5 656,366 72

Partial GroupAggregate (cost=384,182.14..406,209.41 rows=426,334 width=24) (actual time=1,688.681..2,942.448 rows=656,366 loops=72)

  • Output: csims_import_msgs.avail_csavl_fk, csims_import_msgs.commitment_num, PARTIAL max(csims_import_msgs.generation_num)
  • Group Key: csims_import_msgs.avail_csavl_fk, csims_import_msgs.commitment_num
  • Worker 0: actual time=1626.861..2949.367 rows=658704 loops=24
  • Worker 1: actual time=1626.379..2948.597 rows=659234 loops=24
16. 114,665.472 156,667.968 ↑ 1.2 1,421,484 72

Sort (cost=384,182.14..388,623.12 rows=1,776,393 width=24) (actual time=1,685.462..2,175.944 rows=1,421,484 loops=72)

  • Output: csims_import_msgs.avail_csavl_fk, csims_import_msgs.commitment_num, csims_import_msgs.generation_num
  • Sort Key: csims_import_msgs.avail_csavl_fk, csims_import_msgs.commitment_num
  • Sort Method: external sort Disk: 51872kB
  • Worker 0: actual time=1626.852..2151.957 rows=1429402 loops=24
  • Worker 1: actual time=1626.373..2150.651 rows=1429424 loops=24
17. 42,002.496 42,002.496 ↑ 1.2 1,421,484 72

Parallel Seq Scan on csownerbs1.csims_import_msgs (cost=0.00..126,924.93 rows=1,776,393 width=24) (actual time=0.006..583.368 rows=1,421,484 loops=72)

  • Output: csims_import_msgs.avail_csavl_fk, csims_import_msgs.commitment_num, csims_import_msgs.generation_num
  • Worker 0: actual time=0.006..586.441 rows=1429402 loops=24
  • Worker 1: actual time=0.007..586.713 rows=1429424 loops=24
18.          

SubPlan (for Nested Loop Left Join)

19. 0.144 0.888 ↑ 1.0 1 24

Aggregate (cost=234.39..234.40 rows=1 width=32) (actual time=0.037..0.037 rows=1 loops=24)

  • Output: sum(abs(cslee_ledger_entries.mass_air_amount))
20. 0.096 0.744 ↓ 0.0 0 24

Nested Loop (cost=0.43..234.27 rows=24 width=8) (actual time=0.031..0.031 rows=0 loops=24)

  • Output: cslee_ledger_entries.mass_air_amount
  • Join Filter: (cslee_ledger_entries.entry_type_cslet_fk = cslet_ledger_ent_types.cslet_pk)
  • Rows Removed by Join Filter: 3
21. 0.240 0.240 ↑ 1.0 1 24

Seq Scan on csownerbs1.cslet_ledger_ent_types (cost=0.00..1.04 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=24)

  • Output: cslet_ledger_ent_types.cslet_pk, cslet_ledger_ent_types.lock_num, cslet_ledger_ent_types.last_update_user, cslet_ledger_ent_types.last_update_time, cslet_ledger_ent_types.name, cslet_ledger_ent_types.deleted_row
  • Filter: ((cslet_ledger_ent_types.name)::text = 'Scheduled'::text)
  • Rows Removed by Filter: 2
22. 0.408 0.408 ↑ 24.3 3 24

Index Scan using cslee_idx01 on csownerbs1.cslee_ledger_entries (cost=0.43..232.32 rows=73 width=16) (actual time=0.014..0.017 rows=3 loops=24)

  • Output: cslee_ledger_entries.cslee_pk, cslee_ledger_entries.last_update_user, cslee_ledger_entries.last_update_time, cslee_ledger_entries.ledger_csled_fk, cslee_ledger_entries.entry_type_cslet_fk, cslee_ledger_entries.deleted_row, cslee_ledger_entries.mass_air_amount, cslee_ledger_entries.mass_vac_amount, cslee_ledger_entries.volume_amount
  • Index Cond: (cslee_ledger_entries.ledger_csled_fk = availabilities.ledger_csled_fk)
  • Filter: (cslee_ledger_entries.deleted_row IS NULL)
23. 0.072 0.264 ↑ 1.0 1 24

Aggregate (cost=234.39..234.40 rows=1 width=32) (actual time=0.010..0.011 rows=1 loops=24)

  • Output: sum(abs(cslee_ledger_entries_1.mass_vac_amount))
24. 0.048 0.192 ↓ 0.0 0 24

Nested Loop (cost=0.43..234.27 rows=24 width=8) (actual time=0.008..0.008 rows=0 loops=24)

  • Output: cslee_ledger_entries_1.mass_vac_amount
  • Join Filter: (cslee_ledger_entries_1.entry_type_cslet_fk = cslet_ledger_ent_types_1.cslet_pk)
  • Rows Removed by Join Filter: 3
25. 0.048 0.048 ↑ 1.0 1 24

Seq Scan on csownerbs1.cslet_ledger_ent_types cslet_ledger_ent_types_1 (cost=0.00..1.04 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=24)

  • Output: cslet_ledger_ent_types_1.cslet_pk, cslet_ledger_ent_types_1.lock_num, cslet_ledger_ent_types_1.last_update_user, cslet_ledger_ent_types_1.last_update_time, cslet_ledger_ent_types_1.name, cslet_ledger_ent_types_1.deleted_row
  • Filter: ((cslet_ledger_ent_types_1.name)::text = 'Scheduled'::text)
  • Rows Removed by Filter: 2
26. 0.096 0.096 ↑ 24.3 3 24

Index Scan using cslee_idx01 on csownerbs1.cslee_ledger_entries cslee_ledger_entries_1 (cost=0.43..232.32 rows=73 width=16) (actual time=0.002..0.004 rows=3 loops=24)

  • Output: cslee_ledger_entries_1.cslee_pk, cslee_ledger_entries_1.last_update_user, cslee_ledger_entries_1.last_update_time, cslee_ledger_entries_1.ledger_csled_fk, cslee_ledger_entries_1.entry_type_cslet_fk, cslee_ledger_entries_1.deleted_row, cslee_ledger_entries_1.mass_air_amount, cslee_ledger_entries_1.mass_vac_amount, cslee_ledger_entries_1.volume_amount
  • Index Cond: (cslee_ledger_entries_1.ledger_csled_fk = availabilities.ledger_csled_fk)
  • Filter: (cslee_ledger_entries_1.deleted_row IS NULL)
27. 0.072 0.240 ↑ 1.0 1 24

Aggregate (cost=234.39..234.40 rows=1 width=32) (actual time=0.009..0.010 rows=1 loops=24)

  • Output: sum(abs(cslee_ledger_entries_2.volume_amount))
28. 0.072 0.168 ↓ 0.0 0 24

Nested Loop (cost=0.43..234.27 rows=24 width=7) (actual time=0.007..0.007 rows=0 loops=24)

  • Output: cslee_ledger_entries_2.volume_amount
  • Join Filter: (cslee_ledger_entries_2.entry_type_cslet_fk = cslet_ledger_ent_types_2.cslet_pk)
  • Rows Removed by Join Filter: 3
29. 0.024 0.024 ↑ 1.0 1 24

Seq Scan on csownerbs1.cslet_ledger_ent_types cslet_ledger_ent_types_2 (cost=0.00..1.04 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=24)

  • Output: cslet_ledger_ent_types_2.cslet_pk, cslet_ledger_ent_types_2.lock_num, cslet_ledger_ent_types_2.last_update_user, cslet_ledger_ent_types_2.last_update_time, cslet_ledger_ent_types_2.name, cslet_ledger_ent_types_2.deleted_row
  • Filter: ((cslet_ledger_ent_types_2.name)::text = 'Scheduled'::text)
  • Rows Removed by Filter: 2
30. 0.072 0.072 ↑ 24.3 3 24

Index Scan using cslee_idx01 on csownerbs1.cslee_ledger_entries cslee_ledger_entries_2 (cost=0.43..232.32 rows=73 width=15) (actual time=0.002..0.003 rows=3 loops=24)

  • Output: cslee_ledger_entries_2.cslee_pk, cslee_ledger_entries_2.last_update_user, cslee_ledger_entries_2.last_update_time, cslee_ledger_entries_2.ledger_csled_fk, cslee_ledger_entries_2.entry_type_cslet_fk, cslee_ledger_entries_2.deleted_row, cslee_ledger_entries_2.mass_air_amount, cslee_ledger_entries_2.mass_vac_amount, cslee_ledger_entries_2.volume_amount
  • Index Cond: (cslee_ledger_entries_2.ledger_csled_fk = availabilities.ledger_csled_fk)
  • Filter: (cslee_ledger_entries_2.deleted_row IS NULL)
31. 0.168 0.384 ↑ 1.0 1 24

Aggregate (cost=234.39..234.40 rows=1 width=32) (actual time=0.016..0.016 rows=1 loops=24)

  • Output: sum(abs(cslee_ledger_entries_3.mass_air_amount))
32. 0.096 0.216 ↑ 8.0 3 24

Nested Loop (cost=0.43..234.27 rows=24 width=8) (actual time=0.006..0.009 rows=3 loops=24)

  • Output: cslee_ledger_entries_3.mass_air_amount
  • Join Filter: (cslee_ledger_entries_3.entry_type_cslet_fk = cslet_ledger_ent_types_3.cslet_pk)
33. 0.048 0.048 ↑ 1.0 1 24

Seq Scan on csownerbs1.cslet_ledger_ent_types cslet_ledger_ent_types_3 (cost=0.00..1.04 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=24)

  • Output: cslet_ledger_ent_types_3.cslet_pk, cslet_ledger_ent_types_3.lock_num, cslet_ledger_ent_types_3.last_update_user, cslet_ledger_ent_types_3.last_update_time, cslet_ledger_ent_types_3.name, cslet_ledger_ent_types_3.deleted_row
  • Filter: ((cslet_ledger_ent_types_3.name)::text = 'Actualized'::text)
  • Rows Removed by Filter: 2
34. 0.072 0.072 ↑ 24.3 3 24

Index Scan using cslee_idx01 on csownerbs1.cslee_ledger_entries cslee_ledger_entries_3 (cost=0.43..232.32 rows=73 width=16) (actual time=0.002..0.003 rows=3 loops=24)

  • Output: cslee_ledger_entries_3.cslee_pk, cslee_ledger_entries_3.last_update_user, cslee_ledger_entries_3.last_update_time, cslee_ledger_entries_3.ledger_csled_fk, cslee_ledger_entries_3.entry_type_cslet_fk, cslee_ledger_entries_3.deleted_row, cslee_ledger_entries_3.mass_air_amount, cslee_ledger_entries_3.mass_vac_amount, cslee_ledger_entries_3.volume_amount
  • Index Cond: (cslee_ledger_entries_3.ledger_csled_fk = availabilities.ledger_csled_fk)
  • Filter: (cslee_ledger_entries_3.deleted_row IS NULL)
35. 0.120 0.312 ↑ 1.0 1 24

Aggregate (cost=234.39..234.40 rows=1 width=32) (actual time=0.013..0.013 rows=1 loops=24)

  • Output: sum(abs(cslee_ledger_entries_4.mass_vac_amount))
36. 0.072 0.192 ↑ 8.0 3 24

Nested Loop (cost=0.43..234.27 rows=24 width=8) (actual time=0.005..0.008 rows=3 loops=24)

  • Output: cslee_ledger_entries_4.mass_vac_amount
  • Join Filter: (cslee_ledger_entries_4.entry_type_cslet_fk = cslet_ledger_ent_types_4.cslet_pk)
37. 0.048 0.048 ↑ 1.0 1 24

Seq Scan on csownerbs1.cslet_ledger_ent_types cslet_ledger_ent_types_4 (cost=0.00..1.04 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=24)

  • Output: cslet_ledger_ent_types_4.cslet_pk, cslet_ledger_ent_types_4.lock_num, cslet_ledger_ent_types_4.last_update_user, cslet_ledger_ent_types_4.last_update_time, cslet_ledger_ent_types_4.name, cslet_ledger_ent_types_4.deleted_row
  • Filter: ((cslet_ledger_ent_types_4.name)::text = 'Actualized'::text)
  • Rows Removed by Filter: 2
38. 0.072 0.072 ↑ 24.3 3 24

Index Scan using cslee_idx01 on csownerbs1.cslee_ledger_entries cslee_ledger_entries_4 (cost=0.43..232.32 rows=73 width=16) (actual time=0.002..0.003 rows=3 loops=24)

  • Output: cslee_ledger_entries_4.cslee_pk, cslee_ledger_entries_4.last_update_user, cslee_ledger_entries_4.last_update_time, cslee_ledger_entries_4.ledger_csled_fk, cslee_ledger_entries_4.entry_type_cslet_fk, cslee_ledger_entries_4.deleted_row, cslee_ledger_entries_4.mass_air_amount, cslee_ledger_entries_4.mass_vac_amount, cslee_ledger_entries_4.volume_amount
  • Index Cond: (cslee_ledger_entries_4.ledger_csled_fk = availabilities.ledger_csled_fk)
  • Filter: (cslee_ledger_entries_4.deleted_row IS NULL)
39. 0.120 0.312 ↑ 1.0 1 24

Aggregate (cost=234.39..234.40 rows=1 width=32) (actual time=0.013..0.013 rows=1 loops=24)

  • Output: sum(abs(cslee_ledger_entries_5.volume_amount))
40. 0.096 0.192 ↑ 8.0 3 24

Nested Loop (cost=0.43..234.27 rows=24 width=7) (actual time=0.005..0.008 rows=3 loops=24)

  • Output: cslee_ledger_entries_5.volume_amount
  • Join Filter: (cslee_ledger_entries_5.entry_type_cslet_fk = cslet_ledger_ent_types_5.cslet_pk)
41. 0.024 0.024 ↑ 1.0 1 24

Seq Scan on csownerbs1.cslet_ledger_ent_types cslet_ledger_ent_types_5 (cost=0.00..1.04 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=24)

  • Output: cslet_ledger_ent_types_5.cslet_pk, cslet_ledger_ent_types_5.lock_num, cslet_ledger_ent_types_5.last_update_user, cslet_ledger_ent_types_5.last_update_time, cslet_ledger_ent_types_5.name, cslet_ledger_ent_types_5.deleted_row
  • Filter: ((cslet_ledger_ent_types_5.name)::text = 'Actualized'::text)
  • Rows Removed by Filter: 2
42. 0.072 0.072 ↑ 24.3 3 24

Index Scan using cslee_idx01 on csownerbs1.cslee_ledger_entries cslee_ledger_entries_5 (cost=0.43..232.32 rows=73 width=15) (actual time=0.002..0.003 rows=3 loops=24)

  • Output: cslee_ledger_entries_5.cslee_pk, cslee_ledger_entries_5.last_update_user, cslee_ledger_entries_5.last_update_time, cslee_ledger_entries_5.ledger_csled_fk, cslee_ledger_entries_5.entry_type_cslet_fk, cslee_ledger_entries_5.deleted_row, cslee_ledger_entries_5.mass_air_amount, cslee_ledger_entries_5.mass_vac_amount, cslee_ledger_entries_5.volume_amount
  • Index Cond: (cslee_ledger_entries_5.ledger_csled_fk = availabilities.ledger_csled_fk)
  • Filter: (cslee_ledger_entries_5.deleted_row IS NULL)
Planning time : 3.131 ms
Execution time : 144,025.667 ms