explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m9xn

Settings
# exclusive inclusive rows x rows loops node
1. 1.457 3,365.207 ↓ 327.0 327 1

Unique (cost=10,000,000,724.68..10,000,000,724.70 rows=1 width=28) (actual time=3,362.631..3,365.207 rows=327 loops=1)

2.          

CTE prio_linked_act

3. 1.104 35.889 ↓ 48.0 192 1

Unique (cost=237.58..237.61 rows=4 width=8) (actual time=33.774..35.889 rows=192 loops=1)

4. 2.271 34.785 ↓ 373.5 1,494 1

Sort (cost=237.58..237.59 rows=4 width=8) (actual time=33.774..34.785 rows=1,494 loops=1)

  • Sort Key: a1.id, a2.id
  • Sort Method: quicksort Memory: 119kB
5. 1.636 32.514 ↓ 373.5 1,494 1

Hash Join (cost=221.39..237.54 rows=4 width=8) (actual time=30.473..32.514 rows=1,494 loops=1)

  • Hash Cond: ((a1.id_activity_name = an1.id) AND (a1.id = ay1.id_activity))
6. 0.468 0.468 ↑ 1.0 692 1

Seq Scan on activity a1 (cost=0.00..10.92 rows=692 width=8) (actual time=0.024..0.468 rows=692 loops=1)

7. 2.793 30.410 ↓ 12.6 3,584 1

Hash (cost=217.12..217.12 rows=285 width=16) (actual time=30.410..30.410 rows=3,584 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 200kB
8. 4.093 27.617 ↓ 12.6 3,584 1

Hash Join (cost=168.25..217.12 rows=285 width=16) (actual time=22.219..27.617 rows=3,584 loops=1)

  • Hash Cond: (ay1.id = ap1.id_activity_year)
9. 1.433 1.433 ↑ 1.0 2,256 1

Seq Scan on activity_year ay1 (cost=0.00..37.56 rows=2,256 width=8) (actual time=0.010..1.433 rows=2,256 loops=1)

10. 2.768 22.091 ↓ 12.6 3,584 1

Hash (cost=164.68..164.68 rows=285 width=16) (actual time=22.091..22.091 rows=3,584 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 200kB
11. 4.002 19.323 ↓ 12.6 3,584 1

Hash Join (cost=117.07..164.68 rows=285 width=16) (actual time=14.428..19.323 rows=3,584 loops=1)

  • Hash Cond: (ap1.id_ue = ua.id_ue)
12. 1.358 1.358 ↑ 1.0 2,092 1

Seq Scan on activity_parent ap1 (cost=0.00..36.92 rows=2,092 width=8) (actual time=0.009..1.358 rows=2,092 loops=1)

13. 0.731 13.963 ↓ 20.3 973 1

Hash (cost=116.47..116.47 rows=48 width=16) (actual time=13.962..13.963 rows=973 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
14. 1.777 13.232 ↓ 20.3 973 1

Hash Join (cost=68.98..116.47 rows=48 width=16) (actual time=8.076..13.232 rows=973 loops=1)

  • Hash Cond: (ap2.id_ue = ua.id_ancestor)
15. 2.702 11.264 ↓ 14.7 1,644 1

Hash Join (cost=65.27..111.15 rows=112 width=16) (actual time=7.217..11.264 rows=1,644 loops=1)

  • Hash Cond: (ap2.id_activity_year = ay2.id)
16. 1.386 1.386 ↑ 1.0 2,092 1

Seq Scan on activity_parent ap2 (cost=0.00..36.92 rows=2,092 width=8) (actual time=0.005..1.386 rows=2,092 loops=1)

17. 1.196 7.176 ↓ 12.9 1,556 1

Hash (cost=63.75..63.75 rows=121 width=16) (actual time=7.175..7.176 rows=1,556 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 89kB
18. 2.786 5.980 ↓ 12.9 1,556 1

Hash Join (cost=16.52..63.75 rows=121 width=16) (actual time=1.780..5.980 rows=1,556 loops=1)

  • Hash Cond: (ay2.id_activity = a2.id)
19. 1.443 1.443 ↑ 1.0 2,256 1

Seq Scan on activity_year ay2 (cost=0.00..37.56 rows=2,256 width=8) (actual time=0.005..1.443 rows=2,256 loops=1)

20. 0.414 1.751 ↓ 14.1 521 1

Hash (cost=16.06..16.06 rows=37 width=12) (actual time=1.750..1.751 rows=521 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
21. 0.854 1.337 ↓ 14.1 521 1

Hash Join (cost=2.17..16.06 rows=37 width=12) (actual time=0.063..1.337 rows=521 loops=1)

  • Hash Cond: (a2.id_activity_name = an1.id)
22. 0.447 0.447 ↑ 1.0 692 1

Seq Scan on activity a2 (cost=0.00..10.92 rows=692 width=8) (actual time=0.005..0.447 rows=692 loops=1)

23. 0.005 0.036 ↑ 1.0 4 1

Hash (cost=2.12..2.12 rows=4 width=4) (actual time=0.036..0.036 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.031 0.031 ↑ 1.0 4 1

Seq Scan on activity_name an1 (cost=0.00..2.12 rows=4 width=4) (actual time=0.016..0.031 rows=4 loops=1)

  • Filter: ((shortlabel)::text = ANY ('{COURS,TD,TP,COURS/TD}'::text[]))
  • Rows Removed by Filter: 71
25. 0.099 0.191 ↑ 1.0 119 1

Hash (cost=2.23..2.23 rows=119 width=8) (actual time=0.191..0.191 rows=119 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
26. 0.092 0.092 ↑ 1.0 119 1

Seq Scan on ue_ancestor ua (cost=0.00..2.23 rows=119 width=8) (actual time=0.009..0.092 rows=119 loops=1)

  • Filter: (priority_disable IS FALSE)
  • Rows Removed by Filter: 4
27.          

Initplan (forUnique)

28. 0.018 0.018 ↑ 1.0 1 1

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

  • Filter: ((key)::text = 'current_year'::text)
  • Rows Removed by Filter: 3
29. 3.070 3,363.732 ↓ 1,670.0 1,670 1

Sort (cost=10,000,000,486.01..10,000,000,486.02 rows=1 width=28) (actual time=3,362.629..3,363.732 rows=1,670 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
30. 6.298 3,360.662 ↓ 1,670.0 1,670 1

Hash Right Join (cost=10,000,000,398.02..10,000,000,486.00 rows=1 width=28) (actual time=3,359.397..3,360.662 rows=1,670 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: 11076
  • Filter: ((si1.id IS NULL) OR (si2.id IS NULL) OR (si3.id IS NULL))
  • Rows Removed by Filter: 1579
31. 2.657 2.657 ↑ 1.0 4,068 1

Seq Scan on service_item si3 (cost=0.00..72.68 rows=4,068 width=12) (actual time=0.029..2.657 rows=4,068 loops=1)

32. 2.571 3,351.707 ↓ 2,756.0 2,756 1

Hash (cost=10,000,000,398.01..10,000,000,398.01 rows=1 width=36) (actual time=3,351.706..3,351.707 rows=2,756 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 218kB
33. 372.072 3,349.136 ↓ 2,756.0 2,756 1

Nested Loop Left Join (cost=10,000,000,309.89..10,000,000,398.01 rows=1 width=36) (actual time=2,609.575..3,349.136 rows=2,756 loops=1)

  • Join Filter: ((pla3.act_id = si1.id_activity) OR (pla3.act_id = si2.id_activity))
  • Rows Removed by Join Filter: 528897
34. 7.338 2,616.028 ↓ 2,756.0 2,756 1

Hash Right Join (cost=309.89..397.87 rows=1 width=32) (actual time=2,609.294..2,616.028 rows=2,756 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: 10663
35. 2.707 2.707 ↑ 1.0 4,068 1

Seq Scan on service_item si2 (cost=0.00..72.68 rows=4,068 width=12) (actual time=0.018..2.707 rows=4,068 loops=1)

36. 2.387 2,605.983 ↓ 2,541.0 2,541 1

Hash (cost=309.88..309.88 rows=1 width=32) (actual time=2,605.982..2,605.983 rows=2,541 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 181kB
37. 3.526 2,603.596 ↓ 2,541.0 2,541 1

Hash Left Join (cost=221.75..309.88 rows=1 width=32) (actual time=1,290.777..2,603.596 rows=2,541 loops=1)

  • Hash Cond: (si1.id_activity = pla2.act_id)
38. 720.773 2,599.798 ↓ 2,541.0 2,541 1

Hash Left Join (cost=221.62..309.73 rows=1 width=28) (actual time=1,290.477..2,599.798 rows=2,541 loops=1)

  • Hash Cond: (a.id = pla1.act_id)
  • Filter: ((si1.id_activity = pla1.old_act_id) OR (si1.id_activity = a.id))
  • Rows Removed by Filter: 1028795
39. 649.228 1,842.713 ↓ 73,711.7 884,540 1

Hash Join (cost=221.49..309.54 rows=12 width=32) (actual time=1,194.780..1,842.713 rows=884,540 loops=1)

  • Hash Cond: (si1.id_service = s1.id)
40. 2.750 2.750 ↑ 1.0 4,068 1

Seq Scan on service_item si1 (cost=0.00..72.68 rows=4,068 width=12) (actual time=0.014..2.750 rows=4,068 loops=1)

41. 173.394 1,190.735 ↓ 54,226.2 216,905 1

Hash (cost=221.44..221.44 rows=4 width=24) (actual time=1,190.734..1,190.735 rows=216,905 loops=1)

  • Buckets: 262144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 13742kB
42. 173.406 1,017.341 ↓ 54,226.2 216,905 1

Hash Right Join (cost=180.22..221.44 rows=4 width=24) (actual time=843.018..1,017.341 rows=216,905 loops=1)

  • Hash Cond: ((s3.id_teacher = t.id) AND (s3.year = (ay.year - 3)))
43. 0.970 0.970 ↑ 1.0 1,398 1

Seq Scan on service s3 (cost=0.00..28.98 rows=1,398 width=12) (actual time=0.021..0.970 rows=1,398 loops=1)

44. 181.789 842.965 ↓ 54,226.2 216,905 1

Hash (cost=180.16..180.16 rows=4 width=24) (actual time=842.965..842.965 rows=216,905 loops=1)

  • Buckets: 262144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 13859kB
45. 171.804 661.176 ↓ 54,226.2 216,905 1

Hash Right Join (cost=138.93..180.16 rows=4 width=24) (actual time=488.539..661.176 rows=216,905 loops=1)

  • Hash Cond: ((s2.id_teacher = t.id) AND (s2.year = (ay.year - 2)))
46. 0.945 0.945 ↑ 1.0 1,398 1

Seq Scan on service s2 (cost=0.00..28.98 rows=1,398 width=12) (actual time=0.019..0.945 rows=1,398 loops=1)

47. 188.242 488.427 ↓ 54,226.2 216,905 1

Hash (cost=138.87..138.87 rows=4 width=20) (actual time=488.426..488.427 rows=216,905 loops=1)

  • Buckets: 262144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 13063kB
48. 158.514 300.185 ↓ 54,226.2 216,905 1

Hash Join (cost=104.61..138.87 rows=4 width=20) (actual time=140.702..300.185 rows=216,905 loops=1)

  • Hash Cond: (s0.id_teacher = t.id)
49. 1.000 1.000 ↑ 1.0 1,398 1

Seq Scan on service s0 (cost=0.00..28.98 rows=1,398 width=4) (actual time=0.007..1.000 rows=1,398 loops=1)

50. 33.495 140.671 ↓ 42,065.0 42,065 1

Hash (cost=104.60..104.60 rows=1 width=24) (actual time=140.670..140.671 rows=42,065 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2813kB
51. 61.937 107.176 ↓ 42,065.0 42,065 1

Hash Semi Join (cost=60.15..104.60 rows=1 width=24) (actual time=7.001..107.176 rows=42,065 loops=1)

  • Hash Cond: (s1.id_status = status.id)
52. 37.671 45.215 ↓ 50,290.0 50,290 1

Hash Join (cost=59.09..103.52 rows=1 width=28) (actual time=6.960..45.215 rows=50,290 loops=1)

  • Hash Cond: ((ay.year - 1) = s1.year)
53. 0.427 2.762 ↓ 235.0 235 1

Hash Join (cost=14.86..59.28 rows=1 width=12) (actual time=2.160..2.762 rows=235 loops=1)

  • Hash Cond: (ay.id_activity = a.id)
54. 0.534 0.534 ↓ 1.0 333 1

Seq Scan on activity_year ay (cost=0.00..43.20 rows=322 width=8) (actual time=0.282..0.534 rows=333 loops=1)

  • Filter: (year = COALESCE($1))
  • Rows Removed by Filter: 1923
55. 0.404 1.801 ↓ 521.0 521 1

Hash (cost=14.85..14.85 rows=1 width=4) (actual time=1.801..1.801 rows=521 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
56. 0.861 1.397 ↓ 521.0 521 1

Hash Join (cost=2.17..14.85 rows=1 width=4) (actual time=0.063..1.397 rows=521 loops=1)

  • Hash Cond: (a.id_activity_name = an.id)
57. 0.505 0.505 ↓ 230.7 692 1

Seq Scan on activity a (cost=0.00..12.65 rows=3 width=8) (actual time=0.012..0.505 rows=692 loops=1)

  • Filter: (id = COALESCE(id))
58. 0.008 0.031 ↑ 1.0 4 1

Hash (cost=2.12..2.12 rows=4 width=4) (actual time=0.031..0.031 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
59. 0.023 0.023 ↑ 1.0 4 1

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

  • Filter: ((shortlabel)::text = ANY ('{COURS,TD,TP,COURS/TD}'::text[]))
  • Rows Removed by Filter: 71
60. 1.169 4.782 ↓ 199.7 1,398 1

Hash (cost=44.14..44.14 rows=7 width=20) (actual time=4.781..4.782 rows=1,398 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 87kB
61. 2.059 3.613 ↓ 199.7 1,398 1

Hash Join (cost=9.85..44.14 rows=7 width=20) (actual time=0.643..3.613 rows=1,398 loops=1)

  • Hash Cond: (s1.id_teacher = t.id)
62. 0.934 0.934 ↑ 1.0 1,398 1

Seq Scan on service s1 (cost=0.00..28.98 rows=1,398 width=16) (actual time=0.005..0.934 rows=1,398 loops=1)

63. 0.311 0.620 ↓ 193.0 386 1

Hash (cost=9.82..9.82 rows=2 width=4) (actual time=0.619..0.620 rows=386 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
64. 0.309 0.309 ↓ 193.0 386 1

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

  • Filter: (id = COALESCE(id))
65. 0.007 0.024 ↑ 1.0 2 1

Hash (cost=1.04..1.04 rows=2 width=4) (actual time=0.023..0.024 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
66. 0.017 0.017 ↑ 1.0 2 1

Seq Scan on status (cost=0.00..1.04 rows=2 width=4) (actual time=0.013..0.017 rows=2 loops=1)

  • Filter: ((code)::text = ANY ('{PERM,NPERM}'::text[]))
  • Rows Removed by Filter: 1
67. 0.156 36.312 ↓ 48.0 192 1

Hash (cost=0.08..0.08 rows=4 width=8) (actual time=36.311..36.312 rows=192 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
68. 36.156 36.156 ↓ 48.0 192 1

CTE Scan on prio_linked_act pla1 (cost=0.00..0.08 rows=4 width=8) (actual time=33.778..36.156 rows=192 loops=1)

69. 0.139 0.272 ↓ 48.0 192 1

Hash (cost=0.08..0.08 rows=4 width=8) (actual time=0.272..0.272 rows=192 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
70. 0.133 0.133 ↓ 48.0 192 1

CTE Scan on prio_linked_act pla2 (cost=0.00..0.08 rows=4 width=8) (actual time=0.001..0.133 rows=192 loops=1)

71. 361.036 361.036 ↓ 48.0 192 2,756

CTE Scan on prio_linked_act pla3 (cost=0.00..0.08 rows=4 width=8) (actual time=0.001..0.131 rows=192 loops=2,756)