explain.depesz.com

PostgreSQL's explain analyze made readable

Result: w4ih

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 4,722.688 ↑ 6.7 15 1

Sort (cost=45,754.32..45,754.57 rows=100 width=36) (actual time=4,722.687..4,722.688 rows=15 loops=1)

  • Sort Key: ((times.n)::integer)
  • Sort Method: quicksort Memory: 26kB
2. 199.459 4,722.680 ↑ 6.7 15 1

HashAggregate (cost=45,749.00..45,751.00 rows=100 width=36) (actual time=4,722.674..4,722.680 rows=15 loops=1)

  • Group Key: ((times.n)::integer)
3. 865.923 4,523.221 ↓ 8.3 768,429 1

Nested Loop Left Join (cost=1,084.30..43,889.88 rows=92,956 width=53) (actual time=2,641.545..4,523.221 rows=768,429 loops=1)

  • Join Filter: ((t0.began >= (lag(times.t) OVER (?))) AND (t0.began <= times.t))
  • Rows Removed by Join Filter: 10,757,992
4. 0.067 0.088 ↑ 6.7 15 1

WindowAgg (cost=0.00..2.50 rows=100 width=20) (actual time=0.008..0.088 rows=15 loops=1)

5. 0.021 0.021 ↑ 6.7 15 1

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

6. 1,289.919 3,657.210 ↓ 91.9 768,428 15

Materialize (cost=1,084.30..29,266.79 rows=8,366 width=57) (actual time=0.836..243.814 rows=768,428 loops=15)

7. 43.591 2,367.291 ↓ 91.9 768,428 1

Gather (cost=1,084.30..29,224.96 rows=8,366 width=57) (actual time=12.503..2,367.291 rows=768,428 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
8. 29.829 2,323.700 ↓ 78.1 384,214 2 / 2

Nested Loop (cost=84.30..27,388.36 rows=4,921 width=57) (actual time=10.375..2,323.700 rows=384,214 loops=2)

9. 14.290 949.122 ↓ 75.8 96,054 2 / 2

Nested Loop (cost=83.74..15,751.41 rows=1,268 width=24) (actual time=10.355..949.122 rows=96,054 loops=2)

10. 14.757 646.649 ↓ 75.8 96,061 2 / 2

Nested Loop (cost=83.32..15,095.49 rows=1,268 width=24) (actual time=10.341..646.649 rows=96,061 loops=2)

11. 25.493 343.703 ↓ 76.4 96,063 2 / 2

Nested Loop (cost=82.89..14,459.22 rows=1,257 width=24) (actual time=10.324..343.703 rows=96,063 loops=2)

12. 24.407 29.902 ↓ 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.309..29.902 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,273
13. 5.495 5.495 ↓ 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=10.990..10.990 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. 288.308 288.308 ↑ 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.003..0.003 rows=1 loops=192,205)

  • Index Cond: (call_id = t0.id)
15. 288.189 288.189 ↑ 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.003..0.003 rows=1 loops=192,126)

  • Index Cond: (transcript_id = t1.id)
16. 288.183 288.183 ↑ 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.003..0.003 rows=1 loops=192,122)

  • Index Cond: (enriched_transcript_id = t2.id)
17. 1,344.749 1,344.749 ↑ 6.2 4 192,107 / 2

Index Scan using scorecard_result_idx_scorecard_run_id_parent on scorecard_result t4 (cost=0.56..8.93 rows=25 width=65) (actual time=0.009..0.014 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 : 2.091 ms
Execution time : 4,730.858 ms