explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BpQ2 : Optimization for: Optimization for: plan #VfxD; plan #sHoL

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.021 320.044 ↓ 200.0 200 1

Limit (cost=1,823.49..1,823.49 rows=1 width=1,164) (actual time=319.996..320.044 rows=200 loops=1)

  • Output: customer_order.order_number, customer_order.crm_order_id, customer_order.account_ucid, customer_order.order_source, customer_order.order_status, (to_char(customer_order.order_submission_date, 'YYYY-MM-DD HH24:MI:SS.MS'::text)), customer_order.account_name, customer_order.account_number, customer_order.contact_info, customer_order.ibx_rollup, customer_order.cage_rollup, customer_order.product_rollup, customer_order.additional_rollup, customer_order.pii_rollup_status, customer_order.activity_rollup_status, customer_order.expedite_rollup_status, customer_order.customer_reference_number, customer_order.service_request_number, customer_order.contact_rollup
  • Buffers: shared hit=76177
2. 28.024 320.023 ↓ 200.0 200 1

Sort (cost=1,823.49..1,823.49 rows=1 width=1,164) (actual time=319.994..320.023 rows=200 loops=1)

  • Output: customer_order.order_number, customer_order.crm_order_id, customer_order.account_ucid, customer_order.order_source, customer_order.order_status, (to_char(customer_order.order_submission_date, 'YYYY-MM-DD HH24:MI:SS.MS'::text)), customer_order.account_name, customer_order.account_number, customer_order.contact_info, customer_order.ibx_rollup, customer_order.cage_rollup, customer_order.product_rollup, customer_order.additional_rollup, customer_order.pii_rollup_status, customer_order.activity_rollup_status, customer_order.expedite_rollup_status, customer_order.customer_reference_number, customer_order.service_request_number, customer_order.contact_rollup
  • Sort Key: (to_char(customer_order.order_submission_date, 'YYYY-MM-DD HH24:MI:SS.MS'::text)) DESC
  • Sort Method: top-N heapsort Memory: 672kB
  • Buffers: shared hit=76177
3. 27.714 291.999 ↓ 22,566.0 22,566 1

Nested Loop (cost=6.55..1,823.48 rows=1 width=1,164) (actual time=0.352..291.999 rows=22,566 loops=1)

  • Output: customer_order.order_number, customer_order.crm_order_id, customer_order.account_ucid, customer_order.order_source, customer_order.order_status, to_char(customer_order.order_submission_date, 'YYYY-MM-DD HH24:MI:SS.MS'::text), customer_order.account_name, customer_order.account_number, customer_order.contact_info, customer_order.ibx_rollup, customer_order.cage_rollup, customer_order.product_rollup, customer_order.additional_rollup, customer_order.pii_rollup_status, customer_order.activity_rollup_status, customer_order.expedite_rollup_status, customer_order.customer_reference_number, customer_order.service_request_number, customer_order.contact_rollup
  • Buffers: shared hit=76177
4. 0.035 0.035 ↓ 3.5 7 1

Index Scan using account_organization_id_idx on terminal.account (cost=0.42..12.44 rows=2 width=6) (actual time=0.017..0.035 rows=7 loops=1)

  • Output: account.account_ucid, account.account_sfdc_id, account.account_number, account.account_name, account.account_customer_alias, account.account_equinix_alias, account.account_type, account.account_status, account.organization_id, account.organization_name, account.portal_reference_id, account.external_reference_source, account.sales_parent_account_ucid, account.sales_parent_account_sfdc_id, account.sales_parent_account_name, account.sales_top_parent_account_ucid, account.sales_top_parent_account_sfdc_id, account.sales_top_parent_account_number, account.sales_top_parent_account_name, account.sales_top_parent_account_customer_alias, account.sales_top_parent_account_equinix_alias, account.sales_top_parent_account_type_code, account.sales_top_parent_account_status, account.region, account.headquarter_country, account.directions, account.location_confidential_customer_flag, account.segment, account.sub_segment, account.billing_cycle, account.billing_frequency, account.billing_method, account.credit_hold, account.currency_code, account.invoice_split_by, account.invoice_group_by, account.invoice_format, account.invoice_level, account.invoice_delivery_method, account.preferred_document_language, account.credit_memo_delivery_flag, account.billable_flag, account.smart_hands_billing_increment, account.po_required_flag, account.streamlined_flag, account.signature_required_flag, account.notify_billing_contact_flag, account.publish_customer_name_flag, account.reseller_experience, account.reseller_flag, account.reseller_ucid, account.sub_customer_billing_flag, account.zside_available_flag, account.partner_flag, account.partner_type, account.partner_sub_type, account.partner_status, account.partner_tier, account.contract_signed_date, account.contract_end_date, account.dcim_permission_flag, account.master_country_agreement_id, account.gtc_id, account.iccc_group_id, account.contact_info, account.created_by, account.created_date, account.last_updated_by, account.last_updated_date, account.ignore_updates_flag, account.deleted_flag, account.etl_created_by, account.etl_created_date, account.etl_data_source, account.etl_last_updated_by, account.etl_last_updated_date, account.po_tracking_type, account.po_format_restricted, account.po_char_limit_min, account.po_char_limit_max, account.published_by_system, account.government_account_flag, account.masked_account_flag
  • Index Cond: (account.organization_id = 7033)
  • Buffers: shared hit=10
5. 250.593 264.250 ↓ 3,224.0 3,224 7

Bitmap Heap Scan on terminal.customer_order (cost=6.13..905.51 rows=1 width=1,140) (actual time=4.049..37.750 rows=3,224 loops=7)

  • Output: customer_order.order_number, customer_order.crm_order_id, customer_order.order_reviewed_date, customer_order.order_comments, customer_order.order_status, customer_order.order_quote_id, customer_order.order_source, customer_order.order_sub_status, customer_order.order_sub_source, customer_order.order_submission_date, customer_order.order_type, customer_order.order_reviewer_ucid, customer_order.order_reviewer_username, customer_order.account_name, customer_order.account_number, customer_order.account_ucid, customer_order.csc_user_key, customer_order.csc_user_name, customer_order.customer_reference_number, customer_order.customer_followup_flag, customer_order.ecp_reference_number, customer_order.currency_code, customer_order.booked_date, customer_order.cancelled_date, customer_order.future_booking_date, customer_order.po_bearing_customer_flag, customer_order.service_request_number, customer_order.activity_rollup_status, customer_order.expedite_flag, customer_order.expedite_rollup_status, customer_order.macd_flag, customer_order.hold_flag, customer_order.pii_hold_flag, customer_order.pii_rollup_status, customer_order.tibco_integration_status, customer_order.attachment_info, customer_order.additional_info, customer_order.contact_info, customer_order.note_info, customer_order.created_by, customer_order.created_date, customer_order.last_updated_date, customer_order.last_updated_by, customer_order.deleted_flag, customer_order.ignore_updates_flag, customer_order.etl_created_by, customer_order.etl_data_source, customer_order.etl_created_date, customer_order.etl_last_updated_by, customer_order.etl_last_updated_date, customer_order.row_modification_counter, customer_order.ibx_rollup, customer_order.product_rollup, customer_order.cage_rollup, customer_order.additional_rollup, customer_order.quote_flag, customer_order.order_completion_flag, customer_order.order_in_progress_flag, customer_order.crm_processed_flag, customer_order.service_request_number_csc, customer_order.contact_rollup
  • Recheck Cond: (customer_order.account_number = account.account_number)
  • Filter: ((customer_order.order_submission_date >= '2018-02-06 00:00:00'::timestamp without time zone) AND (customer_order.order_submission_date <= '2019-02-07 00:00:00'::timestamp without time zone) AND (((customer_order.contact_info IS NOT NULL) AND (((customer_order.contact_info -> 'orderingContacts'::text) @> '{"userName": "rnoakes"}'::jsonb) OR ((customer_order.contact_info -> 'orderingContacts'::text) @> '{"contactUCId": "266741"}'::jsonb) OR ((customer_order.contact_rollup -> 'technicalContacts'::text) @> '[{"contactUCId": "266741"}]'::jsonb))) OR ((customer_order.ibx_rollup IS NOT NULL) AND (customer_order.ibx_rollup ?| '{BO1,DC2,DC1,DC4,DC3,DC6,MI3,MI2,DC5,DC8,DC7,MI4,SE3,SE2,SV2,SV1,SV4,SV3,SV6,SV5,SV8,SV9,PH1,DC11,DC10,NY2,NY1,NY4,NY5,NY8,NY7,NY9,DE1,DA2,DA1,DA4,DA3,DA6,LA2,LA1,LA4,AT1,AT2,LA3,AT3,LA6,CH2,CH1,CH4,CH3,TR1}'::text[]))))
  • Rows Removed by Filter: 8454
  • Heap Blocks: exact=75911
  • Buffers: shared hit=76167
6. 13.657 13.657 ↓ 53.1 12,049 7

Bitmap Index Scan on customer_order_account_number_idx (cost=0.00..6.13 rows=227 width=0) (actual time=1.951..1.951 rows=12,049 loops=7)

  • Index Cond: (customer_order.account_number = account.account_number)
  • Buffers: shared hit=256