explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CRH5

Settings
# exclusive inclusive rows x rows loops node
1. 0.102 484.987 ↑ 1.0 20 1

GroupAggregate (cost=111,340.05..111,341.20 rows=20 width=192) (actual time=484.889..484.987 rows=20 loops=1)

  • Group Key: cr.account, cr.property, cr.callabandoneddropped, cr.currentresident, cr.maintenancecustomerservicecall, cr.applicationstatuscheck, cr.needsspecificleasingagent, cr.appointmentrunninglatecancelling, cr.newproblem, cr.vendor, cr.wrongnumber, cr (...)
2.          

CTE callresult

3. 0.687 476.697 ↑ 1.0 20 1

Limit (cost=110,065.91..110,065.96 rows=20 width=270) (actual time=476.010..476.697 rows=20 loops=1)

4. 0.619 476.010 ↑ 37.1 20 1

Sort (cost=110,065.91..110,067.77 rows=743 width=270) (actual time=476.008..476.010 rows=20 loops=1)

  • Sort Key: (COALESCE(ac.name, ''::character varying)), (COALESCE(p.name, ''::character varying))
  • Sort Method: top-N heapsort Memory: 34kB
5. 0.695 475.391 ↓ 1.6 1,181 1

WindowAgg (cost=109,901.28..110,046.14 rows=743 width=270) (actual time=475.110..475.391 rows=1,181 loops=1)

6. 1.305 474.696 ↓ 1.6 1,181 1

Finalize GroupAggregate (cost=109,901.28..110,036.85 rows=743 width=198) (actual time=465.928..474.696 rows=1,181 loops=1)

  • Group Key: p.name, p.sfid, ac.name, ac.sfid
7. 0.000 473.391 ↓ 3.4 2,086 1

Gather Merge (cost=109,901.28..110,001.52 rows=620 width=198) (actual time=465.916..473.391 rows=2,086 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 4.797 1,356.822 ↓ 2.2 695 3

Partial GroupAggregate (cost=108,901.26..108,929.93 rows=310 width=198) (actual time=450.608..452.274 rows=695 loops=3)

  • Group Key: p.name, p.sfid, ac.name, ac.sfid
9. 28.197 1,352.025 ↓ 4.6 1,429 3

Sort (cost=108,901.26..108,902.03 rows=310 width=106) (actual time=450.598..450.675 rows=1,429 loops=3)

  • Sort Key: p.name, p.sfid, ac.name, ac.sfid
  • Sort Method: quicksort Memory: 294kB
10. 3.828 1,323.828 ↓ 4.6 1,429 3

Nested Loop Left Join (cost=69,799.43..108,888.43 rows=310 width=106) (actual time=373.975..441.276 rows=1,429 loops=3)

11. 2.205 1,281.426 ↓ 4.6 1,429 3

Hash Join (cost=69,799.01..106,493.60 rows=310 width=81) (actual time=373.912..427.142 rows=1,429 loops=3)

  • Hash Cond: ((ca.accountid)::text = (ac.sfid)::text)
12. 880.008 1,250.352 ↓ 4.7 1,510 3

Parallel Bitmap Heap Scan on "case" ca (cost=69,236.89..105,930.64 rows=323 width=58) (actual time=364.243..416.784 rows=1,510 loops=3)

  • Recheck Cond: ((createddate >= '2019-03-01 05:00:00'::timestamp without time zone) AND (createddate < '2019-03-03 05:00:00'::timestamp without time zone) AND ((status)::text = ANY ('{Closed,"C (...)
  • Rows Removed by Index Recheck: 6060
  • Filter: ((reason IS NOT NULL) AND ic_call_answered_by_ah__c)
  • Rows Removed by Filter: 1964
  • Heap Blocks: exact=9870
13. 1.900 370.344 ↓ 0.0 0 1

BitmapAnd (cost=69,236.89..69,236.89 rows=9,608 width=0) (actual time=370.344..370.344 rows=0 loops=1)

14. 13.840 13.840 ↓ 1.1 37,184 1

Bitmap Index Scan on hc_idx_case_createddate (cost=0.00..718.27 rows=34,184 width=0) (actual time=13.840..13.840 rows=37,184 loops=1)

  • Index Cond: ((createddate >= '2019-03-01 05:00:00'::timestamp without time zone) AND (createddate < '2019-03-03 05:00:00'::timestamp without time zone))
15. 354.604 354.604 ↑ 1.0 3,606,481 1

Bitmap Index Scan on hc_idx_case_status (cost=0.00..68,517.98 rows=3,620,949 width=0) (actual time=354.604..354.604 rows=3,606,481 loops=1)

  • Index Cond: ((status)::text = ANY ('{Closed,"Closed - SP"}'::text[]))
16. 3.390 28.869 ↑ 1.0 2,661 3

Hash (cost=528.72..528.72 rows=2,672 width=42) (actual time=9.623..9.623 rows=2,661 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 226kB
17. 25.479 25.479 ↑ 1.0 2,661 3

Seq Scan on account ac (cost=0.00..528.72 rows=2,672 width=42) (actual time=0.026..8.493 rows=2,661 loops=3)

18. 38.574 38.574 ↑ 1.0 1 4,286

Index Scan using property__c_idx_sfid on property__c p (cost=0.42..7.73 rows=1 width=44) (actual time=0.009..0.009 rows=1 loops=4,286)

  • Index Cond: ((sfid)::text = (ca.property_of_interest__c)::text)
19. 0.185 484.885 ↓ 3.0 61 1

Sort (cost=1,274.09..1,274.14 rows=20 width=203) (actual time=484.883..484.885 rows=61 loops=1)

  • Sort Key: cr.account, cr.property, cr.callabandoneddropped, cr.currentresident, cr.maintenancecustomerservicecall, cr.applicationstatuscheck, cr.needsspecificleasingagent, cr.appointmentrunninglatecancelling, cr.newproblem, cr.vendor, cr.wrongnumbe (...)
  • Sort Method: quicksort Memory: 41kB
20. 0.045 484.700 ↓ 3.0 61 1

Nested Loop Left Join (cost=59.63..1,273.66 rows=20 width=203) (actual time=476.744..484.700 rows=61 loops=1)

21. 476.715 476.715 ↑ 1.0 20 1

CTE Scan on callresult cr (cost=0.00..0.40 rows=20 width=292) (actual time=476.013..476.715 rows=20 loops=1)

22. 0.860 7.940 ↓ 3.0 3 20

Bitmap Heap Scan on "case" ca1 (cost=59.63..63.65 rows=1 width=57) (actual time=0.380..0.397 rows=3 loops=20)

  • Recheck Cond: (((accountid)::text = (cr.ac_sfid)::text) AND (createddate >= '2019-03-01 05:00:00'::timestamp without time zone) AND (createddate < '2019-03-03 05:00:00'::timestamp without time zone) AND ((property_of_interest__c)::text (...)
  • Filter: ((reason IS NULL) AND ic_call_answered_by_ah__c AND ((status)::text <> ALL ('{Closed,"Closed - SP"}'::text[])))
  • Rows Removed by Filter: 12
  • Heap Blocks: exact=277
23. 0.120 7.080 ↓ 0.0 0 20

BitmapAnd (cost=59.63..59.63 rows=1 width=0) (actual time=0.354..0.354 rows=0 loops=20)

24. 0.360 0.360 ↑ 1.6 118 20

Bitmap Index Scan on ah_idx_case_multicol (cost=0.00..14.99 rows=194 width=0) (actual time=0.018..0.018 rows=118 loops=20)

  • Index Cond: (((accountid)::text = (cr.ac_sfid)::text) AND (createddate >= '2019-03-01 05:00:00'::timestamp without time zone) AND (createddate < '2019-03-03 05:00:00'::timestamp without time zone))
25. 6.600 6.600 ↓ 1.8 2,852 20

Bitmap Index Scan on hc_idx_case_property_of_interest__c (cost=0.00..44.40 rows=1,578 width=0) (actual time=0.330..0.330 rows=2,852 loops=20)

  • Index Cond: ((property_of_interest__c)::text = (cr.p_sfid)::text)
Planning time : 5.881 ms
Execution time : 485.698 ms