explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6vPs

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 6,058.211 ↑ 2.4 14 1

Sort (cost=34,964.42..34,964.51 rows=33 width=36) (actual time=6,058.210..6,058.211 rows=14 loops=1)

  • Sort Key: i.bucket
  • Sort Method: quicksort Memory: 26kB
2. 440.823 6,058.197 ↑ 2.4 14 1

HashAggregate (cost=34,962.93..34,963.59 rows=33 width=36) (actual time=6,058.190..6,058.197 rows=14 loops=1)

  • Group Key: i.bucket
3. 1,977.300 5,617.374 ↓ 24.8 768,428 1

Nested Loop (cost=1,084.30..34,342.39 rows=31,027 width=53) (actual time=26.511..5,617.374 rows=768,428 loops=1)

  • Join Filter: ((t0.began >= i.begin) AND (t0.began <= i.""end""))
  • Rows Removed by Join Filter: 9,989,564
4. 0.000 2,871.646 ↓ 90.8 768,428 1

Gather (cost=1,084.30..29,451.50 rows=8,462 width=57) (actual time=26.472..2,871.646 rows=768,428 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
5. 114.939 4,155.011 ↓ 77.2 384,214 2 / 2

Nested Loop (cost=84.30..27,605.30 rows=4,978 width=57) (actual time=18.500..4,155.011 rows=384,214 loops=2)

6. 32.569 1,638.735 ↓ 75.1 96,054 2 / 2

Nested Loop (cost=83.74..15,763.58 rows=1,279 width=24) (actual time=18.480..1,638.735 rows=96,054 loops=2)

7. 33.560 1,125.861 ↓ 75.1 96,061 2 / 2

Nested Loop (cost=83.32..15,102.07 rows=1,279 width=24) (actual time=18.467..1,125.861 rows=96,061 loops=2)

8. 70.224 611.986 ↓ 75.9 96,063 2 / 2

Nested Loop (cost=82.89..14,461.57 rows=1,265 width=24) (actual time=18.455..611.986 rows=96,063 loops=2)

9. 49.233 61.250 ↓ 76.0 96,102 2 / 2

Parallel Bitmap Heap Scan on call t0 (cost=82.47..6,245.58 rows=1,265 width=24) (actual time=18.437..61.250 rows=96,102 loops=2)

  • Recheck Cond: ((began >= ('{""2020-03-01 13:00:00+00"",""2020-03-08 13:00:00+00"",""2020-03-15 13:00:00+00"",""2020-03-22 13:00:00+00"",""2020-03-29 13:00:00+00"",""2020-04-05 14:00:00+00"",""2020-04-12 14:00:00+00"",""2020-04-19 14:00:00+00"",""2020-04-26 14:00:00+00"",""2020-05-03 14:00:00+00"",""2020-05-10 14:00:00+00"",""2020-05-17 14:00:00+00"",""2020-05-24 14:00:00+00"",""2020-05-31 14:00:00+00"",""2020-06-11 14:00:00+00""}'::timestamp with time zone[])[1]) AND (began <= ('{""2020-03-01 13:00:00+00"",""2020-03-08 13:00:00+00"",""2020-03-15 13:00:00+00"",""2020-03-22 13:00:00+00"",""2020-03-29 13:00:00+00"",""2020-04-05 14:00:00+00"",""2020-04-12 14:00:00+00"",""2020-04-19 14:00:00+00"",""2020-04-26 14:00:00+00"",""2020-05-03 14:00:00+00"",""2020-05-10 14:00:00+00"",""2020-05-17 14:00:00+00"",""2020-05-24 14:00:00+00"",""2020-05-31 14:00:00+00"",""2020-06-11 14:00:00+00""}'::timestamp with time zone[])[15]))
  • Heap Blocks: exact=2,021
10. 12.017 12.017 ↓ 89.4 192,205 1 / 2

Bitmap Index Scan on call_idx_began (cost=0.00..81.93 rows=2,151 width=0) (actual time=24.034..24.034 rows=192,205 loops=1)

  • Index Cond: ((began >= ('{""2020-03-01 13:00:00+00"",""2020-03-08 13:00:00+00"",""2020-03-15 13:00:00+00"",""2020-03-22 13:00:00+00"",""2020-03-29 13:00:00+00"",""2020-04-05 14:00:00+00"",""2020-04-12 14:00:00+00"",""2020-04-19 14:00:00+00"",""2020-04-26 14:00:00+00"",""2020-05-03 14:00:00+00"",""2020-05-10 14:00:00+00"",""2020-05-17 14:00:00+00"",""2020-05-24 14:00:00+00"",""2020-05-31 14:00:00+00"",""2020-06-11 14:00:00+00""}'::timestamp with time zone[])[1]) AND (began <= ('{""2020-03-01 13:00:00+00"",""2020-03-08 13:00:00+00"",""2020-03-15 13:00:00+00"",""2020-03-22 13:00:00+00"",""2020-03-29 13:00:00+00"",""2020-04-05 14:00:00+00"",""2020-04-12 14:00:00+00"",""2020-04-19 14:00:00+00"",""2020-04-26 14:00:00+00"",""2020-05-03 14:00:00+00"",""2020-05-10 14:00:00+00"",""2020-05-17 14:00:00+00"",""2020-05-24 14:00:00+00"",""2020-05-31 14:00:00+00"",""2020-06-11 14:00:00+00""}'::timestamp with time zone[])[15]))
11. 480.512 480.512 ↑ 1.0 1 192,205 / 2

Index Scan using transcript_unique_call_id on transcript t1 (cost=0.42..6.49 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=192,205)

  • Index Cond: (call_id = t0.id)
12. 480.315 480.315 ↑ 1.0 1 192,126 / 2

Index Scan using enriched_transcript_unique_transcript_id on enriched_transcript t2 (cost=0.42..0.51 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=192,126)

  • Index Cond: (transcript_id = t1.id)
13. 480.305 480.305 ↑ 1.0 1 192,122 / 2

Index Scan using scorecard_run_unique_enriched_transcript_id on scorecard_run t3 (cost=0.42..0.52 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=192,122)

  • Index Cond: (enriched_transcript_id = t2.id)
14. 2,401.338 2,401.338 ↑ 6.2 4 192,107 / 2

Index Scan using scorecard_result_idx_scorecard_run_id_parent on scorecard_result t4 (cost=0.56..9.01 rows=25 width=65) (actual time=0.017..0.025 rows=4 loops=192,107)

  • Index Cond: (scorecard_run_id = t3.id)
  • Filter: ((parent IS NULL) OR ((question)::text = 'Communication'::text) OR ((question)::text = 'Compliance'::text) OR ((question)::text = 'Conduct'::text))
  • Rows Removed by Filter: 29
15. 768.391 768.428 ↑ 2.4 14 768,428

Materialize (cost=0.00..4.17 rows=33 width=20) (actual time=0.000..0.001 rows=14 loops=768,428)

16. 0.004 0.037 ↑ 2.4 14 1

Subquery Scan on i (cost=0.00..4.00 rows=33 width=20) (actual time=0.022..0.037 rows=14 loops=1)

  • Filter: (i.bucket > 0)
  • Rows Removed by Filter: 1
17. 0.017 0.033 ↑ 6.7 15 1

WindowAgg (cost=0.00..2.75 rows=100 width=20) (actual time=0.019..0.033 rows=15 loops=1)

18. 0.016 0.016 ↑ 6.7 15 1

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

  • Filter: (t IS NOT NULL)
Planning time : 3.089 ms
Execution time : 6,059.508 ms