explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yHYR

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 455.787 ↓ 0.0 0 1

Limit (cost=2,983.77..2,983.86 rows=1 width=360) (actual time=455.787..455.787 rows=0 loops=1)

2.          

CTE casequery

3. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=24.21..24.26 rows=1 width=127) (never executed)

4. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=24.21..24.24 rows=1 width=55) (never executed)

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

Sort (cost=24.21..24.21 rows=1 width=72) (never executed)

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

Nested Loop (cost=1.27..24.20 rows=1 width=72) (never executed)

7. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.00..17.04 rows=1 width=78) (never executed)

8. 0.000 0.000 ↓ 0.0 0

Index Scan using hc_idx_case_createddate on "case" ca (cost=0.43..8.46 rows=1 width=44) (never executed)

  • Index Cond: ((createddate >= '2019-08-26 17:43:57'::timestamp without time zone) AND (createddate < '2019-08-25 17:43:57'::timestamp without time zone))
  • Filter: (ic_call_answered_by_ah__c AND (NOT COALESCE(resident_case__c, false)) AND (property_consultant__c IS NOT NULL) AND ((status)::text <> 'Resident'::text) AND ((recordtypeid)::text = '012i0000000DVbcAAG'::text) (...)
9. 0.000 0.000 ↓ 0.0 0

Index Scan using contact_idx_sfid on contact co (cost=0.56..8.58 rows=1 width=34) (never executed)

  • Index Cond: ((sfid)::text = (ca.property_consultant__c)::text)
  • Filter: ((recordtypeid)::text = '0120H000001O7IWQA0'::text)
10. 0.000 0.000 ↓ 0.0 0

Index Scan using hc_idx_internal_app_permissions__c_contact__c on internal_app_permissions__c ip (cost=0.28..7.15 rows=1 width=32) (never executed)

  • Index Cond: ((contact__c)::text = (co.sfid)::text)
  • Filter: ((employment_status__c)::text = 'Active'::text)
11.          

CTE qaquery

12. 0.000 455.771 ↓ 0.0 0 1

GroupAggregate (cost=2,959.37..2,959.45 rows=1 width=175) (actual time=455.771..455.771 rows=0 loops=1)

  • Group Key: co_1.sfid, ip_1.team__c, co_1.firstname, co_1.lastname
13. 0.006 455.771 ↓ 0.0 0 1

Sort (cost=2,959.37..2,959.38 rows=1 width=92) (actual time=455.771..455.771 rows=0 loops=1)

  • Sort Key: co_1.sfid, ip_1.team__c, co_1.firstname, co_1.lastname
  • Sort Method: quicksort Memory: 25kB
14. 0.001 455.765 ↓ 0.0 0 1

Nested Loop Left Join (cost=592.04..2,959.36 rows=1 width=92) (actual time=455.765..455.765 rows=0 loops=1)

  • Filter: CASE WHEN (qa.service_request_pop__c IS NULL) THEN ((ca_1.createddate >= '2019-08-26 17:43:57'::timestamp without time zone) AND (ca_1.createddate < '2019-08-25 17:43:57'::timestamp without time zone)) ELSE ((sr.createddate >= (...)
15. 0.000 455.764 ↓ 0.0 0 1

Nested Loop Left Join (cost=591.48..2,950.76 rows=1 width=157) (actual time=455.764..455.764 rows=0 loops=1)

16. 0.244 455.764 ↓ 0.0 0 1

Nested Loop (cost=591.05..2,942.30 rows=1 width=149) (actual time=455.764..455.764 rows=0 loops=1)

17. 0.673 3.420 ↓ 330.0 330 1

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

  • Hash Cond: ((co_1.sfid)::text = (ip_1.contact__c)::text)
18. 2.043 2.043 ↑ 12.9 812 1

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

  • Index Cond: ((recordtypeid)::text = '0120H000001O7IWQA0'::text)
19. 0.181 0.704 ↑ 1.0 331 1

Hash (cost=68.00..68.00 rows=337 width=32) (actual time=0.704..0.704 rows=331 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
20. 0.523 0.523 ↑ 1.0 331 1

Seq Scan on internal_app_permissions__c ip_1 (cost=0.00..68.00 rows=337 width=32) (actual time=0.010..0.523 rows=331 loops=1)

  • Filter: ((employment_status__c)::text = 'Active'::text)
  • Rows Removed by Filter: 455
21. 26.070 452.100 ↓ 0.0 0 330

Bitmap Heap Scan on qa_form__c qa (cost=518.28..686.56 rows=1 width=121) (actual time=1.370..1.370 rows=0 loops=330)

  • 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-26 17:43:57'::timestamp without time zone) AND (createddate < '2019-08-25 17:43:57'::timestamp without time zone) AND ((shop_status__c)::text = 'Completed'::text))
  • Rows Removed by Filter: 56
  • Heap Blocks: exact=16,050
22. 5.350 426.030 ↓ 0.0 0 330

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

23. 22.440 22.440 ↓ 1.3 1,037 330

Bitmap Index Scan on hc_idx_qa_form__c_property_consultant__c (cost=0.00..22.57 rows=775 width=0) (actual time=0.068..0.068 rows=1,037 loops=330)

  • Index Cond: (((property_consultant__c)::text = (co_1.sfid)::text) AND (property_consultant__c IS NOT NULL))
24. 398.240 398.240 ↓ 1.0 26,620 262

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

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

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

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

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

  • Index Cond: ((qa.case__c)::text = (sfid)::text)
  • Filter: ((qa.case_account__c)::text = (accountid)::text)
27. 0.002 455.786 ↓ 0.0 0 1

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

28. 0.002 455.784 ↓ 0.0 0 1

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

  • Group Key: qaquery.agent, casequery.team
29. 0.008 455.782 ↓ 0.0 0 1

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

  • Sort Key: qaquery.agent, casequery.team
  • Sort Method: quicksort Memory: 25kB
30. 0.001 455.774 ↓ 0.0 0 1

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

  • Join Filter: (casequery.agent = qaquery.agent)
31. 455.773 455.773 ↓ 0.0 0 1

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

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

CTE Scan on casequery (cost=0.00..0.02 rows=1 width=72) (never executed)

Planning time : 11.893 ms
Execution time : 456.530 ms