explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AOhX

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 20,157.968 ↑ 4.3 47 1

Unique (cost=358,535.70..358,539.51 rows=200 width=64) (actual time=20,157.956..20,157.968 rows=47 loops=1)

  • Output: account.account_number, account.account_name
  • Buffers: shared hit=183497 read=10732 dirtied=422
  • I/O Timings: read=44102.185
2. 0.076 20,157.958 ↑ 10.8 47 1

Sort (cost=358,535.70..358,536.97 rows=507 width=64) (actual time=20,157.955..20,157.958 rows=47 loops=1)

  • Output: account.account_number, account.account_name
  • Sort Key: account.account_name, account.account_number
  • Sort Method: quicksort Memory: 28kB
  • Buffers: shared hit=183497 read=10732 dirtied=422
  • I/O Timings: read=44102.185
3. 0.677 20,157.882 ↑ 10.8 47 1

HashAggregate (cost=358,502.79..358,507.86 rows=507 width=64) (actual time=20,157.871..20,157.882 rows=47 loops=1)

  • Output: account.account_number, account.account_name
  • Group Key: account.account_number, account.account_name
  • Buffers: shared hit=183497 read=10732 dirtied=422
  • I/O Timings: read=44102.185
4. 0.157 20,157.205 ↓ 4.9 2,482 1

Append (cost=751.96..358,500.25 rows=507 width=64) (actual time=54.221..20,157.205 rows=2,482 loops=1)

  • Buffers: shared hit=183497 read=10732 dirtied=422
  • I/O Timings: read=44102.185
5. 0.019 63.180 ↓ 16.2 97 1

Result (cost=751.96..94,063.17 rows=6 width=28) (actual time=54.220..63.180 rows=97 loops=1)

  • Output: account.account_number, account.account_name
  • One-Time Filter: $0
  • Buffers: shared hit=9541
6.          

Initplan (forResult)

7. 0.043 0.043 ↑ 1.0 1 1

Index Scan using asset_account_number_idx on terminal.asset asset_2 (cost=0.43..742.44 rows=1 width=0) (actual time=0.043..0.043 rows=1 loops=1)

  • Index Cond: (asset_2.account_number = '300240'::text)
  • Filter: (((NOT asset_2.deleted_flag) OR (asset_2.deleted_flag IS NULL) OR (NOT asset_2.deleted_flag)) AND ((asset_2.wwcs_status <> 'Approved'::text) OR (asset_2.wwcs_status IS NULL)) AND (asset_2.status = 'Active'::text) AND (CASE WHEN (asset_2.asset_type = ANY ('{"ECX Fabric Port","Cloud Exchange Port","Equinix Ethernet Exchange Port"}'::text[])) THEN asset_2.ecx_cage_unique_space_number ELSE asset_2.cage_unique_space_number END = 'AM5:01:000Z1E'::text))
  • Buffers: shared hit=4
8. 0.067 63.118 ↓ 16.2 97 1

Nested Loop Left Join (cost=751.96..94,063.17 rows=6 width=28) (actual time=54.173..63.118 rows=97 loops=1)

  • Output: account.account_number, account.account_name
  • Inner Unique: true
  • Buffers: shared hit=9537
9. 0.094 62.081 ↓ 16.2 97 1

Nested Loop (cost=9.10..93,298.99 rows=6 width=41) (actual time=54.140..62.081 rows=97 loops=1)

  • Output: asset.asset_number, account.account_number, account.account_name
  • Inner Unique: true
  • Buffers: shared hit=9246
10. 0.100 61.114 ↑ 1.7 97 1

Nested Loop (cost=8.68..93,221.17 rows=162 width=26) (actual time=54.102..61.114 rows=97 loops=1)

  • Output: asset.asset_number, asset_xa.z_side_customer_account_ucid
  • Inner Unique: true
  • Buffers: shared hit=8858
11. 0.044 59.656 ↑ 2.0 97 1

Nested Loop (cost=8.25..92,441.73 rows=195 width=23) (actual time=54.071..59.656 rows=97 loops=1)

  • Output: asset.asset_id, asset.asset_number
  • Buffers: shared hit=8470
12. 0.018 0.104 ↑ 5.6 18 1

HashAggregate (cost=7.82..8.82 rows=100 width=32) (actual time=0.092..0.104 rows=18 loops=1)

  • Output: (((jsonb_array_elements((eqx_org_account.org_account -> 'accounts'::text))) ->> 'accountNumber'::text))
  • Group Key: ((jsonb_array_elements((eqx_org_account.org_account -> 'accounts'::text))) ->> 'accountNumber'::text)
  • Buffers: shared hit=3
13. 0.005 0.086 ↑ 5.6 18 1

Result (cost=0.29..6.57 rows=100 width=32) (actual time=0.079..0.086 rows=18 loops=1)

  • Output: ((jsonb_array_elements((eqx_org_account.org_account -> 'accounts'::text))) ->> 'accountNumber'::text)
  • Buffers: shared hit=3
14. 0.060 0.081 ↑ 5.6 18 1

ProjectSet (cost=0.29..4.82 rows=100 width=32) (actual time=0.078..0.081 rows=18 loops=1)

  • Output: jsonb_array_elements((eqx_org_account.org_account -> 'accounts'::text))
  • Buffers: shared hit=3
15. 0.021 0.021 ↑ 1.0 1 1

Index Scan using cust_org_id_pk on contacts.eqx_org_account (cost=0.29..4.31 rows=1 width=412) (actual time=0.020..0.021 rows=1 loops=1)

  • Output: eqx_org_account.cust_org_id, eqx_org_account.org_account, eqx_org_account.all_asset, eqx_org_account.etl_last_update_date
  • Index Cond: (eqx_org_account.cust_org_id = '20762'::bigint)
  • Buffers: shared hit=3
16. 59.508 59.508 ↓ 2.5 5 18

Index Scan using asset_account_number_idx on terminal.asset (cost=0.43..924.31 rows=2 width=29) (actual time=3.234..3.306 rows=5 loops=18)

  • Output: asset.asset_number, asset.asset_id, asset.account_ucid, asset.account_number, asset.account_name, asset.ibx, asset.ibx_code, asset.crm_status, asset.status, asset.sub_status, asset.asset_type, asset.asset_description, asset.system_name, asset.installation_date, asset.legacy_asset_flag, asset.legacy_customer_order_number, asset.legacy_system_name, asset.monet_cage_flag, asset.wwcs_status, asset.cage_type, asset.cage_unique_space_number, asset.caplogix_cage_unique_space_id, asset.cabinet_number, asset.cabinet_unique_space_number, asset.caplogix_cabinet_unique_space_id, asset.parent_asset_number, asset.parent_product_name, asset.parent_product_part_number, asset.root_asset_number, asset.root_product_name, asset.root_product_part_number, asset.ops_parent_asset_number, asset.ops_root_asset_number, asset.network_cable_number, asset.patch_panel_number, asset.product_description, asset.product_name, asset.product_part_number, asset.ecp_product_name_filter, asset.ecp_product_name, asset.ecp_product_desc, asset.ecp_report_tab_name, asset.quote_number, asset.serial_number, asset.service_order_number, asset.customer_order_number, asset.customer_reference_number, asset.billing_agreement_number, asset.arbiter_cross_connect_account_name, asset.arbiter_cross_connect_account_number, asset.arbiter_cross_connect_account_ucid, asset.ecx_ibx_code, asset.ecx_cage_unique_space_number, asset.ecx_caplogix_cage_unique_space_id, asset.ecx_cabinet_number, asset.ecx_cabinet_unique_space_number, asset.ecx_caplogix_cabinet_unique_space_id, asset.managed_services_package_id, asset.external_reference_number, asset.final_a_side_account_ucid, asset.final_z_side_account_ucid, asset.final_z_side_asset_number, asset.z_side_unique_space_number, asset.created_by, asset.created_date, asset.last_updated_by, asset.last_updated_date, asset.ignore_updates_flag, asset.deleted_flag, asset.etl_created_by, asset.etl_created_date, asset.etl_data_source, asset.etl_last_updated_by, asset.etl_last_updated_date
  • Index Cond: (asset.account_number = (((jsonb_array_elements((eqx_org_account.org_account -> 'accounts'::text))) ->> 'accountNumber'::text)))
  • Filter: (((NOT asset.deleted_flag) OR (asset.deleted_flag IS NULL) OR (NOT asset.deleted_flag)) AND (asset.z_side_unique_space_number IS NOT NULL) AND ((asset.wwcs_status <> 'Approved'::text) OR (asset.wwcs_status IS NULL)) AND (asset.status = 'Active'::text) AND (CASE WHEN (asset.asset_type = ANY ('{"ECX Fabric Port","Cloud Exchange Port","Equinix Ethernet Exchange Port"}'::text[])) THEN asset.ecx_ibx_code ELSE asset.ibx_code END = ANY ('{AM1,AM2,AM3,AM4,AM5,AM6,AM7}'::text[])))
  • Rows Removed by Filter: 724
  • Buffers: shared hit=8467
17. 1.358 1.358 ↑ 1.0 1 97

Index Scan using asset_xa_asset_id_idx on terminal.asset_xa (cost=0.43..4.00 rows=1 width=23) (actual time=0.014..0.014 rows=1 loops=97)

  • Output: asset_xa.asset_id, asset_xa.bctr_room_type, asset_xa.cabinet_type, asset_xa.carrier_circuit_number, asset_xa.customer_cabinet_reference_number, asset_xa.customer_provided_install_flag, asset_xa.final_a_side_system_name, asset_xa.final_z_side_system_name, asset_xa.legacy_cabinet_number, asset_xa.legacy_cage_number, asset_xa.legacy_patch_panel_number, asset_xa.primary_or_redundant_reference_number, asset_xa.product_bundle, asset_xa.switch_name, asset_xa.z_side_customer_account_ucid, asset_xa.asset_variants_info, asset_xa.last_updated_by, asset_xa.last_updated_date, asset_xa.ignore_updates_flag, asset_xa.deleted_flag, asset_xa.etl_created_by, asset_xa.etl_created_date, asset_xa.etl_data_source, asset_xa.etl_last_updated_by, asset_xa.etl_last_updated_date
  • Index Cond: (asset_xa.asset_id = asset.asset_id)
  • Buffers: shared hit=388
18. 0.873 0.873 ↑ 1.0 1 97

Index Scan using account_pk on terminal.account (cost=0.42..0.48 rows=1 width=58) (actual time=0.009..0.009 rows=1 loops=97)

  • 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.published_by_system, account.masked_account_flag, account.government_account_flag, account.federal_account_flag, account.po_tracking_type, account.po_format_restricted, account.po_char_limit_min, account.po_char_limit_max, 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
  • Index Cond: (account.account_ucid = asset_xa.z_side_customer_account_ucid)
  • Filter: ((account.account_number IS NOT NULL) AND ((NOT account.deleted_flag) OR (NOT account.deleted_flag) OR (account.deleted_flag IS NULL)) AND (NOT account.location_confidential_customer_flag) AND (account.account_status = 'Active'::text) AND ((account.account_type = 'Customer'::text) OR ((account.published_by_system = 'Zuora'::text) AND (account.account_type = 'Prospect'::text))))
  • Buffers: shared hit=388
19. 0.970 0.970 ↓ 0.0 0 97

Index Scan using asset_resale_pkey on terminal.asset_resale (cost=0.42..3.61 rows=1 width=14) (actual time=0.010..0.010 rows=0 loops=97)

  • Output: asset_resale.unique_id, asset_resale.asset_number, asset_resale.ebs_account_number, asset_resale.customer_ucm_id, asset_resale.ba_number, asset_resale.status, asset_resale.resale_cage_flag, asset_resale.partner_tier, asset_resale.partner_discount_percent, asset_resale.discount_type, asset_resale.partner_type, asset_resale.partner_sub_type, asset_resale.partner_status, asset_resale.initial_contract_term, asset_resale.pi_percent, asset_resale.last_modified, asset_resale.ignore_updates_flag, asset_resale.deleted_flag, asset_resale.etl_loading_status_flag, asset_resale.etl_created_by, asset_resale.etl_created_date, asset_resale.etl_data_source, asset_resale.etl_last_updated_by, asset_resale.etl_last_updated_date
  • Index Cond: (asset.asset_number = (asset_resale.asset_number)::text)
  • Filter: (((NOT asset_resale.deleted_flag) OR (asset_resale.deleted_flag IS NULL)) AND ((asset_resale.status)::text = 'Active'::text))
  • Buffers: shared hit=291
20. 1,836.793 20,087.189 ↓ 4.8 2,384 1

Gather (cost=1,000.56..264,427.56 rows=500 width=28) (actual time=43.188..20,087.189 rows=2,384 loops=1)

  • Output: account_1.account_number, account_1.account_name
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=173953 read=10731 dirtied=422
  • I/O Timings: read=44095.553
21. 18,010.021 18,250.396 ↓ 3.8 795 3

Nested Loop (cost=0.56..263,377.56 rows=208 width=28) (actual time=2,809.284..18,250.396 rows=795 loops=3)

  • Output: account_1.account_number, account_1.account_name
  • Buffers: shared hit=173953 read=10731 dirtied=422
  • I/O Timings: read=44095.553
  • Worker 0: actual time=8377.461..17427.564 rows=376 loops=1
  • Buffers: shared hit=56837 read=2971 dirtied=129
  • I/O Timings: read=10706.176
  • Worker 1: actual time=8.321..17238.128 rows=919 loops=1
  • Buffers: shared hit=75736 read=3778 dirtied=125
  • I/O Timings: read=16167.718
22. 114.140 114.140 ↑ 1.8 144 3

Parallel Seq Scan on terminal.account account_1 (cost=0.00..60,560.53 rows=255 width=58) (actual time=0.225..114.140 rows=144 loops=3)

  • Output: account_1.account_ucid, account_1.account_sfdc_id, account_1.account_number, account_1.account_name, account_1.account_customer_alias, account_1.account_equinix_alias, account_1.account_type, account_1.account_status, account_1.organization_id, account_1.organization_name, account_1.portal_reference_id, account_1.external_reference_source, account_1.sales_parent_account_ucid, account_1.sales_parent_account_sfdc_id, account_1.sales_parent_account_name, account_1.sales_top_parent_account_ucid, account_1.sales_top_parent_account_sfdc_id, account_1.sales_top_parent_account_number, account_1.sales_top_parent_account_name, account_1.sales_top_parent_account_customer_alias, account_1.sales_top_parent_account_equinix_alias, account_1.sales_top_parent_account_type_code, account_1.sales_top_parent_account_status, account_1.region, account_1.headquarter_country, account_1.directions, account_1.location_confidential_customer_flag, account_1.segment, account_1.sub_segment, account_1.billing_cycle, account_1.billing_frequency, account_1.billing_method, account_1.credit_hold, account_1.currency_code, account_1.invoice_split_by, account_1.invoice_group_by, account_1.invoice_format, account_1.invoice_level, account_1.invoice_delivery_method, account_1.preferred_document_language, account_1.credit_memo_delivery_flag, account_1.billable_flag, account_1.smart_hands_billing_increment, account_1.po_required_flag, account_1.streamlined_flag, account_1.signature_required_flag, account_1.notify_billing_contact_flag, account_1.publish_customer_name_flag, account_1.reseller_experience, account_1.reseller_flag, account_1.reseller_ucid, account_1.sub_customer_billing_flag, account_1.zside_available_flag, account_1.partner_flag, account_1.partner_type, account_1.partner_sub_type, account_1.partner_status, account_1.partner_tier, account_1.contract_signed_date, account_1.contract_end_date, account_1.published_by_system, account_1.masked_account_flag, account_1.government_account_flag, account_1.federal_account_flag, account_1.po_tracking_type, account_1.po_format_restricted, account_1.po_char_limit_min, account_1.po_char_limit_max, account_1.dcim_permission_flag, account_1.master_country_agreement_id, account_1.gtc_id, account_1.iccc_group_id, account_1.contact_info, account_1.created_by, account_1.created_date, account_1.last_updated_by, account_1.last_updated_date, account_1.ignore_updates_flag, account_1.deleted_flag, account_1.etl_created_by, account_1.etl_created_date, account_1.etl_data_source, account_1.etl_last_updated_by, account_1.etl_last_updated_date
  • Filter: (account_1.zside_available_flag AND (account_1.account_status = 'Active'::text))
  • Rows Removed by Filter: 32732
  • Buffers: shared hit=59791
  • Worker 0: actual time=0.147..139.139 rows=117 loops=1
  • Buffers: shared hit=30324
  • Worker 1: actual time=0.499..95.041 rows=105 loops=1
  • Buffers: shared hit=14030
23. 126.235 126.235 ↑ 2.0 6 431

Index Scan using asset_account_uc_id_idx on terminal.asset asset_1 (cost=0.56..795.24 rows=12 width=16) (actual time=122.213..126.235 rows=6 loops=431)

  • Output: asset_1.asset_number, asset_1.asset_id, asset_1.account_ucid, asset_1.account_number, asset_1.account_name, asset_1.ibx, asset_1.ibx_code, asset_1.crm_status, asset_1.status, asset_1.sub_status, asset_1.asset_type, asset_1.asset_description, asset_1.system_name, asset_1.installation_date, asset_1.legacy_asset_flag, asset_1.legacy_customer_order_number, asset_1.legacy_system_name, asset_1.monet_cage_flag, asset_1.wwcs_status, asset_1.cage_type, asset_1.cage_unique_space_number, asset_1.caplogix_cage_unique_space_id, asset_1.cabinet_number, asset_1.cabinet_unique_space_number, asset_1.caplogix_cabinet_unique_space_id, asset_1.parent_asset_number, asset_1.parent_product_name, asset_1.parent_product_part_number, asset_1.root_asset_number, asset_1.root_product_name, asset_1.root_product_part_number, asset_1.ops_parent_asset_number, asset_1.ops_root_asset_number, asset_1.network_cable_number, asset_1.patch_panel_number, asset_1.product_description, asset_1.product_name, asset_1.product_part_number, asset_1.ecp_product_name_filter, asset_1.ecp_product_name, asset_1.ecp_product_desc, asset_1.ecp_report_tab_name, asset_1.quote_number, asset_1.serial_number, asset_1.service_order_number, asset_1.customer_order_number, asset_1.customer_reference_number, asset_1.billing_agreement_number, asset_1.arbiter_cross_connect_account_name, asset_1.arbiter_cross_connect_account_number, asset_1.arbiter_cross_connect_account_ucid, asset_1.ecx_ibx_code, asset_1.ecx_cage_unique_space_number, asset_1.ecx_caplogix_cage_unique_space_id, asset_1.ecx_cabinet_number, asset_1.ecx_cabinet_unique_space_number, asset_1.ecx_caplogix_cabinet_unique_space_id, asset_1.managed_services_package_id, asset_1.external_reference_number, asset_1.final_a_side_account_ucid, asset_1.final_z_side_account_ucid, asset_1.final_z_side_asset_number, asset_1.z_side_unique_space_number, asset_1.created_by, asset_1.created_date, asset_1.last_updated_by, asset_1.last_updated_date, asset_1.ignore_updates_flag, asset_1.deleted_flag, asset_1.etl_created_by, asset_1.etl_created_date, asset_1.etl_data_source, asset_1.etl_last_updated_by, asset_1.etl_last_updated_date
  • Index Cond: (asset_1.account_ucid = account_1.account_ucid)
  • Filter: ((asset_1.status = 'Active'::text) AND (asset_1.ibx = ANY ('{AM1,AM2,AM3,AM4,AM5,AM6,AM7}'::text[])))
  • Rows Removed by Filter: 456
  • Buffers: shared hit=114162 read=10731 dirtied=422
  • I/O Timings: read=44095.553
  • Worker 0: actual time=139.653..147.761 rows=3 loops=117
  • Buffers: shared hit=26513 read=2971 dirtied=129
  • I/O Timings: read=10706.176
  • Worker 1: actual time=156.766..163.262 rows=9 loops=105
  • Buffers: shared hit=61706 read=3778 dirtied=125
  • I/O Timings: read=16167.718
24. 6.679 6.679 ↑ 1.0 1 1

Index Scan using unique_account_number on terminal.account account_2 (cost=0.42..4.45 rows=1 width=28) (actual time=6.678..6.679 rows=1 loops=1)

  • Output: account_2.account_number, account_2.account_name
  • Index Cond: ((account_2.account_number IS NOT NULL) AND (account_2.account_number = '300240'::text))
  • Filter: (((NOT account_2.deleted_flag) OR (NOT account_2.deleted_flag) OR (account_2.deleted_flag IS NULL)) AND (account_2.account_status = 'Active'::text) AND ((account_2.account_type = 'Customer'::text) OR ((account_2.published_by_system = 'Zuora'::text) AND (account_2.account_type = 'Prospect'::text))))
  • Buffers: shared hit=3 read=1
  • I/O Timings: read=6.632
Planning time : 5.497 ms
Execution time : 20,158.388 ms