explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bwWd

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 3,645.739 ↑ 1.0 1 1

Result (cost=708,094.93..708,094.94 rows=1 width=32) (actual time=3,645.739..3,645.739 rows=1 loops=1)

2.          

Initplan (for Result)

3. 6.450 3,645.735 ↑ 1.0 1 1

Aggregate (cost=708,094.92..708,094.93 rows=1 width=32) (actual time=3,645.735..3,645.735 rows=1 loops=1)

4. 18.397 3,639.285 ↑ 1.9 80,626 1

Hash Join (cost=412,696.38..707,703.68 rows=156,497 width=0) (actual time=1,280.510..3,639.285 rows=80,626 loops=1)

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

Nested Loop (cost=412,656.92..707,251.17 rows=156,497 width=16) (actual time=1,280.322..3,620.704 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.007 0.007 ↑ 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.004..0.007 rows=1 loops=1)

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

Hash Right Join (cost=412,656.77..700,200.64 rows=469,491 width=49) (actual time=1,280.196..3,508.650 rows=1,350,211 loops=1)

  • Hash Cond: (t2.call_id = t0.id)
8. 352.286 2,977.700 ↓ 2.9 1,349,299 1

Hash Right Join (cost=381,831.66..668,143.10 rows=469,491 width=49) (actual time=1,100.740..2,977.700 rows=1,349,299 loops=1)

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

Hash Right Join (cost=360,215.17..645,294.53 rows=469,360 width=49) (actual time=954.414..2,480.816 rows=1,349,294 loops=1)

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

Hash Right Join (cost=344,981.51..628,828.77 rows=469,367 width=49) (actual time=820.853..2,006.396 rows=1,349,277 loops=1)

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

Hash Right Join (cost=328,431.70..611,046.86 rows=469,367 width=49) (actual time=678.435..1,516.435 rows=1,349,277 loops=1)

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

Bitmap Heap Scan on scorecard_result t6 (cost=41,357.31..320,571.39 rows=1,295,650 width=49) (actual time=179.583..629.250 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.003 129.036 ↓ 0.0 0 1

BitmapOr (cost=41,357.31..41,357.31 rows=1,336,519 width=0) (actual time=129.036..129.036 rows=0 loops=1)

14. 68.483 68.483 ↑ 1.0 449,759 1

Bitmap Index Scan on scorecard_result_question_idx (cost=0.00..13,791.53 rows=456,396 width=0) (actual time=68.483..68.483 rows=449,759 loops=1)

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

Bitmap Index Scan on scorecard_result_question_idx (cost=0.00..13,717.51 rows=453,994 width=0) (actual time=32.328..32.328 rows=449,759 loops=1)

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

Bitmap Index Scan on scorecard_result_question_idx (cost=0.00..12,876.53 rows=426,129 width=0) (actual time=28.222..28.222 rows=449,759 loops=1)

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

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 32,206kB
18. 340.543 404.557 ↑ 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=114.715..404.557 rows=449,759 loops=1)

  • Recheck Cond: (parent IS NULL)
  • Heap Blocks: exact=243,080
19. 64.014 64.014 ↑ 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=64.014..64.014 rows=449,854 loops=1)

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

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 32,206kB
21. 51.981 51.981 ↓ 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.981 rows=449,759 loops=1)

22. 82.112 131.846 ↓ 1.0 449,776 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 32,207kB
23. 49.734 49.734 ↓ 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..49.734 rows=449,776 loops=1)

24. 90.098 144.598 ↑ 1.0 449,781 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 32,208kB
25. 54.500 54.500 ↑ 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..54.500 rows=449,781 loops=1)

26. 91.100 179.050 ↓ 1.0 450,693 1

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

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

Seq Scan on call t0 (cost=0.00..25,446.42 rows=430,295 width=32) (actual time=0.005..87.950 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.087 0.184 ↓ 1.0 787 1

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

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

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

Planning time : 1.313 ms
Execution time : 3,646.430 ms