explain.depesz.com

PostgreSQL's explain analyze made readable

Result: K3Nj

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 8,977.540 ↑ 14.3 14 1

Sort (cost=157,953.16..157,953.66 rows=200 width=36) (actual time=8,977.540..8,977.540 rows=14 loops=1)

  • Sort Key: i.bucket
  • Sort Method: quicksort Memory: 26kB
2. 215.762 8,977.531 ↑ 14.3 14 1

HashAggregate (cost=157,941.52..157,945.52 rows=200 width=36) (actual time=8,977.526..8,977.531 rows=14 loops=1)

  • Group Key: i.bucket
3. 1,265.508 8,761.769 ↓ 1.2 768,428 1

Nested Loop Left Join (cost=733.86..145,252.64 rows=634,444 width=53) (actual time=1,528.165..8,761.769 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,219.229 7,496.244 ↓ 134.6 768,428 14

Materialize (cost=733.86..31,056.91 rows=5,710 width=57) (actual time=0.931..535.446 rows=768,428 loops=14)

6. 61.124 6,277.015 ↓ 134.6 768,428 1

Nested Loop (cost=733.86..31,028.36 rows=5,710 width=57) (actual time=12.991..6,277.015 rows=768,428 loops=1)

7. 0.000 3,526.393 ↓ 133.8 192,107 1

Nested Loop (cost=733.30..18,928.39 rows=1,436 width=24) (actual time=12.975..3,526.393 rows=192,107 loops=1)

8. 0.000 2,956.358 ↓ 133.8 192,122 1

Nested Loop (cost=732.88..18,185.58 rows=1,436 width=24) (actual time=12.965..2,956.358 rows=192,122 loops=1)

9. 23.984 2,384.219 ↓ 134.9 192,126 1

Nested Loop (cost=732.46..17,464.77 rows=1,424 width=24) (actual time=12.955..2,384.219 rows=192,126 loops=1)

10. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

11. 62.039 2,360.234 ↓ 134.9 192,126 1

Nested Loop (cost=732.46..17,450.51 rows=1,424 width=24) (actual time=12.951..2,360.234 rows=192,126 loops=1)

12. 1,709.566 1,721.580 ↓ 134.0 192,205 1

Bitmap Heap Scan on call t0 (cost=732.03..7,451.55 rows=1,434 width=24) (actual time=12.936..1,721.580 rows=192,205 loops=1)

  • 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]))
  • Filter: (lisa_super_user() OR ((hashed SubPlan 1) IS TRUE))
  • Heap Blocks: exact=6,983
13. 12.014 12.014 ↓ 89.4 192,205 1

Bitmap Index Scan on call_idx_began (cost=0.00..81.93 rows=2,151 width=0) (actual time=12.014..12.014 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.          

SubPlan (for Bitmap Heap Scan)

15. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=71.08..604.41 rows=18,133 width=16) (never executed)

  • Hash Cond: (((((unnest(regexp_match(t1_1.c1, '^team_(.+)'::text))))::character varying))::text = (t0_1.access_control_name)::text)
16. 0.000 0.000 ↓ 0.0 0

Result (cost=0.01..202.01 rows=10,000 width=32) (never executed)

17. 0.000 0.000 ↓ 0.0 0

ProjectSet (cost=0.01..52.01 rows=10,000 width=32) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Function Scan on json_array_elements_text t1_1 (cost=0.01..1.01 rows=100 width=32) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Hash (cost=54.07..54.07 rows=1,360 width=48) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=26.88..54.07 rows=1,360 width=48) (never executed)

  • Hash Cond: (t2_1.ac_team_id = t0_1.id)
21. 0.000 0.000 ↓ 0.0 0

Seq Scan on ac_team_membership t2_1 (cost=0.00..23.60 rows=1,360 width=32) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Hash (cost=17.50..17.50 rows=750 width=48) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Seq Scan on ac_team t0_1 (cost=0.00..17.50 rows=750 width=48) (never executed)

24. 576.615 576.615 ↑ 1.0 1 192,205

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

  • Index Cond: (call_id = t0.id)
25. 576.378 576.378 ↑ 1.0 1 192,126

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)
26. 576.366 576.366 ↑ 1.0 1 192,122

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)
27. 2,689.498 2,689.498 ↑ 6.5 4 192,107

Index Scan using scorecard_result_idx_scorecard_run_id_parent on scorecard_result t4 (cost=0.56..8.17 rows=26 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 = ANY ('{Communication,Compliance,Conduct}'::text[])))
  • Rows Removed by Filter: 29
Planning time : 3.932 ms
Execution time : 8,985.786 ms