explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lQlL

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 8,366.183 ↑ 2.4 14 1

Sort (cost=747,199.84..747,199.92 rows=33 width=36) (actual time=8,366.181..8,366.183 rows=14 loops=1)

  • Sort Key: i.bucket
  • Sort Method: quicksort Memory: 26kB
2. 446.002 8,366.168 ↑ 2.4 14 1

HashAggregate (cost=747,198.35..747,199.01 rows=33 width=36) (actual time=8,366.160..8,366.168 rows=14 loops=1)

  • Group Key: i.bucket
3. 452.851 7,920.166 ↑ 2.4 768,428 1

Hash Join (cost=203,610.11..710,913.39 rows=1,814,248 width=53) (actual time=4,539.541..7,920.166 rows=768,428 loops=1)

  • Hash Cond: (t4.scorecard_run_id = t3.id)
4. 4,248.045 4,248.045 ↓ 1.0 1,728,960 1

Seq Scan on scorecard_result t4 (cost=0.00..482,864.34 rows=1,679,056 width=65) (actual time=0.421..4,248.045 rows=1,728,960 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,958,467
5. 51.986 3,219.270 ↑ 2.4 192,107 1

Hash (cost=197,772.49..197,772.49 rows=467,009 width=20) (actual time=3,219.270..3,219.270 rows=192,107 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 13,852kB
6. 229.493 3,167.284 ↑ 2.4 192,107 1

Nested Loop (cost=110,817.01..197,772.49 rows=467,009 width=20) (actual time=2,624.369..3,167.284 rows=192,107 loops=1)

  • Join Filter: ((t0.began >= i.begin) AND (t0.began <= i.""end""))
  • Rows Removed by Join Filter: 2,497,391
7. 142.333 2,745.684 ↓ 1.5 192,107 1

Hash Join (cost=110,817.01..124,214.54 rows=127,366 width=24) (actual time=2,624.341..2,745.684 rows=192,107 loops=1)

  • Hash Cond: (t3.enriched_transcript_id = t2.id)
8. 63.864 63.864 ↓ 1.0 432,240 1

Seq Scan on scorecard_run t3 (cost=0.00..10,503.09 rows=432,209 width=32) (actual time=0.012..63.864 rows=432,240 loops=1)

9. 58.715 2,539.487 ↓ 1.5 192,122 1

Hash (cost=109,224.75..109,224.75 rows=127,381 width=24) (actual time=2,539.487..2,539.487 rows=192,122 loops=1)

  • Buckets: 262,144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 12,555kB
10. 159.126 2,480.772 ↓ 1.5 192,122 1

Hash Join (cost=97,101.90..109,224.75 rows=127,381 width=24) (actual time=2,337.264..2,480.772 rows=192,122 loops=1)

  • Hash Cond: (t2.transcript_id = t1.id)
11. 60.259 60.259 ↑ 1.0 432,257 1

Seq Scan on enriched_transcript t2 (cost=0.00..9,239.60 rows=432,260 width=32) (actual time=0.008..60.259 rows=432,257 loops=1)

12. 48.954 2,261.387 ↓ 1.5 192,126 1

Hash (cost=95,524.06..95,524.06 rows=126,227 width=24) (actual time=2,261.387..2,261.387 rows=192,126 loops=1)

  • Buckets: 262,144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 12,555kB
13. 135.715 2,212.433 ↓ 1.5 192,126 1

Hash Join (cost=79,028.22..95,524.06 rows=126,227 width=24) (actual time=2,106.663..2,212.433 rows=192,126 loops=1)

  • Hash Cond: (t1.call_id = t0.id)
14. 53.527 53.527 ↓ 1.0 432,262 1

Seq Scan on transcript t1 (cost=0.00..15,371.43 rows=428,343 width=32) (actual time=0.003..53.527 rows=432,262 loops=1)

15. 63.903 2,023.191 ↓ 1.5 192,205 1

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

  • Buckets: 262,144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 12,560kB
16. 1,945.202 1,959.288 ↓ 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=14.990..1,959.288 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
17. 14.086 14.086 ↓ 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=14.086..14.086 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))
18.          

SubPlan (for Bitmap Heap Scan)

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

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

21. 0.000 0.000 ↓ 0.0 0

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

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

23. 0.000 0.000 ↓ 0.0 0

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

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

26. 0.000 0.000 ↓ 0.0 0

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

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

28. 192.077 192.107 ↑ 2.4 14 192,107

Materialize (cost=0.00..4.17 rows=33 width=20) (actual time=0.000..0.001 rows=14 loops=192,107)

29. 0.002 0.030 ↑ 2.4 14 1

Subquery Scan on i (cost=0.00..4.00 rows=33 width=20) (actual time=0.021..0.030 rows=14 loops=1)

  • Filter: (i.bucket > 0)
  • Rows Removed by Filter: 1
30. 0.013 0.028 ↑ 6.7 15 1

WindowAgg (cost=0.00..2.75 rows=100 width=20) (actual time=0.018..0.028 rows=15 loops=1)

31. 0.015 0.015 ↑ 6.7 15 1

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

  • Filter: (t IS NOT NULL)
Planning time : 1.430 ms
Execution time : 8,366.561 ms