explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YPoo

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 4,904.260 ↑ 1.0 1 1

Result (cost=813,740.62..813,740.63 rows=1 width=32) (actual time=4,904.260..4,904.260 rows=1 loops=1)

2.          

Initplan (for Result)

3. 9.059 4,904.258 ↑ 1.0 1 1

Aggregate (cost=813,740.60..813,740.62 rows=1 width=32) (actual time=4,904.258..4,904.258 rows=1 loops=1)

4. 34.426 4,895.199 ↓ 16.4 80,626 1

Nested Loop Left Join (cost=288,163.84..813,728.30 rows=4,922 width=0) (actual time=623.880..4,895.199 rows=80,626 loops=1)

5. 51.202 4,538.269 ↓ 16.4 80,626 1

Nested Loop (cost=288,163.28..740,756.35 rows=4,922 width=16) (actual time=623.869..4,538.269 rows=80,626 loops=1)

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

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

Gather (cost=288,163.13..740,563.61 rows=14,766 width=24) (actual time=623.860..4,487.059 rows=449,759 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 109.041 4,636.914 ↓ 24.4 149,920 3 / 3

Nested Loop Left Join (cost=287,163.13..738,087.01 rows=6,152 width=24) (actual time=597.187..4,636.914 rows=149,920 loops=3)

9. 54.780 3,778.275 ↓ 24.4 149,920 3 / 3

Hash Join (cost=287,162.57..646,879.49 rows=6,152 width=24) (actual time=597.174..3,778.275 rows=149,920 loops=3)

  • Hash Cond: (t0.agent_id = t1.id)
10. 126.116 3,723.259 ↓ 24.4 149,920 3 / 3

Nested Loop (cost=287,123.11..646,823.80 rows=6,152 width=40) (actual time=596.898..3,723.259 rows=149,920 loops=3)

11. 89.898 3,147.384 ↓ 24.4 149,920 3 / 3

Nested Loop (cost=287,122.69..639,247.42 rows=6,152 width=40) (actual time=596.882..3,147.384 rows=149,920 loops=3)

12. 100.461 2,607.727 ↓ 24.4 149,920 3 / 3

Nested Loop (cost=287,122.26..632,170.26 rows=6,152 width=40) (actual time=596.868..2,607.727 rows=149,920 loops=3)

13. 115.467 2,057.507 ↓ 24.4 149,920 3 / 3

Nested Loop (cost=287,121.84..625,313.63 rows=6,152 width=40) (actual time=596.853..2,057.507 rows=149,920 loops=3)

14. 97.631 1,492.281 ↓ 24.4 149,920 3 / 3

Hash Join (cost=287,121.42..618,412.50 rows=6,152 width=40) (actual time=596.832..1,492.281 rows=149,920 loops=3)

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

Parallel Seq Scan on scorecard_result t7 (cost=0.00..330,795.15 rows=188,925 width=24) (actual time=0.008..799.240 rows=149,920 loops=3)

  • Filter: ((question)::text = 'Compliance'::text)
  • Rows Removed by Filter: 4,654,245
16. 123.811 595.410 ↑ 1.0 449,759 3 / 3

Hash (cost=281,261.76..281,261.76 rows=468,773 width=32) (actual time=595.410..595.410 rows=449,759 loops=3)

  • Buckets: 524,288 Batches: 1 Memory Usage: 32,206kB
17. 396.010 471.599 ↑ 1.0 449,759 3 / 3

Bitmap Heap Scan on scorecard_result t5 (cost=15,709.55..281,261.76 rows=468,773 width=32) (actual time=141.228..471.599 rows=449,759 loops=3)

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

Bitmap Index Scan on scorecard_result_unique_position (cost=0.00..15,592.36 rows=468,773 width=0) (actual time=75.588..75.589 rows=449,854 loops=3)

  • Index Cond: (parent IS NULL)
19. 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)
20. 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)
21. 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)
22. 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))
23. 0.120 0.236 ↓ 1.0 787 3 / 3

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

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

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

25. 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.82 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=449,759)

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

Index Scan using scorecard_result_unique_position on scorecard_result t8 (cost=0.56..14.82 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 : 1.962 ms
Execution time : 4,907.308 ms