explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S8dE

Settings
# exclusive inclusive rows x rows loops node
1. 0.736 521.459 ↑ 1.0 20 1

Limit (cost=110,065.91..111,339.47 rows=20 width=278) (actual time=513.281..521.459 rows=20 loops=1)

2. 0.019 520.723 ↑ 37.1 20 1

Result (cost=110,065.91..157,378.66 rows=743 width=278) (actual time=513.279..520.723 rows=20 loops=1)

3. 0.639 512.504 ↑ 37.1 20 1

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

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

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

5. 1.217 511.170 ↓ 1.6 1,181 1

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

  • Group Key: p.name, p.sfid, ac.name, ac.sfid
6. 0.000 509.953 ↓ 3.3 2,070 1

Gather Merge (cost=109,901.28..110,001.52 rows=620 width=198) (actual time=502.477..509.953 rows=2,070 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 4.854 1,466.844 ↓ 2.2 690 3

Partial GroupAggregate (cost=108,901.26..108,929.93 rows=310 width=198) (actual time=487.263..488.948 rows=690 loops=3)

  • Group Key: p.name, p.sfid, ac.name, ac.sfid
8. 32.784 1,461.990 ↓ 4.6 1,429 3

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

  • Sort Key: p.name, p.sfid, ac.name, ac.sfid
  • Sort Method: quicksort Memory: 298kB
9. 3.606 1,429.206 ↓ 4.6 1,429 3

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

10. 2.199 1,387.026 ↓ 4.6 1,429 3

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

  • Hash Cond: ((ca.accountid)::text = (ac.sfid)::text)
11. 942.526 1,348.113 ↓ 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=396.824..449.371 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,"Clo (...)
  • 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=9893
12. 1.934 405.587 ↓ 0.0 0 1

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

13. 15.960 15.960 ↓ 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=15.960..15.960 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))
14. 387.693 387.693 ↑ 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=387.693..387.693 rows=3,606,481 loops=1)

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

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

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

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

17. 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)
18.          

SubPlan (forResult)

19. 0.060 8.200 ↑ 1.0 1 20

Aggregate (cost=63.66..63.67 rows=1 width=8) (actual time=0.410..0.410 rows=1 loops=20)

20. 0.920 8.140 ↓ 3.0 3 20

Bitmap Heap Scan on "case" ca1 (cost=59.63..63.65 rows=1 width=19) (actual time=0.389..0.407 rows=3 loops=20)

  • Recheck Cond: (((accountid)::text = (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
21. 0.100 7.220 ↓ 0.0 0 20

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

22. 0.380 0.380 ↑ 1.6 118 20

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

  • Index Cond: (((accountid)::text = (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))
23. 6.740 6.740 ↓ 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.337..0.337 rows=2,852 loops=20)

  • Index Cond: ((property_of_interest__c)::text = (p.sfid)::text)