explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kGIV

Settings
# exclusive inclusive rows x rows loops node
1. 0.075 290.847 ↑ 1.0 1 1

Nested Loop Left Join (cost=254,707.87..256,628.18 rows=1 width=1,549) (actual time=290.843..290.847 rows=1 loops=1)

  • Output: act.account_ucid, act.account_name, act.account_customer_alias, act.account_equinix_alias, act.account_number, act.account_number, act.account_status, act.account_type, act.billing_cycle, act.billing_frequency, act.billing_method, act.contract_end_date, act.contract_signed_date, act.currency_code, act.credit_hold, act.credit_memo_delivery_flag, act.directions, act.portal_reference_id, act.etl_last_updated_date, act.headquarter_country, act.invoice_delivery_method, act.invoice_format, act.invoice_group_by, act.invoice_level, act.invoice_split_by, act.last_updated_by, act.last_updated_date, act.location_confidential_customer_flag, act.sales_parent_account_ucid, act.partner_flag, act.partner_status, act.partner_sub_type, act.partner_tier, act.partner_type, CASE WHEN act.po_required_flag THEN 'Yes'::text ELSE 'No'::text END, (act.organization_id)::text, act.organization_name, act.preferred_document_language, act.publish_customer_name_flag, act.region, act.reseller_flag, act.reseller_ucid, act.segment, act.signature_required_flag, act.account_sfdc_id, act.smart_hands_billing_increment, act.created_by, act.created_date, act.streamlined_flag, act.sub_customer_billing_flag, act.sub_segment, act.sales_top_parent_account_customer_alias, act.sales_top_parent_account_equinix_alias, act.sales_top_parent_account_name, act.sales_top_parent_account_number, act.sales_top_parent_account_status, act.sales_top_parent_account_type_code, act.sales_top_parent_account_sfdc_id, act.sales_top_parent_account_ucid, CASE WHEN act.signature_required_flag THEN 'Yes'::text ELSE 'No'::text END, act.contact_info, jsonb_build_object('client_serv_manager_email', ((act.contact_info)::json #>> '{primaryCSM,0,emailAddress}'::text[])), jsonb_build_object('sales_engineer_preffname', ((act.contact_info)::json #>> '{primarySalesEngineer,0,preferredFirstName}'::text[])), ibxpresence.ibx_presence_all, ibxpresence.ibx_presence_all
  • Inner Unique: true
  • Buffers: shared hit=49819
2. 0.027 0.027 ↑ 1.0 1 1

Index Scan using account_pk on terminal.account act (cost=0.42..8.44 rows=1 width=1,324) (actual time=0.023..0.027 rows=1 loops=1)

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

Subquery Scan on ibxpresence (cost=254,707.45..256,619.71 rows=1 width=64) (actual time=290.745..290.745 rows=1 loops=1)

  • Output: ibxpresence.account_ucid, ibxpresence.ibx_presence_all
  • Filter: (act.account_ucid = ibxpresence.account_ucid)
  • Buffers: shared hit=49815
4. 0.033 290.743 ↑ 1.0 1 1

GroupAggregate (cost=254,707.45..256,619.70 rows=1 width=64) (actual time=290.743..290.743 rows=1 loops=1)

  • Output: (act.account_ucid), jsonb_agg(jsonb_build_object('ibxp.ibx_code', ibxp.ibx_code))
  • Group Key: act.account_ucid
  • Buffers: shared hit=49815
5. 0.003 290.710 ↑ 212.0 2 1

Result (cost=254,707.45..256,616.50 rows=424 width=64) (actual time=285.783..290.710 rows=2 loops=1)

  • Output: act.account_ucid, ibxp.ibx_code
  • One-Time Filter: (act.account_ucid = '1000000601'::text)
  • Buffers: shared hit=49815
6. 1.935 290.707 ↑ 212.0 2 1

Subquery Scan on ibxp (cost=254,707.45..256,616.50 rows=424 width=64) (actual time=285.781..290.707 rows=2 loops=1)

  • Output: ibxp.ibx_id, ibxp.account_ucid, ibxp.status, ibxp.ibx_code, ibxp.region, ibxp.account_number, ibxp.account_name
  • Filter: (act.account_ucid = ibxp.account_ucid)
  • Rows Removed by Filter: 22346
  • Buffers: shared hit=49815
7. 18.257 288.772 ↑ 3.8 22,348 1

HashAggregate (cost=254,707.44..255,555.91 rows=84,847 width=222) (actual time=284.003..288.772 rows=22,348 loops=1)

  • Output: loc.ibx_id, ast.account_ucid, ast.status, ast.ibx_code, loc.region, ast.account_number, ast.account_name
  • Group Key: loc.ibx_id, ast.account_ucid, ast.status, ast.ibx_code, loc.region, ast.account_number, ast.account_name
  • Buffers: shared hit=49815
8. 1.884 270.515 ↑ 3.8 22,348 1

Append (cost=251,515.51..253,222.62 rows=84,847 width=222) (actual time=261.619..270.515 rows=22,348 loops=1)

  • Buffers: shared hit=49815
9. 73.480 268.621 ↑ 3.8 22,348 1

HashAggregate (cost=251,515.51..252,363.28 rows=84,777 width=65) (actual time=261.619..268.621 rows=22,348 loops=1)

  • Output: loc.ibx_id, ast.account_ucid, ast.status, ast.ibx_code, loc.region, ast.account_number, ast.account_name
  • Group Key: loc.ibx_id, ast.account_ucid, ast.status, ast.ibx_code, loc.region, ast.account_number, ast.account_name
  • Buffers: shared hit=49815
10. 22.338 195.141 ↓ 1.1 90,293 1

Hash Join (cost=3,709.90..250,031.91 rows=84,777 width=65) (actual time=29.080..195.141 rows=90,293 loops=1)

  • Output: loc.ibx_id, ast.account_ucid, ast.status, ast.ibx_code, loc.region, ast.account_number, ast.account_name
  • Inner Unique: true
  • Hash Cond: (ast.ibx_code = (loc.ibx_code)::text)
  • Buffers: shared hit=49815
11. 152.186 172.556 ↓ 1.0 90,749 1

Bitmap Heap Scan on terminal.asset ast (cost=3,619.75..249,704.16 rows=89,016 width=55) (actual time=28.823..172.556 rows=90,749 loops=1)

  • Output: ast.asset_number, ast.asset_id, ast.account_ucid, ast.account_number, ast.account_name, ast.ibx, ast.ibx_code, ast.status, ast.sub_status, ast.asset_type, ast.asset_description, ast.system_name, ast.installation_date, ast.legacy_asset_flag, ast.legacy_customer_order_number, ast.legacy_system_name, ast.monet_cage_flag, ast.cage_type, ast.cage_unique_space_number, ast.caplogix_cage_unique_space_id, ast.cabinet_number, ast.cabinet_unique_space_number, ast.caplogix_cabinet_unique_space_id, ast.parent_asset_number, ast.parent_product_name, ast.parent_product_part_number, ast.root_asset_number, ast.root_product_name, ast.root_product_part_number, ast.ops_parent_asset_number, ast.ops_root_asset_number, ast.network_cable_number, ast.patch_panel_number, ast.product_description, ast.product_name, ast.product_part_number, ast.ecp_product_name_filter, ast.ecp_product_name, ast.ecp_product_desc, ast.quote_number, ast.serial_number, ast.service_order_number, ast.customer_order_number, ast.customer_reference_number, ast.billing_agreement_number, ast.arbiter_cross_connect_account_name, ast.external_reference_number, ast.final_a_side_account_ucid, ast.final_z_side_account_ucid, ast.final_z_side_asset_number, ast.z_side_unique_space_number, ast.created_by, ast.created_date, ast.last_updated_by, ast.last_updated_date, ast.ignore_updates_flag, ast.deleted_flag, ast.etl_created_by, ast.etl_created_date, ast.etl_data_source, ast.etl_last_updated_by, ast.etl_last_updated_date, ast.wwcs_status, ast.ecp_report_tab_name
  • Recheck Cond: (ast.product_part_number = ANY ('{BS00001.PROD,BS00002.PROD,BS00003.PROD,SPC00001.PROD,SPC00002.PROD,SPC00003.PROD,SPC00004.PROD,SPC00005.PROD,SPC00006.PROD,SPC00007.PROD,SPC00008.PROD,SPC00010.PROD,SPC00011.PROD,SPC00012.PROD,SPC00013.PROD,SPC00014.PROD,SPC00015.PROD,BS00001.PROD.,BS00002.PROD.,BS00003.PROD.,SPC00001.PROD.,SPC00002.PROD.,SPC00003.PROD.,SPC00004.PROD.,SPC00005.PROD.,SPC00006.PROD.,SPC00007.PROD.,SPC00008.PROD.,SPC00010.PROD.,SPC00011.PROD.,SPC00012.PROD.,SPC00013.PROD.,SPC00014.PROD.,SPC00015.PROD.,MS00001.PROD,CC00005.PROD,ECX00001.PROD,ECX00002.PROD,ECX00003.PROD,ECX00008.PROD,IX00008.PROD,IX00001.PROD,IX00003.PROD,IX00007.PROD,IX00002.PROD}'::text[]))
  • Filter: (((NOT ast.deleted_flag) OR (ast.deleted_flag IS NULL)) AND ((ast.wwcs_status <> 'Approved'::text) OR (ast.wwcs_status IS NULL)) AND (ast.status = 'Active'::text))
  • Rows Removed by Filter: 51111
  • Heap Blocks: exact=49007
  • Buffers: shared hit=49731
12. 20.370 20.370 ↓ 1.1 142,261 1

Bitmap Index Scan on asset_product_part_number_idx (cost=0.00..3,597.50 rows=124,010 width=0) (actual time=20.370..20.370 rows=142,261 loops=1)

  • Index Cond: (ast.product_part_number = ANY ('{BS00001.PROD,BS00002.PROD,BS00003.PROD,SPC00001.PROD,SPC00002.PROD,SPC00003.PROD,SPC00004.PROD,SPC00005.PROD,SPC00006.PROD,SPC00007.PROD,SPC00008.PROD,SPC00010.PROD,SPC00011.PROD,SPC00012.PROD,SPC00013.PROD,SPC00014.PROD,SPC00015.PROD,BS00001.PROD.,BS00002.PROD.,BS00003.PROD.,SPC00001.PROD.,SPC00002.PROD.,SPC00003.PROD.,SPC00004.PROD.,SPC00005.PROD.,SPC00006.PROD.,SPC00007.PROD.,SPC00008.PROD.,SPC00010.PROD.,SPC00011.PROD.,SPC00012.PROD.,SPC00013.PROD.,SPC00014.PROD.,SPC00015.PROD.,MS00001.PROD,CC00005.PROD,ECX00001.PROD,ECX00002.PROD,ECX00003.PROD,ECX00008.PROD,IX00008.PROD,IX00001.PROD,IX00003.PROD,IX00007.PROD,IX00002.PROD}'::text[]))
  • Buffers: shared hit=724
13. 0.044 0.247 ↑ 1.0 240 1

Hash (cost=87.15..87.15 rows=240 width=14) (actual time=0.247..0.247 rows=240 loops=1)

  • Output: loc.ibx_id, loc.region, loc.ibx_code
  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=84
14. 0.203 0.203 ↑ 1.0 240 1

Seq Scan on terminal.location loc (cost=0.00..87.15 rows=240 width=14) (actual time=0.006..0.203 rows=240 loops=1)

  • Output: loc.ibx_id, loc.region, loc.ibx_code
  • Filter: (loc.location_status = 'Open'::text)
  • Rows Removed by Filter: 12
  • Buffers: shared hit=84
15. 0.001 0.010 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.00..11.57 rows=70 width=222) (actual time=0.010..0.010 rows=0 loops=1)

  • Output: "*SELECT* 2".ibx_id, "*SELECT* 2".account_ucid, "*SELECT* 2".status, "*SELECT* 2".ibx_code, "*SELECT* 2".region, "*SELECT* 2".account_number, "*SELECT* 2".account_name
16. 0.009 0.009 ↓ 0.0 0 1

Seq Scan on terminal.pods_ods_cust_accounts_ibx_en1 (cost=0.00..10.88 rows=70 width=860) (actual time=0.009..0.009 rows=0 loops=1)

  • Output: (pods_ods_cust_accounts_ibx_en1.ibx_id)::numeric(38,0), pods_ods_cust_accounts_ibx_en1.ucm_id, pods_ods_cust_accounts_ibx_en1.status, pods_ods_cust_accounts_ibx_en1.ibx_code, pods_ods_cust_accounts_ibx_en1.region, pods_ods_cust_accounts_ibx_en1.cust_acct_number, pods_ods_cust_accounts_ibx_en1.cust_acct_name