explain.depesz.com

PostgreSQL's explain analyze made readable

Result: axx6

Settings
# exclusive inclusive rows x rows loops node
1. 2,139.549 115,450.130 ↓ 1.3 272,934 1

Sort (cost=14,361,396.15..14,361,501.25 rows=210,215 width=679) (actual time=115,080.830..115,450.130 rows=272,934 loops=1)

  • Sort Key: a.createddate DESC, a.sfid DESC, service_component__c.sfid DESC
  • Sort Method: external merge Disk: 783624kB
2. 100,962.070 113,310.581 ↓ 1.3 272,934 1

Gather (cost=128,227.64..14,312,514.23 rows=210,215 width=679) (actual time=7,033.388..113,310.581 rows=272,934 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
3. 1,777.942 12,348.147 ↓ 1.1 136,467 2

Nested Loop Left Join (cost=127,227.64..259,738.21 rows=123,656 width=515) (actual time=7,066.965..12,348.147 rows=136,467 loops=2)

4. 2,969.216 10,570.193 ↓ 1.1 136,467 2

Nested Loop Left Join (cost=127,227.55..193,881.83 rows=123,656 width=467) (actual time=7,066.950..10,570.193 rows=136,467 loops=2)

5. 463.172 7,600.957 ↓ 1.1 136,467 2

Merge Left Join (cost=127,227.47..128,025.44 rows=123,656 width=419) (actual time=7,066.881..7,600.957 rows=136,467 loops=2)

  • Merge Cond: ((a.sfid)::text = (service_component__c.service__c)::text)
  • Join Filter: ((a.product__c)::text <> ALL ('{Colocation,"Colo Cloud Servers","Colo Managed Services"}'::text[]))
  • Rows Removed by Join Filter: 17297
6. 1,373.233 3,396.180 ↑ 1.2 104,900 2

Sort (cost=106,062.61..106,124.44 rows=123,656 width=314) (actual time=3,374.684..3,396.180 rows=104,900 loops=2)

  • Sort Key: a.sfid
  • Sort Method: quicksort Memory: 45542kB
7. 1,191.430 2,022.947 ↑ 1.2 104,900 2

Nested Loop Left Join (cost=7,867.09..103,970.85 rows=123,656 width=314) (actual time=128.973..2,022.947 rows=104,900 loops=2)

8. 77.720 831.506 ↑ 1.2 104,900 2

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

  • Hash Cond: ((a.account__c)::text = (b.sfid)::text)
9. 626.314 626.314 ↑ 1.2 104,901 2

Parallel Seq Scan on service__c a (cost=0.00..30,306.16 rows=123,738 width=239) (actual time=0.012..626.314 rows=104,901 loops=2)

  • Filter: ((status__c)::text <> ALL ('{Disconnected,Billed}'::text[]))
  • Rows Removed by Filter: 56638
10. 52.271 127.472 ↑ 1.0 184,308 2

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

  • Buckets: 262144 Batches: 1 Memory Usage: 15335kB
11. 75.201 75.201 ↑ 1.0 184,308 2

Seq Scan on account b (cost=0.00..7,221.92 rows=184,308 width=46) (actual time=0.015..75.201 rows=184,308 loops=2)

12. 0.011 0.011 ↓ 0.0 0 209,800

Index Scan using hcu_idx_location__c_sfid on location__c l (cost=0.09..0.53 rows=1 width=48) (actual time=0.011..0.011 rows=0 loops=209,800)

  • Index Cond: ((a.service_location_a__c)::text = (sfid)::text)
13. 3,013.580 3,741.605 ↓ 1.0 276,950 2

Sort (cost=21,164.85..21,303.30 rows=276,898 width=124) (actual time=3,692.173..3,741.605 rows=276,950 loops=2)

  • Sort Key: service_component__c.service__c
  • Sort Method: quicksort Memory: 54004kB
14. 728.025 728.025 ↓ 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.341..728.025 rows=276,975 loops=2)

  • Filter: ((status__c)::text <> 'Disconnected'::text)
  • Rows Removed by Filter: 103232
15. 0.020 0.020 ↑ 1.0 1 272,934

Index Scan using hcu_idx_location__c_sfid on location__c e (cost=0.09..0.53 rows=1 width=48) (actual time=0.020..0.020 rows=1 loops=272,934)

  • Index Cond: (COALESCE(NULLIF((service_component__c.location_a__c)::text, ''::text), NULLIF((a.service_location_a__c)::text, ''::text)) = (sfid)::text)
16. 0.012 0.012 ↑ 1.0 1 272,934

Index Scan using hcu_idx_location__c_sfid on location__c f (cost=0.09..0.53 rows=1 width=48) (actual time=0.012..0.012 rows=1 loops=272,934)

  • Index Cond: (COALESCE(NULLIF((service_component__c.location_z__c)::text, ''::text), NULLIF((a.service_location_z__c)::text, ''::text)) = (sfid)::text)
17.          

SubPlan (forGather)

18. 0.032 0.231 ↑ 1.0 1 272,934

Aggregate (cost=34.53..34.53 rows=1 width=32) (actual time=0.231..0.231 rows=1 loops=272,934)

19. 0.004 0.199 ↓ 2.0 6 272,934

Subquery Scan on t (cost=34.52..34.53 rows=3 width=151) (actual time=0.196..0.199 rows=6 loops=272,934)

20. 0.027 0.195 ↓ 2.0 6 272,934

Sort (cost=34.52..34.52 rows=3 width=253) (actual time=0.195..0.195 rows=6 loops=272,934)

  • Sort Key: h.sfid DESC
  • Sort Method: quicksort Memory: 25kB
21. 0.000 0.168 ↓ 2.0 6 272,934

Result (cost=0.26..34.51 rows=3 width=253) (actual time=0.039..0.168 rows=6 loops=272,934)

  • One-Time Filter: ((a.product__c)::text <> ALL ('{Colocation,"Colo Cloud Servers","Colo Managed Services"}'::text[]))
22. 0.058 0.170 ↓ 2.0 6 267,390

Nested Loop Left Join (cost=0.26..34.51 rows=3 width=253) (actual time=0.039..0.170 rows=6 loops=267,390)

23. 0.074 0.104 ↓ 2.0 6 267,390

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

24. 0.017 0.017 ↓ 2.0 6 267,390

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

  • Index Cond: ((a.sfid)::text = (service__c)::text)
  • Filter: ((status__c)::text <> 'Disconnected'::text)
  • Rows Removed by Filter: 1
25. 0.013 0.013 ↑ 1.0 1 1,678,819

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

  • Index Cond: ((h.location_a__c)::text = (sfid)::text)
26. 0.008 0.008 ↑ 1.0 1 1,678,819

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

  • Index Cond: (NULLIF((h.location_z__c)::text, ''::text) = (sfid)::text)
27. 0.006 0.023 ↑ 1.0 1 272,934

Aggregate (cost=9.96..9.96 rows=1 width=32) (actual time=0.023..0.023 rows=1 loops=272,934)

28. 0.001 0.017 ↓ 2.0 6 272,934

Result (cost=0.08..9.95 rows=3 width=19) (actual time=0.014..0.017 rows=6 loops=272,934)

  • One-Time Filter: ((a.product__c)::text <> ALL ('{Colocation,"Colo Cloud Servers","Colo Managed Services"}'::text[]))
29. 0.016 0.016 ↓ 2.0 6 267,390

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

  • Index Cond: ((a.sfid)::text = (service__c)::text)
  • Filter: ((status__c)::text <> 'Disconnected'::text)
  • Rows Removed by Filter: 1
30. 0.010 0.110 ↑ 1.0 1 272,934

Aggregate (cost=22.24..22.24 rows=1 width=148) (actual time=0.110..0.110 rows=1 loops=272,934)

31. 0.000 0.100 ↓ 2.0 6 272,934

Result (cost=0.17..22.23 rows=3 width=29) (actual time=0.026..0.100 rows=6 loops=272,934)

  • One-Time Filter: ((a.product__c)::text <> ALL ('{Colocation,"Colo Cloud Servers","Colo Managed Services"}'::text[]))
32. 0.072 0.101 ↓ 2.0 6 267,390

Nested Loop Left Join (cost=0.17..22.23 rows=3 width=29) (actual time=0.026..0.101 rows=6 loops=267,390)

33. 0.016 0.016 ↓ 2.0 6 267,390

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

  • Index Cond: ((a.sfid)::text = (service__c)::text)
  • Filter: ((status__c)::text <> 'Disconnected'::text)
  • Rows Removed by Filter: 1
34. 0.013 0.013 ↑ 1.0 1 1,678,819

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

  • Index Cond: ((i.location_a__c)::text = (sfid)::text)