explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6zJu

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 6,387.364 ↑ 14.3 14 1

Sort (cost=214,261.24..214,261.74 rows=200 width=36) (actual time=6,387.363..6,387.364 rows=14 loops=1)

  • Sort Key: i.bucket
  • Sort Method: quicksort Memory: 26kB
2. 223.989 6,387.355 ↑ 14.3 14 1

HashAggregate (cost=214,249.60..214,253.60 rows=200 width=36) (actual time=6,387.350..6,387.355 rows=14 loops=1)

  • Group Key: i.bucket
3. 1,330.409 6,163.366 ↑ 1.2 768,428 1

Nested Loop Left Join (cost=1,084.30..195,745.16 rows=925,222 width=53) (actual time=969.310..6,163.366 rows=768,428 loops=1)

  • Join Filter: ((t0.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[])[i.bucket]) AND (t0.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[])[(i.bucket + 1)]))
  • Rows Removed by Join Filter: 9,989,564
4. 0.017 0.017 ↑ 71.4 14 1

Function Scan on generate_series i (cost=0.00..10.00 rows=1,000 width=4) (actual time=0.005..0.017 rows=14 loops=1)

5. 1,293.315 4,832.940 ↓ 92.3 768,428 14

Materialize (cost=1,084.30..29,215.97 rows=8,327 width=57) (actual time=0.908..345.210 rows=768,428 loops=14)

6. 0.000 3,539.625 ↓ 92.3 768,428 1

Gather (cost=1,084.30..29,174.34 rows=8,327 width=57) (actual time=12.667..3,539.625 rows=768,428 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
7. 96.156 3,555.469 ↓ 78.4 384,214 2 / 2

Nested Loop (cost=84.30..27,341.64 rows=4,898 width=57) (actual time=10.234..3,555.469 rows=384,214 loops=2)

8. 7.598 1,250.082 ↓ 76.1 96,054 2 / 2

Nested Loop (cost=83.74..15,784.81 rows=1,263 width=24) (actual time=10.218..1,250.082 rows=96,054 loops=2)

9. 0.334 858.240 ↓ 76.1 96,061 2 / 2

Nested Loop (cost=83.32..15,131.51 rows=1,263 width=24) (actual time=10.209..858.240 rows=96,061 loops=2)

10. 41.816 473.654 ↓ 76.6 96,063 2 / 2

Nested Loop (cost=82.89..14,496.84 rows=1,254 width=24) (actual time=10.198..473.654 rows=96,063 loops=2)

11. 11.433 431.838 ↓ 76.6 96,063 2 / 2

Nested Loop (cost=82.89..14,459.22 rows=1,254 width=24) (actual time=10.197..431.838 rows=96,063 loops=2)

12. 30.459 35.995 ↓ 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=10.183..35.995 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=3,210
13. 5.535 5.535 ↓ 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=11.071..11.071 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]))
14. 384.410 384.410 ↑ 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.004..0.004 rows=1 loops=192,205)

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

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=192,126)

16. 384.252 384.252 ↑ 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.004..0.004 rows=1 loops=192,126)

  • Index Cond: (transcript_id = t1.id)
17. 384.244 384.244 ↑ 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.004..0.004 rows=1 loops=192,122)

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

Index Scan using scorecard_result_idx_scorecard_run_id_parent on scorecard_result t4 (cost=0.56..8.90 rows=25 width=65) (actual time=0.017..0.023 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
Planning time : 4.195 ms
Execution time : 6,397.041 ms