explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Tow5

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 5,560.807 ↑ 3,730.3 16 1

Sort (cost=641,325.75..641,474.96 rows=59,685 width=40) (actual time=5,560.806..5,560.807 rows=16 loops=1)

  • Sort Key: ((date_trunc('year'::text, t0.began) + ('7 days'::interval * date_part('week'::text, t0.began))))
  • Sort Method: quicksort Memory: 26kB
2. 235.449 5,560.793 ↑ 3,730.3 16 1

HashAggregate (cost=634,800.66..636,591.21 rows=59,685 width=40) (actual time=5,560.386..5,560.793 rows=16 loops=1)

  • Group Key: (date_trunc('year'::text, t0.began) + ('7 days'::interval * date_part('week'::text, t0.began)))
3. 737.073 5,325.344 ↓ 1.6 768,428 1

Hash Join (cost=125,808.36..624,904.00 rows=494,833 width=57) (actual time=2,890.381..5,325.344 rows=768,428 loops=1)

  • Hash Cond: (t4.scorecard_run_id = t3.id)
4. 2,398.236 2,398.236 ↓ 1.0 1,729,072 1

Seq Scan on scorecard_result t4 (cost=0.00..482,902.03 rows=1,679,187 width=65) (actual time=0.020..2,398.236 rows=1,729,072 loops=1)

  • Filter: ((parent IS NULL) OR ((question)::text = 'Communication'::text) OR ((question)::text = 'Compliance'::text) OR ((question)::text = 'Conduct'::text))
  • Rows Removed by Filter: 11,959,454
5. 43.302 2,190.035 ↓ 1.5 192,107 1

Hash (cost=124,216.28..124,216.28 rows=127,366 width=24) (actual time=2,190.035..2,190.035 rows=192,107 loops=1)

  • Buckets: 262,144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 12,554kB
6. 117.943 2,146.733 ↓ 1.5 192,107 1

Hash Join (cost=110,818.75..124,216.28 rows=127,366 width=24) (actual time=2,048.397..2,146.733 rows=192,107 loops=1)

  • Hash Cond: (t3.enriched_transcript_id = t2.id)
7. 38.985 38.985 ↓ 1.0 432,268 1

Seq Scan on scorecard_run t3 (cost=0.00..10,503.09 rows=432,209 width=32) (actual time=0.006..38.985 rows=432,268 loops=1)

8. 41.028 1,989.805 ↓ 1.5 192,122 1

Hash (cost=109,226.49..109,226.49 rows=127,381 width=24) (actual time=1,989.805..1,989.805 rows=192,122 loops=1)

  • Buckets: 262,144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 12,555kB
9. 109.468 1,948.777 ↓ 1.5 192,122 1

Hash Join (cost=97,103.53..109,226.49 rows=127,381 width=24) (actual time=1,859.513..1,948.777 rows=192,122 loops=1)

  • Hash Cond: (t2.transcript_id = t1.id)
10. 35.682 35.682 ↓ 1.0 432,285 1

Seq Scan on enriched_transcript t2 (cost=0.00..9,239.60 rows=432,260 width=32) (actual time=0.005..35.682 rows=432,285 loops=1)

11. 41.822 1,803.627 ↓ 1.5 192,126 1

Hash (cost=95,525.56..95,525.56 rows=126,238 width=24) (actual time=1,803.627..1,803.627 rows=192,126 loops=1)

  • Buckets: 262,144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 12,555kB
12. 116.947 1,761.805 ↓ 1.5 192,126 1

Hash Join (cost=79,028.22..95,525.56 rows=126,238 width=24) (actual time=1,667.972..1,761.805 rows=192,126 loops=1)

  • Hash Cond: (t1.call_id = t0.id)
13. 47.033 47.033 ↓ 1.0 432,290 1

Seq Scan on transcript t1 (cost=0.00..15,372.82 rows=428,382 width=32) (actual time=0.005..47.033 rows=432,290 loops=1)

14. 53.608 1,597.825 ↓ 1.5 192,205 1

Hash (cost=77,443.20..77,443.20 rows=126,802 width=24) (actual time=1,597.825..1,597.825 rows=192,205 loops=1)

  • Buckets: 262,144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 12,560kB
15. 1,528.017 1,544.217 ↓ 1.5 192,205 1

Bitmap Heap Scan on call t0 (cost=7,571.90..77,443.20 rows=126,802 width=24) (actual time=17.392..1,544.217 rows=192,205 loops=1)

  • Recheck Cond: ((began >= '2020-03-01 13:00:00+00'::timestamp with time zone) AND (began < '2020-06-11 14:00:00+00'::timestamp with time zone))
  • Filter: (lisa_super_user() OR ((hashed SubPlan 1) IS TRUE))
  • Heap Blocks: exact=6,983
16. 16.200 16.200 ↓ 1.0 192,205 1

Bitmap Index Scan on call_idx_began (cost=0.00..6,890.45 rows=190,203 width=0) (actual time=16.200..16.200 rows=192,205 loops=1)

  • Index Cond: ((began >= '2020-03-01 13:00:00+00'::timestamp with time zone) AND (began < '2020-06-11 14:00:00+00'::timestamp with time zone))
17.          

SubPlan (for Bitmap Heap Scan)

18. 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)
19. 0.000 0.000 ↓ 0.0 0

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

20. 0.000 0.000 ↓ 0.0 0

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

21. 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)

22. 0.000 0.000 ↓ 0.0 0

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

23. 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)
24. 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)

25. 0.000 0.000 ↓ 0.0 0

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

26. 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)

Planning time : 1.509 ms
Execution time : 5,563.491 ms