explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KlYZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 3,679.409 ↑ 1.0 1 1

Result (cost=719,858.93..719,858.94 rows=1 width=32) (actual time=3,679.409..3,679.409 rows=1 loops=1)

2.          

Initplan (for Result)

3. 6.636 3,679.406 ↑ 1.0 1 1

Aggregate (cost=719,858.92..719,858.93 rows=1 width=32) (actual time=3,679.406..3,679.406 rows=1 loops=1)

4. 17.813 3,672.770 ↑ 1.9 80,626 1

Hash Join (cost=424,460.38..719,467.68 rows=156,497 width=0) (actual time=1,361.224..3,672.770 rows=80,626 loops=1)

  • Hash Cond: (t0.agent_id = t1.id)
5. 113.385 3,654.782 ↑ 1.9 80,626 1

Nested Loop (cost=424,420.92..719,015.17 rows=156,497 width=16) (actual time=1,361.044..3,654.782 rows=80,626 loops=1)

  • Join Filter: (CASE WHEN ((t6.question)::text = 'Compliance'::text) THEN t6.result ELSE NULL::double precision END < t9.risk_threshold)
  • Rows Removed by Join Filter: 1,269,585
6. 0.008 0.008 ↑ 1.0 1 1

Index Scan using rule_category_pkey on rule_category t9 (cost=0.15..8.17 rows=1 width=8) (actual time=0.005..0.008 rows=1 loops=1)

  • Index Cond: (id = '2dd5c3bb-527c-4c51-b623-2a2cc1d29527'::uuid)
7. 343.904 3,541.389 ↓ 2.9 1,350,211 1

Hash Right Join (cost=424,420.77..711,964.64 rows=469,491 width=49) (actual time=1,360.851..3,541.389 rows=1,350,211 loops=1)

  • Hash Cond: (t2.call_id = t0.id)
8. 342.182 3,033.119 ↓ 2.9 1,349,299 1

Hash Right Join (cost=393,595.66..679,907.10 rows=469,491 width=49) (actual time=1,196.071..3,033.119 rows=1,349,299 loops=1)

  • Hash Cond: (t3.transcript_id = t2.id)
9. 338.031 2,561.298 ↓ 2.9 1,349,294 1

Hash Right Join (cost=371,979.17..657,058.53 rows=469,360 width=49) (actual time=1,066.010..2,561.298 rows=1,349,294 loops=1)

  • Hash Cond: (t4.enriched_transcript_id = t3.id)
10. 348.974 2,100.803 ↓ 2.9 1,349,277 1

Hash Right Join (cost=356,745.51..640,592.77 rows=469,367 width=49) (actual time=943.134..2,100.803 rows=1,349,277 loops=1)

  • Hash Cond: (t5.scorecard_run_id = t4.id)
11. 386.459 1,617.953 ↓ 2.9 1,349,277 1

Hash Right Join (cost=340,195.70..622,810.86 rows=469,367 width=49) (actual time=808.849..1,617.953 rows=1,349,277 loops=1)

  • Hash Cond: (t6.parent = t5.id)
12. 490.286 723.957 ↓ 1.0 1,349,277 1

Bitmap Heap Scan on scorecard_result t6 (cost=53,121.31..332,335.39 rows=1,295,650 width=49) (actual time=299.585..723.957 rows=1,349,277 loops=1)

  • Recheck Cond: (((question)::text = 'Communication'::text) OR ((question)::text = 'Compliance'::text) OR ((question)::text = 'Conduct'::text))
  • Heap Blocks: exact=255,703
13. 0.004 233.671 ↓ 0.0 0 1

BitmapOr (cost=53,121.31..53,121.31 rows=1,336,519 width=0) (actual time=233.671..233.671 rows=0 loops=1)

14. 107.429 107.429 ↑ 1.0 449,759 1

Bitmap Index Scan on scorecard_result_parent_question_idx (cost=0.00..17,807.53 rows=456,396 width=0) (actual time=107.429..107.429 rows=449,759 loops=1)

  • Index Cond: ((question)::text = 'Communication'::text)
15. 66.655 66.655 ↑ 1.0 449,759 1

Bitmap Index Scan on scorecard_result_parent_question_idx (cost=0.00..17,713.52 rows=453,994 width=0) (actual time=66.655..66.655 rows=449,759 loops=1)

  • Index Cond: ((question)::text = 'Compliance'::text)
16. 59.583 59.583 ↓ 1.1 449,759 1

Bitmap Index Scan on scorecard_result_parent_question_idx (cost=0.00..16,628.53 rows=426,129 width=0) (actual time=59.583..59.583 rows=449,759 loops=1)

  • Index Cond: ((question)::text = 'Conduct'::text)
17. 100.986 507.537 ↑ 1.0 449,759 1

Hash (cost=281,207.30..281,207.30 rows=469,367 width=32) (actual time=507.536..507.537 rows=449,759 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 32,206kB
18. 342.860 406.551 ↑ 1.0 449,759 1

Bitmap Heap Scan on scorecard_result t5 (cost=15,714.15..281,207.30 rows=469,367 width=32) (actual time=113.813..406.551 rows=449,759 loops=1)

  • Recheck Cond: (parent IS NULL)
  • Heap Blocks: exact=243,080
19. 63.691 63.691 ↑ 1.0 449,854 1

Bitmap Index Scan on scorecard_result_unique_position (cost=0.00..15,596.81 rows=469,367 width=0) (actual time=63.691..63.691 rows=449,854 loops=1)

  • Index Cond: (parent IS NULL)
20. 82.657 133.876 ↓ 1.0 449,759 1

Hash (cost=10,928.25..10,928.25 rows=449,725 width=32) (actual time=133.876..133.876 rows=449,759 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 32,206kB
21. 51.219 51.219 ↓ 1.0 449,759 1

Seq Scan on scorecard_run t4 (cost=0.00..10,928.25 rows=449,725 width=32) (actual time=0.007..51.219 rows=449,759 loops=1)

22. 70.778 122.464 ↓ 1.0 449,776 1

Hash (cost=9,612.18..9,612.18 rows=449,718 width=32) (actual time=122.464..122.464 rows=449,776 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 32,207kB
23. 51.686 51.686 ↓ 1.0 449,776 1

Seq Scan on enriched_transcript t3 (cost=0.00..9,612.18 rows=449,718 width=32) (actual time=0.008..51.686 rows=449,776 loops=1)

24. 72.737 129.639 ↑ 1.0 449,781 1

Hash (cost=15,993.44..15,993.44 rows=449,844 width=32) (actual time=129.639..129.639 rows=449,781 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 32,208kB
25. 56.902 56.902 ↑ 1.0 449,781 1

Seq Scan on transcript t2 (cost=0.00..15,993.44 rows=449,844 width=32) (actual time=0.008..56.902 rows=449,781 loops=1)

26. 74.794 164.366 ↓ 1.0 450,693 1

Hash (cost=25,446.42..25,446.42 rows=430,295 width=32) (actual time=164.366..164.366 rows=450,693 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 32,265kB
27. 89.572 89.572 ↓ 1.0 450,693 1

Seq Scan on call t0 (cost=0.00..25,446.42 rows=430,295 width=32) (actual time=0.006..89.572 rows=450,693 loops=1)

  • Filter: ((agent_id IS NOT NULL) AND (began < '2020-07-02 14:00:00+00'::timestamp with time zone) AND (began >= '2019-07-01 14:00:00+00'::timestamp with time zone))
28. 0.086 0.175 ↓ 1.0 787 1

Hash (cost=29.76..29.76 rows=776 width=16) (actual time=0.175..0.175 rows=787 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 45kB
29. 0.089 0.089 ↓ 1.0 787 1

Seq Scan on agent t1 (cost=0.00..29.76 rows=776 width=16) (actual time=0.004..0.089 rows=787 loops=1)

Planning time : 1.325 ms
Execution time : 3,680.107 ms