explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EZo

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 75.068 ↓ 6.5 13 1

Sort (cost=7,429.70..7,429.70 rows=2 width=372) (actual time=75.067..75.068 rows=13 loops=1)

  • Sort Key: (COALESCE(qa.qaname, ca.qaname))
  • Sort Method: quicksort Memory: 28kB
2.          

CTE qaquery

3. 1.010 38.118 ↓ 13.0 13 1

GroupAggregate (cost=3,798.20..3,798.21 rows=1 width=67) (actual time=37.110..38.118 rows=13 loops=1)

  • Group Key: (COALESCE(co.firstname, ''::character varying)), qa_1.qa_reviewer__c
4. 1.826 37.108 ↓ 2,655.0 2,655 1

Sort (cost=3,798.20..3,798.20 rows=1 width=75) (actual time=36.904..37.108 rows=2,655 loops=1)

  • Sort Key: (COALESCE(co.firstname, ''::character varying)), qa_1.qa_reviewer__c
  • Sort Method: quicksort Memory: 305kB
5. 1.215 35.282 ↓ 2,655.0 2,655 1

Hash Join (cost=2,335.63..3,798.20 rows=1 width=75) (actual time=14.998..35.282 rows=2,655 loops=1)

  • Hash Cond: ((qa_1.qa_reviewer__c)::text = (co.sfid)::text)
6. 19.377 31.876 ↓ 3.1 2,655 1

Bitmap Heap Scan on qa_form__c qa_1 (cost=755.16..2,217.27 rows=866 width=43) (actual time=12.790..31.876 rows=2,655 loops=1)

  • Recheck Cond: ((completion_time__c >= '2019-04-15 18:12:29'::timestamp without time zone) AND (completion_time__c < '2019-05-15 18:12:29'::timestamp without time zone) AND ((shop_status__c)::text = ANY ('{Completed,Disputed}'::t (...)
  • Filter: (NOT dismiss_from_qa__c)
  • Rows Removed by Filter: 15
  • Heap Blocks: exact=1441
7. 0.105 12.499 ↓ 0.0 0 1

BitmapAnd (cost=755.16..755.16 rows=878 width=0) (actual time=12.499..12.499 rows=0 loops=1)

8. 0.718 0.718 ↓ 1.1 2,855 1

Bitmap Index Scan on hc_idx_qa_form__c_completion_time__c (cost=0.00..49.48 rows=2,698 width=0) (actual time=0.718..0.718 rows=2,855 loops=1)

  • Index Cond: ((completion_time__c >= '2019-04-15 18:12:29'::timestamp without time zone) AND (completion_time__c < '2019-05-15 18:12:29'::timestamp without time zone))
9. 11.676 11.676 ↑ 1.1 33,790 1

Bitmap Index Scan on hc_idx_qa_form__c_shop_status__c (cost=0.00..705.55 rows=36,921 width=0) (actual time=11.676..11.676 rows=33,790 loops=1)

  • Index Cond: ((shop_status__c)::text = ANY ('{Completed,Disputed}'::text[]))
10. 0.235 2.191 ↑ 8.1 702 1

Hash (cost=1,560.61..1,560.61 rows=5,675 width=26) (actual time=2.191..2.191 rows=702 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 104kB
11. 1.956 1.956 ↑ 8.1 702 1

Index Scan using hc_idx_contact_recordtypeid on contact co (cost=0.11..1,560.61 rows=5,675 width=26) (actual time=0.030..1.956 rows=702 loops=1)

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

CTE caquery

13. 0.986 36.808 ↓ 5.0 10 1

GroupAggregate (cost=3,631.43..3,631.44 rows=2 width=67) (actual time=35.657..36.808 rows=10 loops=1)

  • Group Key: (COALESCE(co_1.firstname, ''::character varying)), ca_1.reviewer__c
14. 1.678 35.822 ↓ 1,490.5 2,981 1

Sort (cost=3,631.43..3,631.43 rows=2 width=72) (actual time=35.605..35.822 rows=2,981 loops=1)

  • Sort Key: (COALESCE(co_1.firstname, ''::character varying)), ca_1.reviewer__c
  • Sort Method: quicksort Memory: 329kB
15. 1.029 34.144 ↓ 1,490.5 2,981 1

Hash Join (cost=1,580.47..3,631.43 rows=2 width=72) (actual time=2.528..34.144 rows=2,981 loops=1)

  • Hash Cond: ((ca_1.reviewer__c)::text = (co_1.sfid)::text)
16. 31.303 31.303 ↓ 1.0 2,981 1

Seq Scan on call_analysis__c ca_1 (cost=0.00..2,049.46 rows=2,845 width=40) (actual time=0.694..31.303 rows=2,981 loops=1)

  • Filter: (((status__c)::text = ANY ('{Completed,Disputed}'::text[])) AND ((call_result__c)::text <> 'Abandon'::text) AND (call_completed_time__c >= '2019-04-15 18:12:29'::timestamp without time zone) AND (call_completed_time__c < (...)
  • Rows Removed by Filter: 23003
17. 0.220 1.812 ↑ 8.1 702 1

Hash (cost=1,560.61..1,560.61 rows=5,675 width=26) (actual time=1.812..1.812 rows=702 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 104kB
18. 1.592 1.592 ↑ 8.1 702 1

Index Scan using hc_idx_contact_recordtypeid on contact co_1 (cost=0.11..1,560.61 rows=5,675 width=26) (actual time=0.028..1.592 rows=702 loops=1)

  • Index Cond: ((recordtypeid)::text = '0120H000001O7IWQA0'::text)
19. 0.029 75.047 ↓ 6.5 13 1

WindowAgg (cost=0.03..0.05 rows=2 width=372) (actual time=75.041..75.047 rows=13 loops=1)

20. 0.007 75.018 ↓ 6.5 13 1

Group (cost=0.03..0.04 rows=2 width=236) (actual time=75.012..75.018 rows=13 loops=1)

  • Group Key: qa.qareviewer, ca.careviewer, qa.qaname, ca.qaname, (COALESCE(qa.completedcount, '0'::bigint)), (COALESCE(qa.disputedcount, '0'::bigint)), (COALESCE(ca.completedcallanalysiscount, '0'::bigint)), (COALESCE(ca.disputedcallanalysiscou (...)
21. 0.034 75.011 ↓ 6.5 13 1

Sort (cost=0.03..0.03 rows=2 width=236) (actual time=75.010..75.011 rows=13 loops=1)

  • Sort Key: qa.qareviewer, ca.careviewer, qa.qaname, ca.qaname, (COALESCE(qa.completedcount, '0'::bigint)), (COALESCE(qa.disputedcount, '0'::bigint)), (COALESCE(ca.completedcallanalysiscount, '0'::bigint)), (COALESCE(ca.disputedcallanalys (...)
  • Sort Method: quicksort Memory: 27kB
22. 0.024 74.977 ↓ 6.5 13 1

Hash Full Join (cost=0.01..0.03 rows=2 width=236) (actual time=73.808..74.977 rows=13 loops=1)

  • Hash Cond: ((ca.careviewer)::text = (qa.qareviewer)::text)
23. 36.816 36.816 ↓ 5.0 10 1

CTE Scan on caquery ca (cost=0.00..0.01 rows=2 width=102) (actual time=35.659..36.816 rows=10 loops=1)

24. 0.008 38.137 ↓ 13.0 13 1

Hash (cost=0.01..0.01 rows=1 width=102) (actual time=38.137..38.137 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 38.129 38.129 ↓ 13.0 13 1

CTE Scan on qaquery qa (cost=0.00..0.01 rows=1 width=102) (actual time=37.113..38.129 rows=13 loops=1)

Planning time : 1.566 ms
Execution time : 75.262 ms