explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NVSX

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 21,309.915 ↑ 4.4 45 1

Sort (cost=217,006.44..217,006.94 rows=200 width=36) (actual time=21,309.912..21,309.915 rows=45 loops=1)

  • Sort Key: i.bucket
  • Sort Method: quicksort Memory: 27kB
2. 434.891 21,309.889 ↑ 4.4 45 1

HashAggregate (cost=216,994.79..216,998.79 rows=200 width=36) (actual time=21,309.873..21,309.889 rows=45 loops=1)

  • Group Key: i.bucket
3. 8,452.696 20,874.998 ↓ 1.7 1,602,614 1

Nested Loop Left Join (cost=1,084.30..198,137.01 rows=942,889 width=53) (actual time=5,873.610..20,874.998 rows=1,602,614 loops=1)

  • Join Filter: ((t0.began >= ('{""2019-07-31 14:00:00+00"",""2019-08-04 14:00:00+00"",""2019-08-11 14:00:00+00"",""2019-08-18 14:00:00+00"",""2019-08-25 14:00:00+00"",""2019-09-01 14:00:00+00"",""2019-09-08 14:00:00+00"",""2019-09-15 14:00:00+00"",""2019-09-22 14:00:00+00"",""2019-09-29 14:00:00+00"",""2019-10-06 13:00:00+00"",""2019-10-13 13:00:00+00"",""2019-10-20 13:00:00+00"",""2019-10-27 13:00:00+00"",""2019-11-03 13:00:00+00"",""2019-11-10 13:00:00+00"",""2019-11-17 13:00:00+00"",""2019-11-24 13:00:00+00"",""2019-12-01 13:00:00+00"",""2019-12-08 13:00:00+00"",""2019-12-15 13:00:00+00"",""2019-12-22 13:00:00+00"",""2019-12-29 13:00:00+00"",""2020-01-05 13:00:00+00"",""2020-01-12 13:00:00+00"",""2020-01-19 13:00:00+00"",""2020-01-26 13:00:00+00"",""2020-02-02 13:00:00+00"",""2020-02-09 13:00:00+00"",""2020-02-16 13:00:00+00"",""2020-02-23 13:00:00+00"",""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 < ('{""2019-07-31 14:00:00+00"",""2019-08-04 14:00:00+00"",""2019-08-11 14:00:00+00"",""2019-08-18 14:00:00+00"",""2019-08-25 14:00:00+00"",""2019-09-01 14:00:00+00"",""2019-09-08 14:00:00+00"",""2019-09-15 14:00:00+00"",""2019-09-22 14:00:00+00"",""2019-09-29 14:00:00+00"",""2019-10-06 13:00:00+00"",""2019-10-13 13:00:00+00"",""2019-10-20 13:00:00+00"",""2019-10-27 13:00:00+00"",""2019-11-03 13:00:00+00"",""2019-11-10 13:00:00+00"",""2019-11-17 13:00:00+00"",""2019-11-24 13:00:00+00"",""2019-12-01 13:00:00+00"",""2019-12-08 13:00:00+00"",""2019-12-15 13:00:00+00"",""2019-12-22 13:00:00+00"",""2019-12-29 13:00:00+00"",""2020-01-05 13:00:00+00"",""2020-01-12 13:00:00+00"",""2020-01-19 13:00:00+00"",""2020-01-26 13:00:00+00"",""2020-02-02 13:00:00+00"",""2020-02-09 13:00:00+00"",""2020-02-16 13:00:00+00"",""2020-02-23 13:00:00+00"",""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: 70,514,224
4. 0.052 0.052 ↑ 22.2 45 1

Function Scan on generate_series i (cost=0.00..10.00 rows=1,000 width=4) (actual time=0.011..0.052 rows=45 loops=1)

5. 7,340.958 12,422.250 ↓ 188.9 1,602,596 45

Materialize (cost=1,084.30..28,428.23 rows=8,486 width=57) (actual time=0.731..276.050 rows=1,602,596 loops=45)

6. 0.000 5,081.292 ↓ 188.9 1,602,596 1

Gather (cost=1,084.30..28,385.80 rows=8,486 width=57) (actual time=32.760..5,081.292 rows=1,602,596 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
7. 151.363 5,102.598 ↓ 160.5 801,298 2 / 2

Nested Loop (cost=84.30..26,537.20 rows=4,992 width=57) (actual time=30.249..5,102.598 rows=801,298 loops=2)

8. 54.948 2,146.692 ↓ 155.9 200,324 2 / 2

Nested Loop (cost=83.74..15,806.25 rows=1,285 width=24) (actual time=30.223..2,146.692 rows=200,324 loops=2)

9. 62.928 1,490.745 ↓ 155.9 200,333 2 / 2

Nested Loop (cost=83.32..15,141.69 rows=1,285 width=24) (actual time=30.211..1,490.745 rows=200,333 loops=2)

10. 69.071 826.812 ↓ 158.0 200,335 2 / 2

Nested Loop (cost=82.89..14,499.52 rows=1,268 width=24) (actual time=30.198..826.812 rows=200,335 loops=2)

11. 82.127 155.391 ↓ 158.7 200,784 2 / 2

Nested Loop (cost=82.47..6,283.53 rows=1,265 width=24) (actual time=30.179..155.391 rows=200,784 loops=2)

12. 58.306 73.264 ↓ 158.7 200,784 2 / 2

Parallel Bitmap Heap Scan on call t0 (cost=82.47..6,245.58 rows=1,265 width=24) (actual time=30.175..73.264 rows=200,784 loops=2)

  • Recheck Cond: ((began >= ('{""2019-07-31 14:00:00+00"",""2019-08-04 14:00:00+00"",""2019-08-11 14:00:00+00"",""2019-08-18 14:00:00+00"",""2019-08-25 14:00:00+00"",""2019-09-01 14:00:00+00"",""2019-09-08 14:00:00+00"",""2019-09-15 14:00:00+00"",""2019-09-22 14:00:00+00"",""2019-09-29 14:00:00+00"",""2019-10-06 13:00:00+00"",""2019-10-13 13:00:00+00"",""2019-10-20 13:00:00+00"",""2019-10-27 13:00:00+00"",""2019-11-03 13:00:00+00"",""2019-11-10 13:00:00+00"",""2019-11-17 13:00:00+00"",""2019-11-24 13:00:00+00"",""2019-12-01 13:00:00+00"",""2019-12-08 13:00:00+00"",""2019-12-15 13:00:00+00"",""2019-12-22 13:00:00+00"",""2019-12-29 13:00:00+00"",""2020-01-05 13:00:00+00"",""2020-01-12 13:00:00+00"",""2020-01-19 13:00:00+00"",""2020-01-26 13:00:00+00"",""2020-02-02 13:00:00+00"",""2020-02-09 13:00:00+00"",""2020-02-16 13:00:00+00"",""2020-02-23 13:00:00+00"",""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 < ('{""2019-07-31 14:00:00+00"",""2019-08-04 14:00:00+00"",""2019-08-11 14:00:00+00"",""2019-08-18 14:00:00+00"",""2019-08-25 14:00:00+00"",""2019-09-01 14:00:00+00"",""2019-09-08 14:00:00+00"",""2019-09-15 14:00:00+00"",""2019-09-22 14:00:00+00"",""2019-09-29 14:00:00+00"",""2019-10-06 13:00:00+00"",""2019-10-13 13:00:00+00"",""2019-10-20 13:00:00+00"",""2019-10-27 13:00:00+00"",""2019-11-03 13:00:00+00"",""2019-11-10 13:00:00+00"",""2019-11-17 13:00:00+00"",""2019-11-24 13:00:00+00"",""2019-12-01 13:00:00+00"",""2019-12-08 13:00:00+00"",""2019-12-15 13:00:00+00"",""2019-12-22 13:00:00+00"",""2019-12-29 13:00:00+00"",""2020-01-05 13:00:00+00"",""2020-01-12 13:00:00+00"",""2020-01-19 13:00:00+00"",""2020-01-26 13:00:00+00"",""2020-02-02 13:00:00+00"",""2020-02-09 13:00:00+00"",""2020-02-16 13:00:00+00"",""2020-02-23 13:00:00+00"",""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[])[46]))
  • Heap Blocks: exact=5,991
13. 14.958 14.958 ↓ 186.7 401,567 1 / 2

Bitmap Index Scan on call_idx_began (cost=0.00..81.93 rows=2,151 width=0) (actual time=29.916..29.916 rows=401,567 loops=1)

  • Index Cond: ((began >= ('{""2019-07-31 14:00:00+00"",""2019-08-04 14:00:00+00"",""2019-08-11 14:00:00+00"",""2019-08-18 14:00:00+00"",""2019-08-25 14:00:00+00"",""2019-09-01 14:00:00+00"",""2019-09-08 14:00:00+00"",""2019-09-15 14:00:00+00"",""2019-09-22 14:00:00+00"",""2019-09-29 14:00:00+00"",""2019-10-06 13:00:00+00"",""2019-10-13 13:00:00+00"",""2019-10-20 13:00:00+00"",""2019-10-27 13:00:00+00"",""2019-11-03 13:00:00+00"",""2019-11-10 13:00:00+00"",""2019-11-17 13:00:00+00"",""2019-11-24 13:00:00+00"",""2019-12-01 13:00:00+00"",""2019-12-08 13:00:00+00"",""2019-12-15 13:00:00+00"",""2019-12-22 13:00:00+00"",""2019-12-29 13:00:00+00"",""2020-01-05 13:00:00+00"",""2020-01-12 13:00:00+00"",""2020-01-19 13:00:00+00"",""2020-01-26 13:00:00+00"",""2020-02-02 13:00:00+00"",""2020-02-09 13:00:00+00"",""2020-02-16 13:00:00+00"",""2020-02-23 13:00:00+00"",""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 < ('{""2019-07-31 14:00:00+00"",""2019-08-04 14:00:00+00"",""2019-08-11 14:00:00+00"",""2019-08-18 14:00:00+00"",""2019-08-25 14:00:00+00"",""2019-09-01 14:00:00+00"",""2019-09-08 14:00:00+00"",""2019-09-15 14:00:00+00"",""2019-09-22 14:00:00+00"",""2019-09-29 14:00:00+00"",""2019-10-06 13:00:00+00"",""2019-10-13 13:00:00+00"",""2019-10-20 13:00:00+00"",""2019-10-27 13:00:00+00"",""2019-11-03 13:00:00+00"",""2019-11-10 13:00:00+00"",""2019-11-17 13:00:00+00"",""2019-11-24 13:00:00+00"",""2019-12-01 13:00:00+00"",""2019-12-08 13:00:00+00"",""2019-12-15 13:00:00+00"",""2019-12-22 13:00:00+00"",""2019-12-29 13:00:00+00"",""2020-01-05 13:00:00+00"",""2020-01-12 13:00:00+00"",""2020-01-19 13:00:00+00"",""2020-01-26 13:00:00+00"",""2020-02-02 13:00:00+00"",""2020-02-09 13:00:00+00"",""2020-02-16 13:00:00+00"",""2020-02-23 13:00:00+00"",""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[])[46]))
14. 0.000 0.000 ↑ 1.0 1 401,567 / 2

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

15. 602.351 602.351 ↑ 1.0 1 401,567 / 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=401,567)

  • Index Cond: (call_id = t0.id)
16. 601.005 601.005 ↑ 1.0 1 400,670 / 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=400,670)

  • Index Cond: (transcript_id = t1.id)
17. 600.999 600.999 ↑ 1.0 1 400,666 / 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=400,666)

  • Index Cond: (enriched_transcript_id = t2.id)
18. 2,804.543 2,804.543 ↑ 6.5 4 400,649 / 2

Index Scan using scorecard_result_idx_scorecard_run_id_parent on scorecard_result t4 (cost=0.56..8.09 rows=26 width=65) (actual time=0.009..0.014 rows=4 loops=400,649)

  • Index Cond: (scorecard_run_id = t3.id)
  • Filter: ((parent IS NULL) OR ((question)::text = ANY ('{Communication,Compliance,Conduct}'::text[])))
  • Rows Removed by Filter: 27
Planning time : 4.432 ms
Execution time : 21,323.744 ms