explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eKcs

Settings
# exclusive inclusive rows x rows loops node
1. 0.319 6,844.818 ↓ 194.0 194 1

WindowAgg (cost=976,778.10..976,778.15 rows=1 width=1,256) (actual time=6,844.662..6,844.818 rows=194 loops=1)

2.          

CTE casequery

3. 0.404 5,778.020 ↓ 11.1 266 1

WindowAgg (cost=975,266.23..975,266.53 rows=24 width=98) (actual time=5,777.806..5,778.020 rows=266 loops=1)

4. 79.748 5,777.616 ↓ 11.1 266 1

GroupAggregate (cost=975,266.23..975,266.43 rows=24 width=58) (actual time=5,669.165..5,777.616 rows=266 loops=1)

  • Group Key: co.sfid, co.firstname, co.lastname
5. 481.162 5,697.868 ↓ 7,757.9 186,189 1

Sort (cost=975,266.23..975,266.24 rows=24 width=59) (actual time=5,669.150..5,697.868 rows=186,189 loops=1)

  • Sort Key: co.sfid, co.firstname, co.lastname
  • Sort Method: quicksort Memory: 32,134kB
6. 67.674 5,216.706 ↓ 7,757.9 186,189 1

Hash Join (cost=452,070.61..975,266.12 rows=24 width=59) (actual time=3,533.413..5,216.706 rows=186,189 loops=1)

  • Hash Cond: ((ca.property_consultant__c)::text = (co.sfid)::text)
7. 1,678.054 5,144.735 ↑ 1.1 196,793 1

Bitmap Heap Scan on "case" ca (cost=449,948.11..973,030.39 rows=215,684 width=44) (actual time=3,529.049..5,144.735 rows=196,793 loops=1)

  • Recheck Cond: ((createddate >= '2019-09-01 17:21:09'::timestamp without time zone) AND (createddate < '2019-10-01 17:21:09'::timestamp without time zone))
  • Filter: (ic_call_answered_by_ah__c AND (NOT resident_case__c) AND ((status)::text <> 'Resident'::text) AND ((recordtypeid)::text = '012i0000000DVbcAAG'::text))
  • Rows Removed by Filter: 25,445
  • Heap Blocks: exact=214,846
8. 98.204 3,466.681 ↓ 0.0 0 1

BitmapAnd (cost=449,948.11..449,948.11 rows=305,336 width=0) (actual time=3,466.681..3,466.681 rows=0 loops=1)

9. 304.373 304.373 ↓ 1.1 834,193 1

Bitmap Index Scan on hc_idx_case_createddate (cost=0.00..26,069.31 rows=738,597 width=0) (actual time=304.373..304.373 rows=834,193 loops=1)

  • Index Cond: ((createddate >= '2019-09-01 17:21:09'::timestamp without time zone) AND (createddate < '2019-10-01 17:21:09'::timestamp without time zone))
10. 3,064.104 3,064.104 ↓ 1.0 7,181,831 1

Bitmap Index Scan on hc_idx_case_ic_call_answered_by_ah__c (cost=0.00..423,857.18 rows=7,035,380 width=0) (actual time=3,064.104..3,064.104 rows=7,181,831 loops=1)

  • Index Cond: (ic_call_answered_by_ah__c = true)
11. 0.228 4.297 ↑ 8.9 834 1

Hash (cost=2,096.54..2,096.54 rows=7,418 width=34) (actual time=4.296..4.297 rows=834 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 118kB
12. 4.069 4.069 ↑ 8.9 834 1

Index Scan using hc_idx_contact_recordtypeid on contact co (cost=0.11..2,096.54 rows=7,418 width=34) (actual time=0.036..4.069 rows=834 loops=1)

  • Index Cond: ((recordtypeid)::text = '0120H000001O7IWQA0'::text)
13.          

CTE qaquery

14. 8.565 1,063.095 ↓ 195.0 195 1

GroupAggregate (cost=1,508.45..1,508.49 rows=1 width=370) (actual time=1,054.316..1,063.095 rows=195 loops=1)

  • Group Key: co_1.sfid, co_1.firstname, co_1.lastname
15. 6.974 1,054.530 ↓ 3,331.0 3,331 1

Sort (cost=1,508.45..1,508.46 rows=1 width=136) (actual time=1,054.241..1,054.530 rows=3,331 loops=1)

  • Sort Key: co_1.sfid, co_1.firstname, co_1.lastname
  • Sort Method: quicksort Memory: 914kB
16. 21.559 1,047.556 ↓ 3,331.0 3,331 1

Nested Loop Left Join (cost=0.42..1,508.45 rows=1 width=136) (actual time=0.166..1,047.556 rows=3,331 loops=1)

  • Filter: CASE WHEN (qa.service_request_pop__c IS NULL) THEN ((ca_1.createddate >= '2019-09-01 17:21:09'::timestamp without time zone) AND (ca_1.createddate < '2019-10-01 17:21:09'::timestamp without time zone)) ELSE ((sr.createddate >= (...)
  • Rows Removed by Filter: 17,326
17. 10.546 695.485 ↓ 20,657.0 20,657 1

Nested Loop Left Join (cost=0.31..1,504.34 rows=1 width=201) (actual time=0.127..695.485 rows=20,657 loops=1)

18. 5.605 499.026 ↓ 20,657.0 20,657 1

Nested Loop (cost=0.20..1,500.28 rows=1 width=193) (actual time=0.123..499.026 rows=20,657 loops=1)

19. 0.310 9.788 ↓ 327.0 327 1

Nested Loop (cost=0.11..1,484.21 rows=1 width=53) (actual time=0.073..9.788 rows=327 loops=1)

20. 1.278 1.278 ↑ 1.0 328 1

Seq Scan on internal_app_permissions__c ip_1 (cost=0.00..133.83 rows=328 width=19) (actual time=0.020..1.278 rows=328 loops=1)

  • Filter: ((employment_status__c)::text = 'Active'::text)
  • Rows Removed by Filter: 480
21. 8.200 8.200 ↑ 1.0 1 328

Index Scan using contact_idx_sfid on contact co_1 (cost=0.11..4.12 rows=1 width=34) (actual time=0.025..0.025 rows=1 loops=328)

  • Index Cond: ((sfid)::text = (ip_1.contact__c)::text)
  • Filter: ((recordtypeid)::text = '0120H000001O7IWQA0'::text)
  • Rows Removed by Filter: 0
22. 483.633 483.633 ↓ 10.5 63 327

Index Scan using hc_idx_qa_form__c_property_consultant__c on qa_form__c qa (cost=0.08..16.06 rows=6 width=178) (actual time=0.027..1.479 rows=63 loops=327)

  • Index Cond: (((property_consultant__c)::text = (co_1.sfid)::text) AND (property_consultant__c IS NOT NULL))
  • Filter: (((shop_category__c)::text = ANY ('{Leasing,Maintenance}'::text[])) AND ((shop_status__c)::text = 'Completed'::text))
  • Rows Removed by Filter: 898
23. 185.913 185.913 ↓ 0.0 0 20,657

Index Scan using service_request1__c_idx_sfid on service_request1__c sr (cost=0.11..4.05 rows=1 width=46) (actual time=0.009..0.009 rows=0 loops=20,657)

  • Index Cond: ((qa.service_request_pop__c)::text = (sfid)::text)
  • Filter: ((qa.case_account__c)::text = (account_name__c)::text)
24. 330.512 330.512 ↑ 1.0 1 20,657

Index Scan using hcu_idx_case_sfid on "case" ca_1 (cost=0.11..4.11 rows=1 width=46) (actual time=0.016..0.016 rows=1 loops=20,657)

  • Index Cond: ((qa.case__c)::text = (sfid)::text)
  • Filter: ((qa.case_account__c)::text = (accountid)::text)
25. 1.142 6,844.499 ↓ 194.0 194 1

GroupAggregate (cost=3.07..3.12 rows=1 width=1,248) (actual time=6,843.366..6,844.499 rows=194 loops=1)

  • Group Key: qaquery.agent, (COALESCE(ip.team__c, ''::character varying))
26. 0.329 6,843.357 ↓ 194.0 194 1

Sort (cost=3.07..3.07 rows=1 width=384) (actual time=6,843.343..6,843.357 rows=194 loops=1)

  • Sort Key: qaquery.agent, (COALESCE(ip.team__c, ''::character varying))
  • Sort Method: quicksort Memory: 125kB
27. 0.177 6,843.028 ↓ 194.0 194 1

Nested Loop (cost=0.06..3.07 rows=1 width=384) (actual time=6,841.430..6,843.028 rows=194 loops=1)

28. 0.156 6,841.881 ↓ 194.0 194 1

Hash Join (cost=0.01..0.17 rows=1 width=406) (actual time=6,841.395..6,841.881 rows=194 loops=1)

  • Hash Cond: (casequery.agent = qaquery.agent)
29. 5,778.164 5,778.164 ↓ 11.1 266 1

CTE Scan on casequery (cost=0.00..0.14 rows=24 width=110) (actual time=5,777.809..5,778.164 rows=266 loops=1)

30. 0.148 1,063.561 ↓ 195.0 195 1

Hash (cost=0.01..0.01 rows=1 width=328) (actual time=1,063.561..1,063.561 rows=195 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 69kB
31. 1,063.413 1,063.413 ↓ 195.0 195 1

CTE Scan on qaquery (cost=0.00..0.01 rows=1 width=328) (actual time=1,054.321..1,063.413 rows=195 loops=1)

  • Filter: (agent IS NOT NULL)
32. 0.970 0.970 ↑ 1.0 1 194

Index Scan using hc_idx_internal_app_permissions__c_contact__c on internal_app_permissions__c ip (cost=0.06..2.89 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=194)

  • Index Cond: ((contact__c)::text = (casequery.sfid)::text)
  • Filter: ((employment_status__c)::text = 'Active'::text)
Planning time : 8.310 ms
Execution time : 6,849.086 ms