explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HWTt

Settings
# exclusive inclusive rows x rows loops node
1. 95,342.482 109,727.252 ↓ 1.3 272,935 1

Gather (cost=62,494.91..14,723,043.23 rows=210,215 width=576) (actual time=6,622.206..109,727.252 rows=272,935 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
2.          

SubPlan (forGather)

3. 14,384.419 14,384.770 ↓ 1.1 136,468 2

Nested Loop Left Join (cost=61,494.91..194,005.48 rows=123,656 width=467) (actual time=6,589.396..14,384.770 rows=136,468 loops=2)

4. 0.175 0.175 ↑ 1.0 1 272,935

Aggregate (cost=34.51..34.52 rows=1 width=32) (actual time=0.175..0.175 rows=1 loops=272,935)

5. 0.000 0.176 ↑ 1.0 1 272,935

Aggregate (cost=34.48..34.49 rows=1 width=32) (actual time=0.176..0.176 rows=1 loops=272,935)

6. 11,730.320 11,730.320 ↓ 1.1 136,468 2

Nested Loop Left Join (cost=61,494.83..128,149.10 rows=123,656 width=419) (actual time=6,589.381..11,730.320 rows=136,468 loops=2)

7. 0.000 0.018 ↑ 1.0 1 272,935

Index Scan using hcu_idx_location__c_sfid on location__c locz (cost=0.09..0.53 rows=1 width=48) (actual time=0.018..0.018 rows=1 loops=272,935)

  • Index Cond: (COALESCE(NULLIF((service_component__c.location_z__c)::text, ''::text), NULLIF((svc.service_location_z__c)::text, ''::text)) = (sfid)::text)
8. 0.162 0.162 ↓ 2.2 9 272,935

Result (cost=4.26..34.48 rows=4 width=29) (actual time=0.036..0.162 rows=9 loops=272,935)

9. 0.000 0.157 ↓ 2.0 6 272,935

Result (cost=0.26..34.51 rows=3 width=185) (actual time=0.037..0.157 rows=6 loops=272,935)

  • One-Time Filter: ((svc.product__c)::text <> ALL ('{Colocation,"Colo Cloud Servers","Colo Managed Services"}'::text[]))
  • One-Time Filter: ((svc.product__c)::text <> ALL ('{Colocation,"Colo Cloud Servers","Colo Managed Services"}'::text[]))
10. 7,103.043 7,103.043 ↓ 1.1 136,468 2

Merge Left Join (cost=61,494.74..62,292.72 rows=123,656 width=371) (actual time=6,588.449..7,103.043 rows=136,468 loops=2)

11. 0.000 0.032 ↑ 1.0 1 272,935

Index Scan using hcu_idx_location__c_sfid on location__c loca (cost=0.09..0.53 rows=1 width=48) (actual time=0.032..0.032 rows=1 loops=272,935)

  • Rows Removed by Join Filter: 17297
  • Merge Cond: ((svc.sfid)::text = (service_component__c.service__c)::text)
  • Join Filter: ((svc.product__c)::text <> ALL ('{Colocation,"Colo Cloud Servers","Colo Managed Services"}'::text[]))
  • Index Cond: (COALESCE(NULLIF((service_component__c.location_a__c)::text, ''::text), NULLIF((svc.service_location_a__c)::text, ''::text)) = (sfid)::text)
12. 0.163 0.163 ↓ 2.2 9 267,390

Nested Loop Left Join (cost=4.26..34.48 rows=4 width=29) (actual time=0.037..0.163 rows=9 loops=267,390)

13. 0.000 0.159 ↓ 2.0 6 267,390

Nested Loop Left Join (cost=0.26..34.51 rows=3 width=185) (actual time=0.037..0.159 rows=6 loops=267,390)

14. 2,687.507 2,687.507 ↑ 1.2 104,900 2

Sort (cost=40,329.89..40,391.72 rows=123,656 width=266) (actual time=2,669.003..2,687.507 rows=104,900 loops=2)

15. 3,965.881 3,966.034 ↓ 1.0 276,975 2

Sort (cost=21,164.85..21,303.30 rows=276,898 width=124) (actual time=3,919.427..3,966.034 rows=276,975 loops=2)

  • Sort Method: quicksort Memory: 54004kB
  • Sort Method: quicksort Memory: 45773kB
  • Sort Key: svc.sfid
  • Sort Key: service_component__c.service__c
16. 0.107 0.107 ↓ 2.0 6 267,390

Nested Loop Left Join (cost=0.17..22.22 rows=3 width=156) (actual time=0.028..0.107 rows=6 loops=267,390)

17. 0.008 0.008 ↑ 1.0 1 1,678,819

Index Scan using hcu_idx_location__c_sfid on location__c locz1 (cost=0.09..4.09 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=1,678,819)

18. 0.016 0.016 ↓ 2.0 6 267,390

Index Scan using hc_idx_service_component__c_service__c on service_component__c sc2 (cost=0.08..9.95 rows=3 width=38) (actual time=0.013..0.016 rows=6 loops=267,390)

19. 0.000 0.022 ↑ 2.0 1 1,678,819

Bitmap Heap Scan on location__c locs1 (cost=4.17..8.17 rows=2 width=48) (actual time=0.022..0.022 rows=1 loops=1,678,819)

  • Rows Removed by Filter: 1
  • Recheck Cond: (((sc2.location_a__c)::text = (sfid)::text) OR ((sc2.location_z__c)::text = (sfid)::text))
  • Index Cond: (NULLIF((sc1.location_z__c)::text, ''::text) = (sfid)::text)
  • Index Cond: ((svc.sfid)::text = (service__c)::text)
  • Heap Blocks: exact=2392144
  • Filter: ((status__c)::text <> 'Disconnected'::text)
20. 701.363 701.363 ↓ 1.0 276,975 2

Seq Scan on service_component__c (cost=0.00..16,158.82 rows=276,898 width=124) (actual time=0.337..701.363 rows=276,975 loops=2)

21. 1,219.650 1,219.701 ↑ 1.2 104,900 2

Hash Join (cost=7,867.00..38,238.13 rows=123,656 width=266) (actual time=206.484..1,219.701 rows=104,900 loops=2)

  • Rows Removed by Filter: 103232
  • Hash Cond: ((svc.account__c)::text = (acct.sfid)::text)
  • Filter: ((status__c)::text <> 'Disconnected'::text)
22. 0.013 0.013 ↑ 1.0 1 1,678,819

Index Scan using hcu_idx_location__c_sfid on location__c loca1 (cost=0.09..4.09 rows=1 width=48) (actual time=0.013..0.013 rows=1 loops=1,678,819)

23. 0.017 0.017 ↓ 2.0 6 267,390

Index Scan using hc_idx_service_component__c_service__c on service_component__c sc1 (cost=0.08..9.95 rows=3 width=127) (actual time=0.014..0.017 rows=6 loops=267,390)

24. 0.000 0.021 ↓ 0.0 0 1,678,819

BitmapOr (cost=4.17..4.17 rows=2 width=0) (actual time=0.021..0.021 rows=0 loops=1,678,819)

  • Rows Removed by Filter: 1
  • Index Cond: ((svc.sfid)::text = (service__c)::text)
  • Index Cond: ((sc1.location_a__c)::text = (sfid)::text)
  • Filter: ((status__c)::text <> 'Disconnected'::text)
25. 923.910 923.910 ↑ 1.2 104,902 2

Parallel Seq Scan on service__c svc (cost=0.00..30,306.16 rows=123,738 width=239) (actual time=0.010..923.910 rows=104,902 loops=2)

26. 205.643 205.664 ↑ 1.0 184,308 2

Hash (cost=7,221.92..7,221.92 rows=184,308 width=46) (actual time=205.664..205.664 rows=184,308 loops=2)

  • Rows Removed by Filter: 56638
  • Filter: ((status__c)::text <> ALL ('{Disconnected,Billed}'::text[]))
  • Buckets: 262144 Batches: 1 Memory Usage: 15335kB
27. 0.013 0.013 ↑ 1.0 1 1,678,819

Bitmap Index Scan on hcu_idx_location__c_sfid (cost=0.00..2.09 rows=1 width=0) (actual time=0.013..0.013 rows=1 loops=1,678,819)

28. 0.000 0.008 ↑ 1.0 1 1,678,819

Bitmap Index Scan on hcu_idx_location__c_sfid (cost=0.00..2.09 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1,678,819)

  • Index Cond: ((sc2.location_z__c)::text = (sfid)::text)
  • Index Cond: ((sc2.location_a__c)::text = (sfid)::text)
29. 130.952 130.952 ↑ 1.0 184,308 2

Seq Scan on account acct (cost=0.00..7,221.92 rows=184,308 width=46) (actual time=0.014..130.952 rows=184,308 loops=2)