explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lEyb

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=8,187.69..8,187.78 rows=1 width=360) (actual rows= loops=)

2.          

CTE casequery

3. 0.000 0.000 ↓ 0.0

WindowAgg (cost=5,228.12..5,228.17 rows=1 width=127) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=5,228.12..5,228.16 rows=1 width=55) (actual rows= loops=)

  • Group Key: co.sfid, ip.team__c, co.firstname, co.lastname
5. 0.000 0.000 ↓ 0.0

Sort (cost=5,228.12..5,228.13 rows=1 width=72) (actual rows= loops=)

  • Sort Key: co.sfid, ip.team__c, co.firstname, co.lastname
6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3,013.00..5,228.11 rows=1 width=72) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Hash Join (cost=72.77..2,255.73 rows=1 width=66) (actual rows= loops=)

  • Hash Cond: ((co.sfid)::text = (ip.contact__c)::text)
8. 0.000 0.000 ↓ 0.0

Index Scan using hc_idx_contact_recordtypeid on contact co (cost=0.56..2,131.13 rows=10,476 width=34) (actual rows= loops=)

  • Index Cond: ((recordtypeid)::text = '0120H000001O7IWQA0'::text)
9. 0.000 0.000 ↓ 0.0

Hash (cost=68.00..68.00 rows=337 width=32) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Seq Scan on internal_app_permissions__c ip (cost=0.00..68.00 rows=337 width=32) (actual rows= loops=)

  • Filter: ((employment_status__c)::text = 'Active'::text)
11. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on "case" ca (cost=2,940.23..2,972.37 rows=1 width=44) (actual rows= loops=)

  • Recheck Cond: (((property_consultant__c)::text = (co.sfid)::text) AND (property_consultant__c IS NOT NULL) AND (createddate >= '2019-08-27 11:13:55'::timestamp without time zone) AND (createddate < '2019-09-26 11:13:55'::t (...)
  • Filter: (ic_call_answered_by_ah__c AND (NOT COALESCE(resident_case__c, false)) AND ((status)::text <> 'Resident'::text) AND ((recordtypeid)::text = '012i0000000DVbcAAG'::text))
12. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=2,940.23..2,940.23 rows=8 width=0) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on hc_idx_case_property_consultant__c (cost=0.00..20.48 rows=915 width=0) (actual rows= loops=)

  • Index Cond: (((property_consultant__c)::text = (co.sfid)::text) AND (property_consultant__c IS NOT NULL))
14. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on hc_idx_case_createddate (cost=0.00..2,918.45 rows=139,001 width=0) (actual rows= loops=)

  • Index Cond: ((createddate >= '2019-08-27 11:13:55'::timestamp without time zone) AND (createddate < '2019-09-26 11:13:55'::timestamp without time zone))
15.          

CTE qaquery

16. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=2,959.38..2,959.46 rows=1 width=175) (actual rows= loops=)

  • Group Key: co_1.sfid, ip_1.team__c, co_1.firstname, co_1.lastname
17. 0.000 0.000 ↓ 0.0

Sort (cost=2,959.38..2,959.38 rows=1 width=92) (actual rows= loops=)

  • Sort Key: co_1.sfid, ip_1.team__c, co_1.firstname, co_1.lastname
18. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=592.05..2,959.37 rows=1 width=92) (actual rows= loops=)

  • Filter: CASE WHEN (qa.service_request_pop__c IS NULL) THEN ((ca_1.createddate >= '2019-08-27 11:13:55'::timestamp without time zone) AND (ca_1.createddate < '2019-09-26 11:13:55'::timestamp without time zone)) ELSE ((sr.createddate >= (...)
19. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=591.49..2,950.77 rows=1 width=157) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=591.06..2,942.31 rows=1 width=149) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Hash Join (cost=72.77..2,255.73 rows=1 width=66) (actual rows= loops=)

  • Hash Cond: ((co_1.sfid)::text = (ip_1.contact__c)::text)
22. 0.000 0.000 ↓ 0.0

Index Scan using hc_idx_contact_recordtypeid on contact co_1 (cost=0.56..2,131.13 rows=10,476 width=34) (actual rows= loops=)

  • Index Cond: ((recordtypeid)::text = '0120H000001O7IWQA0'::text)
23. 0.000 0.000 ↓ 0.0

Hash (cost=68.00..68.00 rows=337 width=32) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Seq Scan on internal_app_permissions__c ip_1 (cost=0.00..68.00 rows=337 width=32) (actual rows= loops=)

  • Filter: ((employment_status__c)::text = 'Active'::text)
25. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on qa_form__c qa (cost=518.28..686.57 rows=1 width=121) (actual rows= loops=)

  • Recheck Cond: (((property_consultant__c)::text = (co_1.sfid)::text) AND (property_consultant__c IS NOT NULL) AND ((shop_category__c)::text = ANY ('{Leasing,Maintenance}'::text[])))
  • Filter: ((createddate >= '2019-08-27 11:13:55'::timestamp without time zone) AND (createddate < '2019-09-26 11:13:55'::timestamp without time zone) AND ((shop_status__c)::text = 'Completed'::text))
26. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=518.28..518.28 rows=47 width=0) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on hc_idx_qa_form__c_property_consultant__c (cost=0.00..22.57 rows=775 width=0) (actual rows= loops=)

  • Index Cond: (((property_consultant__c)::text = (co_1.sfid)::text) AND (property_consultant__c IS NOT NULL))
28. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on hc_idx_qa_form__c_shop_category__c (cost=0.00..495.46 rows=26,483 width=0) (actual rows= loops=)

  • Index Cond: ((shop_category__c)::text = ANY ('{Leasing,Maintenance}'::text[]))
29. 0.000 0.000 ↓ 0.0

Index Scan using service_request1__c_idx_sfid on service_request1__c sr (cost=0.43..8.45 rows=1 width=45) (actual rows= loops=)

  • Index Cond: ((qa.service_request_pop__c)::text = (sfid)::text)
  • Filter: ((qa.case_account__c)::text = (account_name__c)::text)
30. 0.000 0.000 ↓ 0.0

Index Scan using hcu_idx_case_sfid on "case" ca_1 (cost=0.56..8.58 rows=1 width=46) (actual rows= loops=)

  • Index Cond: ((qa.case__c)::text = (sfid)::text)
  • Filter: ((qa.case_account__c)::text = (accountid)::text)
31. 0.000 0.000 ↓ 0.0

WindowAgg (cost=0.06..0.15 rows=1 width=360) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=0.06..0.13 rows=1 width=352) (actual rows= loops=)

  • Group Key: qaquery.agent, casequery.team
33. 0.000 0.000 ↓ 0.0

Sort (cost=0.06..0.07 rows=1 width=160) (actual rows= loops=)

  • Sort Key: qaquery.agent, casequery.team
34. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.00..0.05 rows=1 width=160) (actual rows= loops=)

  • Join Filter: (casequery.agent = qaquery.agent)
35. 0.000 0.000 ↓ 0.0

CTE Scan on qaquery (cost=0.00..0.02 rows=1 width=120) (actual rows= loops=)

  • Filter: (agent IS NOT NULL)
36. 0.000 0.000 ↓ 0.0

CTE Scan on casequery (cost=0.00..0.02 rows=1 width=72) (actual rows= loops=)