explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xk8

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 5,320.482 ↑ 1.0 1 1

Result (cost=995,139.17..995,139.18 rows=1 width=32) (actual time=5,320.481..5,320.482 rows=1 loops=1)

2.          

CTE __local_0__

3. 0.427 5,320.355 ↑ 1.1 30 1

Subquery Scan on __local_1__ (cost=995,137.66..995,138.32 rows=33 width=64) (actual time=5,319.970..5,320.355 rows=30 loops=1)

4. 0.020 5,319.928 ↑ 1.1 30 1

Sort (cost=995,137.66..995,137.74 rows=33 width=36) (actual time=5,319.925..5,319.928 rows=30 loops=1)

  • Sort Key: i.bucket
  • Sort Method: quicksort Memory: 27kB
5. 517.185 5,319.908 ↑ 1.1 30 1

HashAggregate (cost=995,136.16..995,136.82 rows=33 width=36) (actual time=5,319.893..5,319.908 rows=30 loops=1)

  • Group Key: i.bucket
6. 754.533 4,802.723 ↑ 3.5 1,799,036 1

Hash Join (cost=224,854.46..868,955.68 rows=6,309,024 width=53) (actual time=659.589..4,802.723 rows=1,799,036 loops=1)

  • Hash Cond: (t1.call_id = t0.id)
7. 421.431 3,763.550 ↓ 1.0 1,799,036 1

Hash Join (cost=53,399.96..574,674.86 rows=1,720,643 width=65) (actual time=374.083..3,763.550 rows=1,799,036 loops=1)

  • Hash Cond: (t2.transcript_id = t1.id)
8. 418.520 3,212.370 ↓ 1.0 1,799,036 1

Hash Join (cost=31,783.47..548,541.64 rows=1,720,643 width=65) (actual time=243.931..3,212.370 rows=1,799,036 loops=1)

  • Hash Cond: (t3.enriched_transcript_id = t2.id)
9. 447.372 2,674.033 ↓ 1.0 1,799,036 1

Hash Join (cost=16,549.81..528,791.25 rows=1,720,643 width=65) (actual time=123.711..2,674.033 rows=1,799,036 loops=1)

  • Hash Cond: (t4.scorecard_run_id = t3.id)
10. 2,103.369 2,103.369 ↓ 1.0 1,799,036 1

Seq Scan on scorecard_result t4 (cost=0.00..507,724.71 rows=1,720,643 width=65) (actual time=0.009..2,103.369 rows=1,799,036 loops=1)

  • Filter: ((parent IS NULL) OR ((question)::text = 'Communication'::text) OR ((question)::text = 'Compliance'::text) OR ((question)::text = 'Conduct'::text))
  • Rows Removed by Filter: 12,613,457
11. 68.146 123.292 ↓ 1.0 449,759 1

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

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

Seq Scan on scorecard_run t3 (cost=0.00..10,928.25 rows=449,725 width=32) (actual time=0.005..55.146 rows=449,759 loops=1)

13. 67.453 119.817 ↓ 1.0 449,776 1

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

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

Seq Scan on enriched_transcript t2 (cost=0.00..9,612.18 rows=449,718 width=32) (actual time=0.010..52.364 rows=449,776 loops=1)

15. 72.411 129.749 ↑ 1.0 449,781 1

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

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

Seq Scan on transcript t1 (cost=0.00..15,993.44 rows=449,844 width=32) (actual time=0.009..57.338 rows=449,781 loops=1)

17. 89.944 284.640 ↑ 3.5 450,693 1

Hash (cost=142,487.65..142,487.65 rows=1,577,748 width=20) (actual time=284.640..284.640 rows=450,693 loops=1)

  • Buckets: 1,048,576 Batches: 2 Memory Usage: 19,658kB
18. 46.801 194.696 ↑ 3.5 450,693 1

Nested Loop (cost=0.42..142,487.65 rows=1,577,748 width=20) (actual time=0.071..194.696 rows=450,693 loops=1)

19. 0.015 0.111 ↓ 1.6 52 1

Subquery Scan on i (cost=0.00..4.00 rows=33 width=20) (actual time=0.019..0.111 rows=52 loops=1)

  • Filter: (i.bucket > 0)
  • Rows Removed by Filter: 1
20. 0.064 0.096 ↑ 1.9 53 1

WindowAgg (cost=0.00..2.75 rows=100 width=20) (actual time=0.016..0.096 rows=53 loops=1)

21. 0.032 0.032 ↑ 1.9 53 1

Function Scan on unnest times (cost=0.00..1.00 rows=100 width=16) (actual time=0.013..0.032 rows=53 loops=1)

  • Filter: (t IS NOT NULL)
22. 147.784 147.784 ↑ 5.5 8,667 52

Index Scan using call_idx_began on call t0 (cost=0.42..3,839.58 rows=47,811 width=24) (actual time=0.004..2.842 rows=8,667 loops=52)

  • Index Cond: ((began >= i.begin) AND (began <= i.""end"") AND (began >= '2019-07-01 14:00:00+00'::timestamp with time zone) AND (began <= '2020-07-02 14:00:00+00'::timestamp with time zone))
23.          

CTE __local_2__

24. 0.068 5,320.457 ↑ 1.0 1 1

Aggregate (cost=0.83..0.84 rows=1 width=32) (actual time=5,320.457..5,320.457 rows=1 loops=1)

25. 5,320.389 5,320.389 ↑ 1.1 30 1

CTE Scan on __local_0__ (cost=0.00..0.66 rows=33 width=24) (actual time=5,319.977..5,320.389 rows=30 loops=1)

26.          

Initplan (for Result)

27. 5,320.472 5,320.472 ↑ 1.0 1 1

CTE Scan on __local_2__ (cost=0.00..0.02 rows=1 width=32) (actual time=5,320.471..5,320.472 rows=1 loops=1)

Planning time : 1.234 ms
Execution time : 5,321.112 ms