explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zBvc

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 14.144 ↓ 13.0 13 1

Limit (cost=6,569.28..6,569.29 rows=1 width=372) (actual time=14.143..14.144 rows=13 loops=1)

2.          

CTE qaquery

3. 0.222 4.576 ↓ 9.0 9 1

GroupAggregate (cost=3,399.29..3,399.32 rows=1 width=67) (actual time=4.363..4.576 rows=9 loops=1)

  • Group Key: (COALESCE(co.firstname, ''::character varying)), qa_1.qa_reviewer__c
4. 0.343 4.354 ↓ 1,144.0 1,144 1

Sort (cost=3,399.29..3,399.30 rows=1 width=75) (actual time=4.315..4.354 rows=1,144 loops=1)

  • Sort Key: (COALESCE(co.firstname, ''::character varying)), qa_1.qa_reviewer__c
  • Sort Method: quicksort Memory: 138kB
5. 0.217 4.011 ↓ 1,144.0 1,144 1

Hash Join (cost=1,756.06..3,399.28 rows=1 width=75) (actual time=2.657..4.011 rows=1,144 loops=1)

  • Hash Cond: ((qa_1.qa_reviewer__c)::text = (co.sfid)::text)
6. 1.200 3.045 ↓ 2.2 1,144 1

Bitmap Heap Scan on qa_form__c qa_1 (cost=652.61..2,294.48 rows=514 width=43) (actual time=1.901..3.045 rows=1,144 loops=1)

  • Recheck Cond: ((completion_time__c >= '2019-04-10 19:17:47'::timestamp without time zone) AND (completion_time__c < '2019-05-10 19:17:47'::timestamp without time zone) AND ((shop_status__c)::text = ANY ('{Completed,Disputed}'::t (...)
  • Filter: (NOT dismiss_from_qa__c)
  • Heap Blocks: exact=625
7. 0.023 1.845 ↓ 0.0 0 1

BitmapAnd (cost=652.61..652.61 rows=519 width=0) (actual time=1.845..1.845 rows=0 loops=1)

8. 0.091 0.091 ↓ 1.0 1,150 1

Bitmap Index Scan on hc_idx_qa_form__c_completion_time__c (cost=0.00..27.65 rows=1,136 width=0) (actual time=0.091..0.091 rows=1,150 loops=1)

  • Index Cond: ((completion_time__c >= '2019-04-10 19:17:47'::timestamp without time zone) AND (completion_time__c < '2019-05-10 19:17:47'::timestamp without time zone))
9. 1.731 1.731 ↓ 1.0 31,114 1

Bitmap Index Scan on hc_idx_qa_form__c_shop_status__c (cost=0.00..624.45 rows=30,882 width=0) (actual time=1.731..1.731 rows=31,114 loops=1)

  • Index Cond: ((shop_status__c)::text = ANY ('{Completed,Disputed}'::text[]))
10. 0.091 0.749 ↑ 7.7 684 1

Hash (cost=1,037.44..1,037.44 rows=5,281 width=26) (actual time=0.749..0.749 rows=684 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 103kB
11. 0.658 0.658 ↑ 7.7 684 1

Index Scan using hc_idx_contact_recordtypeid on contact co (cost=0.56..1,037.44 rows=5,281 width=26) (actual time=0.016..0.658 rows=684 loops=1)

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

CTE caquery

13. 0.185 9.507 ↓ 6.0 6 1

GroupAggregate (cost=3,169.80..3,169.83 rows=1 width=67) (actual time=9.340..9.507 rows=6 loops=1)

  • Group Key: (COALESCE(co_1.firstname, ''::character varying)), ca_1.reviewer__c
14. 0.259 9.322 ↓ 943.0 943 1

Sort (cost=3,169.80..3,169.80 rows=1 width=72) (actual time=9.290..9.322 rows=943 loops=1)

  • Sort Key: (COALESCE(co_1.firstname, ''::character varying)), ca_1.reviewer__c
  • Sort Method: quicksort Memory: 98kB
15. 0.352 9.063 ↓ 943.0 943 1

Hash Join (cost=1,103.45..3,169.79 rows=1 width=72) (actual time=1.003..9.063 rows=943 loops=1)

  • Hash Cond: ((ca_1.reviewer__c)::text = (co_1.sfid)::text)
16. 7.918 7.918 ↓ 1.0 943 1

Seq Scan on call_analysis__c ca_1 (cost=0.00..2,063.88 rows=935 width=40) (actual time=0.204..7.918 rows=943 loops=1)

  • Filter: (((status__c)::text = ANY ('{Completed,Disputed}'::text[])) AND ((call_result__c)::text <> 'Abandon'::text) AND (call_completed_time__c >= '2019-04-10 19:17:47'::timestamp without time zone) AND (call_completed_time__c < (...)
  • Rows Removed by Filter: 22101
17. 0.100 0.793 ↑ 7.7 684 1

Hash (cost=1,037.44..1,037.44 rows=5,281 width=26) (actual time=0.793..0.793 rows=684 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 103kB
18. 0.693 0.693 ↑ 7.7 684 1

Index Scan using hc_idx_contact_recordtypeid on contact co_1 (cost=0.56..1,037.44 rows=5,281 width=26) (actual time=0.015..0.693 rows=684 loops=1)

  • Index Cond: ((recordtypeid)::text = '0120H000001O7IWQA0'::text)
19. 0.010 14.142 ↓ 13.0 13 1

Sort (cost=0.13..0.13 rows=1 width=372) (actual time=14.142..14.142 rows=13 loops=1)

  • Sort Key: (COALESCE(qa.qaname, ca.qaname))
  • Sort Method: quicksort Memory: 27kB
20. 0.018 14.132 ↓ 13.0 13 1

WindowAgg (cost=0.09..0.12 rows=1 width=372) (actual time=14.128..14.132 rows=13 loops=1)

21. 0.012 14.114 ↓ 13.0 13 1

HashAggregate (cost=0.09..0.10 rows=1 width=236) (actual time=14.112..14.114 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.disputedcallanalysiscoun (...)
22. 0.009 14.102 ↓ 13.0 13 1

Hash Full Join (cost=0.03..0.07 rows=1 width=236) (actual time=13.881..14.102 rows=13 loops=1)

  • Hash Cond: ((qa.qareviewer)::text = (ca.careviewer)::text)
23. 4.579 4.579 ↓ 9.0 9 1

CTE Scan on qaquery qa (cost=0.00..0.02 rows=1 width=102) (actual time=4.364..4.579 rows=9 loops=1)

24. 0.003 9.514 ↓ 6.0 6 1

Hash (cost=0.02..0.02 rows=1 width=102) (actual time=9.514..9.514 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 9.511 9.511 ↓ 6.0 6 1

CTE Scan on caquery ca (cost=0.00..0.02 rows=1 width=102) (actual time=9.343..9.511 rows=6 loops=1)

Planning time : 0.802 ms
Execution time : 14.266 ms