explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AfFn

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 4,297.463 ↑ 1.0 1 1

Result (cost=761,740.23..761,740.24 rows=1 width=32) (actual time=4,297.462..4,297.463 rows=1 loops=1)

2.          

Initplan (for Result)

3. 10.057 4,297.459 ↑ 1.0 1 1

Aggregate (cost=761,740.21..761,740.23 rows=1 width=32) (actual time=4,297.459..4,297.459 rows=1 loops=1)

4. 9.608 4,287.402 ↓ 16.4 80,626 1

Nested Loop Left Join (cost=301,947.82..761,727.89 rows=4,928 width=0) (actual time=787.572..4,287.402 rows=80,626 loops=1)

5. 39.122 3,955.290 ↓ 16.4 80,626 1

Nested Loop (cost=301,947.26..688,678.65 rows=4,928 width=16) (actual time=787.562..3,955.290 rows=80,626 loops=1)

  • Join Filter: (t7.result < t9.risk_threshold)
  • Rows Removed by Join Filter: 369,133
6. 0.005 0.005 ↑ 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.005 rows=1 loops=1)

  • Index Cond: (id = '2dd5c3bb-527c-4c51-b623-2a2cc1d29527'::uuid)
7. 0.000 3,916.163 ↓ 30.4 449,759 1

Gather (cost=301,947.11..688,485.67 rows=14,785 width=24) (actual time=787.538..3,916.163 rows=449,759 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 87.198 4,036.046 ↓ 24.3 149,920 3 / 3

Nested Loop Left Join (cost=300,947.11..686,007.17 rows=6,160 width=24) (actual time=747.167..4,036.046 rows=149,920 loops=3)

9. 50.798 3,199.250 ↓ 24.3 149,920 3 / 3

Hash Join (cost=300,946.55..594,695.62 rows=6,160 width=24) (actual time=747.153..3,199.250 rows=149,920 loops=3)

  • Hash Cond: (t0.agent_id = t1.id)
10. 104.545 3,148.221 ↓ 24.3 149,920 3 / 3

Nested Loop (cost=300,907.09..594,639.90 rows=6,160 width=40) (actual time=746.881..3,148.221 rows=149,920 loops=3)

11. 70.063 2,593.917 ↓ 24.3 149,920 3 / 3

Nested Loop (cost=300,906.67..587,053.67 rows=6,160 width=40) (actual time=746.866..2,593.917 rows=149,920 loops=3)

12. 78.782 2,074.095 ↓ 24.3 149,920 3 / 3

Nested Loop (cost=300,906.25..579,967.31 rows=6,160 width=40) (actual time=746.855..2,074.095 rows=149,920 loops=3)

13. 88.726 1,545.554 ↓ 24.3 149,920 3 / 3

Nested Loop (cost=300,905.82..573,101.77 rows=6,160 width=40) (actual time=746.843..1,545.554 rows=149,920 loops=3)

14. 96.426 1,007.069 ↓ 24.3 149,920 3 / 3

Hash Join (cost=300,905.40..566,192.14 rows=6,160 width=40) (actual time=746.824..1,007.069 rows=149,920 loops=3)

  • Hash Cond: (t7.parent = t5.id)
15. 279.225 305.276 ↑ 1.3 149,920 3 / 3

Parallel Bitmap Heap Scan on scorecard_result t7 (cost=13,831.01..278,621.20 rows=189,164 width=24) (actual time=139.945..305.276 rows=149,920 loops=3)

  • Recheck Cond: ((question)::text = 'Compliance'::text)
  • Heap Blocks: exact=81,274
16. 26.051 26.051 ↑ 1.0 449,759 1 / 3

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

  • Index Cond: ((question)::text = 'Compliance'::text)
17. 123.891 605.367 ↑ 1.0 449,759 3 / 3

Hash (cost=281,207.30..281,207.30 rows=469,367 width=32) (actual time=605.366..605.367 rows=449,759 loops=3)

  • Buckets: 524,288 Batches: 1 Memory Usage: 32,206kB
18. 403.677 481.476 ↑ 1.0 449,759 3 / 3

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

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

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

  • Index Cond: (parent IS NULL)
20. 449.759 449.759 ↑ 1.0 1 449,759 / 3

Index Scan using scorecard_run_pkey on scorecard_run t4 (cost=0.42..1.12 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=449,759)

  • Index Cond: (id = t5.scorecard_run_id)
21. 449.759 449.759 ↑ 1.0 1 449,759 / 3

Index Scan using enriched_transcript_pkey on enriched_transcript t3 (cost=0.42..1.11 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=449,759)

  • Index Cond: (id = t4.enriched_transcript_id)
22. 449.759 449.759 ↑ 1.0 1 449,759 / 3

Index Scan using transcript_pkey on transcript t2 (cost=0.42..1.15 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=449,759)

  • Index Cond: (id = t3.transcript_id)
23. 449.759 449.759 ↑ 1.0 1 449,759 / 3

Index Scan using call_pkey on call t0 (cost=0.42..1.23 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=449,759)

  • Index Cond: (id = t2.call_id)
  • 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))
24. 0.120 0.231 ↓ 1.0 787 3 / 3

Hash (cost=29.76..29.76 rows=776 width=16) (actual time=0.231..0.231 rows=787 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 45kB
25. 0.111 0.111 ↓ 1.0 787 3 / 3

Seq Scan on agent t1 (cost=0.00..29.76 rows=776 width=16) (actual time=0.010..0.111 rows=787 loops=3)

26. 749.598 749.598 ↑ 1.0 1 449,759 / 3

Index Scan using scorecard_result_unique_position on scorecard_result t6 (cost=0.56..14.81 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=449,759)

  • Index Cond: (parent = t5.id)
  • Filter: ((question)::text = 'Communication'::text)
  • Rows Removed by Filter: 2
27. 322.504 322.504 ↑ 1.0 1 80,626

Index Scan using scorecard_result_unique_position on scorecard_result t8 (cost=0.56..14.81 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=80,626)

  • Index Cond: (parent = t5.id)
  • Filter: ((question)::text = 'Conduct'::text)
  • Rows Removed by Filter: 2
Planning time : 2.064 ms
Execution time : 4,303.854 ms