explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jRH

Settings
# exclusive inclusive rows x rows loops node
1. 1.489 2,980.497 ↓ 323.0 323 1

Unique (cost=335.34..335.36 rows=1 width=28) (actual time=2,977.924..2,980.497 rows=323 loops=1)

2.          

Initplan (forUnique)

3. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on params (cost=0.00..1.05 rows=1 width=4) (actual time=0.010..0.013 rows=1 loops=1)

  • Filter: ((key)::text = 'current_year'::text)
  • Rows Removed by Filter: 3
4. 3.100 2,978.995 ↓ 1,665.0 1,665 1

Sort (cost=334.29..334.29 rows=1 width=28) (actual time=2,977.921..2,978.995 rows=1,665 loops=1)

  • Sort Key: t.id, ay.id_activity, si1.id_activity, s1.id, si1.id, si2.id, si3.id
  • Sort Method: quicksort Memory: 179kB
5. 6.141 2,975.895 ↓ 1,665.0 1,665 1

Hash Right Join (cost=246.59..334.28 rows=1 width=28) (actual time=2,974.644..2,975.895 rows=1,665 loops=1)

  • Hash Cond: (si3.id_service = s3.id)
  • Join Filter: ((si3.id_activity = pla3.old_act_id) OR (si3.id_activity = si2.id_activity))
  • Rows Removed by Join Filter: 10901
  • Filter: ((si1.id IS NULL) OR (si2.id IS NULL) OR (si3.id IS NULL))
  • Rows Removed by Filter: 1567
6. 2.734 2.734 ↑ 1.0 3,990 1

Seq Scan on service_item si3 (cost=0.00..72.68 rows=3,990 width=12) (actual time=0.006..2.734 rows=3,990 loops=1)

  • Filter: (NOT priority_canceled)
  • Rows Removed by Filter: 78
7. 2.320 2,967.020 ↓ 2,739.0 2,739 1

Hash (cost=246.58..246.58 rows=1 width=36) (actual time=2,967.019..2,967.020 rows=2,739 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 217kB
8. 6.847 2,964.700 ↓ 2,739.0 2,739 1

Nested Loop Left Join (cost=154.81..246.58 rows=1 width=36) (actual time=2,935.275..2,964.700 rows=2,739 loops=1)

9. 6.640 2,941.419 ↓ 2,739.0 2,739 1

Hash Right Join (cost=154.42..242.11 rows=1 width=32) (actual time=2,935.256..2,941.419 rows=2,739 loops=1)

  • Hash Cond: (si2.id_service = s2.id)
  • Join Filter: ((si2.id_activity = pla2.old_act_id) OR (si2.id_activity = si1.id_activity))
  • Rows Removed by Join Filter: 9704
10. 2.811 2.811 ↑ 1.0 3,990 1

Seq Scan on service_item si2 (cost=0.00..72.68 rows=3,990 width=12) (actual time=0.015..2.811 rows=3,990 loops=1)

  • Filter: (NOT priority_canceled)
  • Rows Removed by Filter: 78
11. 2.383 2,931.968 ↓ 2,524.0 2,524 1

Hash (cost=154.41..154.41 rows=1 width=32) (actual time=2,931.968..2,931.968 rows=2,524 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 179kB
12. 5.712 2,929.585 ↓ 2,524.0 2,524 1

Nested Loop Left Join (cost=58.54..154.41 rows=1 width=32) (actual time=2,748.095..2,929.585 rows=2,524 loops=1)

13. 5.647 2,921.349 ↓ 2,524.0 2,524 1

Nested Loop Left Join (cost=58.40..154.23 rows=1 width=28) (actual time=2,748.089..2,921.349 rows=2,524 loops=1)

14. 168.495 2,908.130 ↓ 2,524.0 2,524 1

Hash Join (cost=58.12..145.93 rows=1 width=24) (actual time=2,748.070..2,908.130 rows=2,524 loops=1)

  • Hash Cond: (si1.id_service = s1.id)
  • Join Filter: ((si1.id_activity = pla1.old_act_id) OR (si1.id_activity = a.id))
  • Rows Removed by Join Filter: 1018020
15. 2.974 2.974 ↑ 1.0 3,990 1

Seq Scan on service_item si1 (cost=0.00..72.68 rows=3,990 width=12) (actual time=0.007..2.974 rows=3,990 loops=1)

  • Filter: (NOT priority_canceled)
  • Rows Removed by Filter: 78
16. 205.747 2,736.661 ↓ 62,764.0 251,056 1

Hash (cost=58.07..58.07 rows=4 width=24) (actual time=2,736.661..2,736.661 rows=251,056 loops=1)

  • Buckets: 262144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 15333kB
17. 184.914 2,530.914 ↓ 62,764.0 251,056 1

Hash Right Join (cost=54.76..58.07 rows=4 width=24) (actual time=2,345.927..2,530.914 rows=251,056 loops=1)

  • Hash Cond: (pla1.act_id = a.id)
18. 0.124 0.124 ↑ 1.0 167 1

Seq Scan on view_prio_linked_act pla1 (cost=0.00..2.67 rows=167 width=8) (actual time=0.021..0.124 rows=167 loops=1)

19. 180.903 2,345.876 ↓ 54,226.2 216,905 1

Hash (cost=54.71..54.71 rows=4 width=20) (actual time=2,345.875..2,345.876 rows=216,905 loops=1)

  • Buckets: 262144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 13012kB
20. 344.741 2,164.973 ↓ 54,226.2 216,905 1

Nested Loop (cost=1.24..54.71 rows=4 width=20) (actual time=0.167..2,164.973 rows=216,905 loops=1)

  • Join Filter: (t.id = s0.id_teacher)
21. 126.965 1,609.907 ↓ 42,065.0 42,065 1

Nested Loop Left Join (cost=0.97..54.20 rows=1 width=24) (actual time=0.141..1,609.907 rows=42,065 loops=1)

22. 182.419 1,398.812 ↓ 42,065.0 42,065 1

Nested Loop (cost=0.69..45.90 rows=1 width=20) (actual time=0.135..1,398.812 rows=42,065 loops=1)

23. 383.904 1,029.875 ↓ 93,259.0 93,259 1

Nested Loop (cost=0.41..41.59 rows=1 width=16) (actual time=0.092..1,029.875 rows=93,259 loops=1)

  • Join Filter: (a.id_activity_name = an.id)
  • Rows Removed by Join Filter: 402213
24. 0.035 0.035 ↑ 1.0 4 1

Seq Scan on activity_name an (cost=0.00..2.12 rows=4 width=4) (actual time=0.010..0.035 rows=4 loops=1)

  • Filter: ((shortlabel)::text = ANY ('{COURS,TD,TP,COURS/TD}'::text[]))
  • Rows Removed by Filter: 71
25. 395.756 645.936 ↓ 61,934.0 123,868 4

Materialize (cost=0.41..39.35 rows=2 width=20) (actual time=0.018..161.484 rows=123,868 loops=4)

26. 158.910 250.180 ↓ 61,934.0 123,868 1

Nested Loop (cost=0.41..39.34 rows=2 width=20) (actual time=0.063..250.180 rows=123,868 loops=1)

27. 0.475 3.023 ↓ 179.0 179 1

Nested Loop Semi Join (cost=0.41..26.66 rows=1 width=12) (actual time=0.053..3.023 rows=179 loops=1)

28. 0.628 2.120 ↓ 214.0 214 1

Nested Loop (cost=0.28..26.44 rows=1 width=16) (actual time=0.026..2.120 rows=214 loops=1)

29. 0.334 0.334 ↓ 193.0 386 1

Seq Scan on teacher t (cost=0.00..9.82 rows=2 width=4) (actual time=0.007..0.334 rows=386 loops=1)

  • Filter: (id = COALESCE(id))
30. 1.158 1.158 ↑ 1.0 1 386

Index Scan using service_id_teacher_year_key on service s1 (cost=0.28..8.30 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=386)

  • Index Cond: ((id_teacher = t.id) AND (year = 2018))
31. 0.428 0.428 ↑ 1.0 1 214

Index Scan using status_pkey on status (cost=0.13..0.21 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=214)

  • Index Cond: (id = s1.id_status)
  • Filter: ((code)::text = ANY ('{PERM,NPERM}'::text[]))
  • Rows Removed by Filter: 0
32. 88.247 88.247 ↓ 230.7 692 179

Seq Scan on activity a (cost=0.00..12.65 rows=3 width=8) (actual time=0.003..0.493 rows=692 loops=179)

  • Filter: (id = COALESCE(id))
33. 186.518 186.518 ↓ 0.0 0 93,259

Index Only Scan using activity_year_id_activity_year_key on activity_year ay (cost=0.28..4.30 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=93,259)

  • Index Cond: ((id_activity = a.id) AND (year = COALESCE($0)))
  • Heap Fetches: 0
34. 84.130 84.130 ↑ 1.0 1 42,065

Index Scan using service_id_teacher_year_key on service s2 (cost=0.28..8.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=42,065)

  • Index Cond: ((id_teacher = t.id) AND (year = 2017))
35. 210.325 210.325 ↓ 1.2 5 42,065

Index Only Scan using service_id_teacher_year_key on service s0 (cost=0.28..0.46 rows=4 width=4) (actual time=0.002..0.005 rows=5 loops=42,065)

  • Index Cond: (id_teacher = s1.id_teacher)
  • Heap Fetches: 0
36. 7.572 7.572 ↑ 1.0 1 2,524

Index Scan using service_id_teacher_year_key on service s3 (cost=0.28..8.30 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=2,524)

  • Index Cond: ((id_teacher = t.id) AND (year = 2016))
37. 2.524 2.524 ↓ 0.0 0 2,524

Index Scan using act_id_index on view_prio_linked_act pla2 (cost=0.14..0.17 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=2,524)

  • Index Cond: (act_id = si1.id_activity)
38. 5.478 16.434 ↓ 0.0 0 2,739

Bitmap Heap Scan on view_prio_linked_act pla3 (cost=0.39..4.44 rows=3 width=8) (actual time=0.006..0.006 rows=0 loops=2,739)

  • Recheck Cond: ((act_id = si1.id_activity) OR (act_id = si2.id_activity))
  • Heap Blocks: exact=89
39. 5.478 10.956 ↓ 0.0 0 2,739

BitmapOr (cost=0.39..0.39 rows=3 width=0) (actual time=0.004..0.004 rows=0 loops=2,739)

40. 2.739 2.739 ↓ 0.0 0 2,739

Bitmap Index Scan on act_id_index (cost=0.00..0.15 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=2,739)

  • Index Cond: (act_id = si1.id_activity)
41. 2.739 2.739 ↓ 0.0 0 2,739

Bitmap Index Scan on act_id_index (cost=0.00..0.15 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=2,739)

  • Index Cond: (act_id = si2.id_activity)