explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aRPh : New Query

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 6,241.242 ↑ 3,175.0 15 1

Sort (cost=573,976.66..574,095.72 rows=47,625 width=40) (actual time=6,241.240..6,241.242 rows=15 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. 249.132 6,241.230 ↑ 3,175.0 15 1

HashAggregate (cost=568,847.58..570,276.33 rows=47,625 width=40) (actual time=6,241.034..6,241.230 rows=15 loops=1)

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

Hash Join (cost=115,668.83..558,402.86 rows=522,236 width=57) (actual time=3,126.079..5,992.098 rows=768,428 loops=1)

  • Hash Cond: (t4.scorecard_run_id = t3.id)
4. 2,853.771 2,853.771 ↓ 1.0 1,710,356 1

Seq Scan on scorecard_result t4 (cost=0.00..425,924.92 rows=1,697,170 width=65) (actual time=0.017..2,853.771 rows=1,710,356 loops=1)

  • Filter: ((parent IS NULL) OR ((question)::text = ANY ('{Communication,Compliance,Conduct}'::text[])))
  • Rows Removed by Filter: 11,795,722
5. 35.636 2,355.051 ↓ 1.5 192,107 1

Hash (cost=114,024.15..114,024.15 rows=131,575 width=24) (actual time=2,355.051..2,355.051 rows=192,107 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 12,554kB
6. 116.782 2,319.415 ↓ 1.5 192,107 1

Hash Join (cost=100,713.98..114,024.15 rows=131,575 width=24) (actual time=2,230.359..2,319.415 rows=192,107 loops=1)

  • Hash Cond: (t3.enriched_transcript_id = t2.id)
7. 112.821 112.821 ↑ 1.0 427,589 1

Seq Scan on scorecard_run t3 (cost=0.00..10,390.94 rows=427,594 width=32) (actual time=0.408..112.821 rows=427,589 loops=1)

8. 43.279 2,089.812 ↓ 1.5 192,122 1

Hash (cost=99,069.27..99,069.27 rows=131,577 width=24) (actual time=2,089.812..2,089.812 rows=192,122 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 12,555kB
9. 127.693 2,046.533 ↓ 1.5 192,122 1

Hash Join (cost=87,016.75..99,069.27 rows=131,577 width=24) (actual time=1,936.962..2,046.533 rows=192,122 loops=1)

  • Hash Cond: (t2.transcript_id = t1.id)
10. 74.605 74.605 ↓ 1.0 427,606 1

Seq Scan on enriched_transcript t2 (cost=0.00..9,140.00 rows=427,600 width=32) (actual time=0.369..74.605 rows=427,606 loops=1)

11. 40.336 1,844.235 ↓ 1.5 192,126 1

Hash (cost=85,380.47..85,380.47 rows=130,902 width=24) (actual time=1,844.235..1,844.235 rows=192,126 loops=1)

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

Hash Join (cost=68,997.70..85,380.47 rows=130,902 width=24) (actual time=1,706.702..1,803.899 rows=192,126 loops=1)

  • Hash Cond: (t1.call_id = t0.id)
13. 67.008 67.008 ↓ 1.0 427,611 1

Seq Scan on transcript t1 (cost=0.00..15,266.07 rows=425,407 width=32) (actual time=0.009..67.008 rows=427,611 loops=1)

14. 55.190 1,616.923 ↓ 1.9 192,205 1

Hash (cost=67,711.35..67,711.35 rows=102,908 width=24) (actual time=1,616.923..1,616.923 rows=192,205 loops=1)

  • Buckets: 262,144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 12,560kB
15. 1,548.401 1,561.733 ↓ 1.9 192,205 1

Bitmap Heap Scan on call t0 (cost=7,427.51..67,711.35 rows=102,908 width=24) (actual time=14.240..1,561.733 rows=192,205 loops=1)

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

Bitmap Index Scan on call_idx_began (cost=0.00..6,752.04 rows=154,362 width=0) (actual time=13.332..13.332 rows=192,205 loops=1)

  • Index Cond: ((began >= '2020-03-02 00:00:00+11'::timestamp with time zone) AND (began < '2020-06-12 00:00:00+10'::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)