explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Bihk

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 97.377 ↓ 6.5 13 1

Sort (cost=6,188.87..6,188.87 rows=2 width=372) (actual time=97.377..97.377 rows=13 loops=1)

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

CTE qaquery

3. 0.665 62.020 ↓ 13.0 13 1

GroupAggregate (cost=2,544.34..2,544.35 rows=1 width=67) (actual time=61.358..62.020 rows=13 loops=1)

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

Sort (cost=2,544.34..2,544.34 rows=1 width=75) (actual time=61.219..61.355 rows=2,655 loops=1)

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

Nested Loop (cost=1,587.32..2,544.34 rows=1 width=75) (actual time=15.579..59.957 rows=2,655 loops=1)

6. 15.634 30.971 ↓ 26.6 2,655 1

Bitmap Heap Scan on qa_form__c qa_1 (cost=1,587.21..2,132.64 rows=100 width=43) (actual time=15.517..30.971 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) AND ((qa_reviewer__c)::text <> ''::text))
  • Rows Removed by Filter: 15
  • Heap Blocks: exact=1441
7. 0.143 15.337 ↓ 0.0 0 1

BitmapAnd (cost=1,587.21..1,587.21 rows=299 width=0) (actual time=15.337..15.337 rows=0 loops=1)

8. 0.503 0.503 ↓ 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.503..0.503 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. 6.853 6.853 ↑ 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=6.853..6.853 rows=33,790 loops=1)

  • Index Cond: ((shop_status__c)::text = ANY ('{Completed,Disputed}'::text[]))
10. 7.838 7.838 ↑ 1.1 35,457 1

Bitmap Index Scan on hc_idx_qa_form__c_qa_reviewer__c (cost=0.00..832.07 rows=38,657 width=0) (actual time=7.838..7.838 rows=35,457 loops=1)

  • Index Cond: (qa_reviewer__c IS NOT NULL)
11. 26.550 26.550 ↑ 1.0 1 2,655

Index Scan using contact_idx_sfid on contact co (cost=0.11..4.12 rows=1 width=26) (actual time=0.010..0.010 rows=1 loops=2,655)

  • Index Cond: ((sfid)::text = (qa_1.qa_reviewer__c)::text)
  • Filter: ((recordtypeid)::text = '0120H000001O7IWQA0'::text)
12.          

CTE caquery

13. 0.745 35.237 ↓ 5.0 10 1

GroupAggregate (cost=3,644.46..3,644.47 rows=2 width=67) (actual time=34.374..35.237 rows=10 loops=1)

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

Sort (cost=3,644.46..3,644.46 rows=2 width=72) (actual time=34.333..34.492 rows=2,981 loops=1)

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

Hash Join (cost=1,580.47..3,644.46 rows=2 width=72) (actual time=5.992..33.197 rows=2,981 loops=1)

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

Seq Scan on call_analysis__c ca_1 (cost=0.00..2,062.51 rows=2,821 width=40) (actual time=0.517..26.916 rows=2,981 loops=1)

  • Filter: ((reviewer__c IS NOT NULL) AND ((status__c)::text = ANY ('{Completed,Disputed}'::text[])) AND ((call_result__c)::text <> 'Abandon'::text) AND ((reviewer__c)::text <> ''::text) AND (call_completed_time__c >= '2019-04-15 1 (...)
  • Rows Removed by Filter: 23003
17. 0.186 5.454 ↑ 8.1 702 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 104kB
18. 5.268 5.268 ↑ 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.039..5.268 rows=702 loops=1)

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

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

20. 0.006 97.330 ↓ 6.5 13 1

Group (cost=0.03..0.04 rows=2 width=236) (actual time=97.325..97.330 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.027 97.324 ↓ 6.5 13 1

Sort (cost=0.03..0.03 rows=2 width=236) (actual time=97.323..97.324 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.017 97.297 ↓ 6.5 13 1

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

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

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

24. 0.007 62.036 ↓ 13.0 13 1

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

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

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