explain.depesz.com

PostgreSQL's explain analyze made readable

Result: k1uQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 501.441 ↓ 0.0 0 1

Limit (cost=140,590.20..140,590.30 rows=1 width=358) (actual time=501.441..501.441 rows=0 loops=1)

2.          

CTE rankquery

3. 11.864 87.893 ↓ 6.0 6 1

Gather (cost=1,539.50..70,622.79 rows=1 width=38) (actual time=22.887..87.893 rows=6 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 7.623 76.029 ↓ 2.0 2 3

Hash Join (cost=539.50..69,622.59 rows=1 width=153) (actual time=12.271..76.029 rows=2 loops=3)

  • Hash Cond: ((ed.service_request__r__externalid__c)::text = (sr_1.externalid__c)::text)
  • Join Filter: (((ed.service_request__c)::text = (sr_1.sfid)::text) OR ((COALESCE(sr_1.sfid, ''::character varying))::text = ''::text))
5. 61.746 61.746 ↑ 1.2 154,755 3

Parallel Seq Scan on emergency_dispatch__c ed (cost=0.00..67,874.20 rows=193,420 width=153) (actual time=0.024..61.746 rows=154,755 loops=3)

6. 0.009 6.660 ↑ 2.5 6 3

Hash (cost=539.31..539.31 rows=15 width=26) (actual time=6.660..6.660 rows=6 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 0.171 6.651 ↓ 2.6 39 3

Bitmap Heap Scan on service_request1__c sr_1 (cost=475.31..539.31 rows=15 width=26) (actual time=6.514..6.651 rows=39 loops=3)

  • Recheck Cond: (((status__c)::text = 'Initiate Emergency Dispatch'::text) AND (createddate >= '2019-04-14 13:51:31'::timestamp without time zone) AND (createddate < '2019-05-15 13:51:31'::timestamp without time zone))
  • Filter: ((NOT manually_dispatched__c) OR (manually_dispatched__c IS NULL))
  • Heap Blocks: exact=37
8. 0.200 6.480 ↓ 0.0 0 3

BitmapAnd (cost=475.31..475.31 rows=16 width=0) (actual time=6.480..6.480 rows=0 loops=3)

9. 0.599 0.599 ↓ 1.1 2,029 3

Bitmap Index Scan on ah_service_request1_status (cost=0.00..42.12 rows=1,825 width=0) (actual time=0.599..0.599 rows=2,029 loops=3)

  • Index Cond: ((status__c)::text = 'Initiate Emergency Dispatch'::text)
10. 5.681 5.681 ↓ 1.1 21,799 3

Bitmap Index Scan on hc_idx_service_request1__c_createddate (cost=0.00..432.93 rows=20,450 width=0) (actual time=5.681..5.681 rows=21,799 loops=3)

  • Index Cond: ((createddate >= '2019-04-14 13:51:31'::timestamp without time zone) AND (createddate < '2019-05-15 13:51:31'::timestamp without time zone))
11. 0.014 501.439 ↓ 3.0 3 1

Result (cost=69,967.32..69,967.41 rows=1 width=358) (actual time=501.434..501.439 rows=3 loops=1)

12. 0.014 501.425 ↓ 3.0 3 1

Sort (cost=69,967.32..69,967.32 rows=1 width=334) (actual time=501.424..501.425 rows=3 loops=1)

  • Sort Key: (COALESCE(to_char(timezone('US/Pacific'::text, timezone('UTC'::text, sr.createddate)), 'MM/DD/YYYY HH:MI AM'::text), ''::text))
  • Sort Method: quicksort Memory: 25kB
13. 0.028 501.411 ↓ 3.0 3 1

WindowAgg (cost=1,542.76..69,967.31 rows=1 width=334) (actual time=501.406..501.411 rows=3 loops=1)

14. 3.440 501.383 ↓ 3.0 3 1

Nested Loop Left Join (cost=1,542.76..69,967.29 rows=1 width=155) (actual time=500.993..501.383 rows=3 loops=1)

  • Join Filter: (((sr.externalid__c)::text = (ra.externalid__c)::text) OR ((sr.sfid)::text = (ra.sfid)::text))
  • Rows Removed by Join Filter: 42955
  • Filter: (wod.manual_dispatch_only__c OR (((sr.status__c)::text = 'Initiate Emergency Dispatch'::text) AND (NOT wod.manual_dispatch_only__c) AND (ra.total_phone_tree <= ra.total_phone_result) AND (ra.total_phone_result > 0)) OR ((( (...)
  • Rows Removed by Filter: 7157
15. 3.109 404.863 ↓ 7,160.0 7,160 1

Nested Loop Left Join (cost=1,542.76..69,967.24 rows=1 width=156) (actual time=14.397..404.863 rows=7,160 loops=1)

16. 3.194 337.314 ↓ 7,160.0 7,160 1

Nested Loop Left Join (cost=1,542.34..69,960.75 rows=1 width=167) (actual time=14.365..337.314 rows=7,160 loops=1)

17. 5.372 291.160 ↓ 7,160.0 7,160 1

Nested Loop (cost=1,541.92..69,954.71 rows=1 width=176) (actual time=14.325..291.160 rows=7,160 loops=1)

18. 12.322 235.668 ↓ 7,160.0 7,160 1

Nested Loop (cost=1,541.64..69,949.17 rows=1 width=153) (actual time=14.308..235.668 rows=7,160 loops=1)

19. 11.729 109.470 ↓ 4,067.0 8,134 1

Hash Join (cost=1,541.35..69,937.67 rows=2 width=170) (actual time=14.185..109.470 rows=8,134 loops=1)

  • Hash Cond: ((sr.property_consultant__c)::text = (c.sfid)::text)
20. 89.882 95.868 ↓ 1.0 20,740 1

Bitmap Heap Scan on service_request1__c sr (cost=437.90..68,782.02 rows=19,881 width=174) (actual time=12.292..95.868 rows=20,740 loops=1)

  • Recheck Cond: ((createddate >= '2019-04-14 13:51:31'::timestamp without time zone) AND (createddate < '2019-05-15 13:51:31'::timestamp without time zone))
  • Filter: ((NOT manually_dispatched__c) OR (manually_dispatched__c IS NULL))
  • Rows Removed by Filter: 738
  • Heap Blocks: exact=16722
21. 5.986 5.986 ↓ 1.1 21,799 1

Bitmap Index Scan on hc_idx_service_request1__c_createddate (cost=0.00..432.93 rows=20,450 width=0) (actual time=5.986..5.986 rows=21,799 loops=1)

  • Index Cond: ((createddate >= '2019-04-14 13:51:31'::timestamp without time zone) AND (createddate < '2019-05-15 13:51:31'::timestamp without time zone))
22. 0.279 1.873 ↑ 7.7 684 1

Hash (cost=1,037.44..1,037.44 rows=5,281 width=34) (actual time=1.873..1.873 rows=684 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 108kB
23. 1.594 1.594 ↑ 7.7 684 1

Index Scan using hc_idx_contact_recordtypeid on contact c (cost=0.56..1,037.44 rows=5,281 width=34) (actual time=0.041..1.594 rows=684 loops=1)

  • Index Cond: ((recordtypeid)::text = '0120H000001O7IWQA0'::text)
24. 113.876 113.876 ↑ 1.0 1 8,134

Index Scan using work_order_dispatch__c_idx_sfid on work_order_dispatch__c wod (cost=0.29..5.75 rows=1 width=20) (actual time=0.014..0.014 rows=1 loops=8,134)

  • Index Cond: ((sfid)::text = (sr.work_order_dispatch__c)::text)
25. 50.120 50.120 ↑ 1.0 1 7,160

Index Scan using account_idx_sfid on account ac (cost=0.28..5.54 rows=1 width=42) (actual time=0.007..0.007 rows=1 loops=7,160)

  • Index Cond: ((sfid)::text = (sr.account_name__c)::text)
26. 42.960 42.960 ↑ 1.0 1 7,160

Index Scan using service_request_type__c_idx_sfid on service_request_type__c srt (cost=0.42..6.03 rows=1 width=28) (actual time=0.006..0.006 rows=1 loops=7,160)

  • Index Cond: ((sfid)::text = (sr.service_request_type__c)::text)
27. 64.440 64.440 ↑ 1.0 1 7,160

Index Scan using property__c_idx_sfid on property__c p (cost=0.42..6.49 rows=1 width=63) (actual time=0.009..0.009 rows=1 loops=7,160)

  • Index Cond: ((sfid)::text = (sr.property_of_interest__c)::text)
  • Filter: ((account__c)::text = (sr.account_name__c)::text)
28. 93.080 93.080 ↓ 6.0 6 7,160

CTE Scan on rankquery ra (cost=0.00..0.02 rows=1 width=124) (actual time=0.003..0.013 rows=6 loops=7,160)