explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gu6d

Settings
# exclusive inclusive rows x rows loops node
1. 1.463 4,395.092 ↓ 327.0 327 1

Unique (cost=548.13..548.15 rows=1 width=28) (actual time=4,392.508..4,395.092 rows=327 loops=1)

2.          

CTE prio_linked_act

3. 1.095 36.120 ↓ 48.0 192 1

Unique (cost=216.18..216.21 rows=4 width=8) (actual time=34.048..36.120 rows=192 loops=1)

4. 2.170 35.025 ↓ 365.8 1,463 1

Sort (cost=216.18..216.19 rows=4 width=8) (actual time=34.047..35.025 rows=1,463 loops=1)

  • Sort Key: a1.id, a2.id
  • Sort Method: quicksort Memory: 117kB
5. 1.625 32.855 ↓ 365.8 1,463 1

Hash Join (cost=197.99..216.14 rows=4 width=8) (actual time=30.832..32.855 rows=1,463 loops=1)

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

Seq Scan on activity a1 (cost=0.00..12.92 rows=692 width=8) (actual time=0.016..0.453 rows=692 loops=1)

7. 2.784 30.777 ↓ 11.5 3,496 1

Hash (cost=193.42..193.42 rows=305 width=16) (actual time=30.776..30.777 rows=3,496 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 196kB
8. 4.045 27.993 ↓ 11.5 3,496 1

Hash Join (cost=144.35..193.42 rows=305 width=16) (actual time=22.641..27.993 rows=3,496 loops=1)

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

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

10. 2.696 22.518 ↓ 11.5 3,496 1

Hash (cost=140.53..140.53 rows=305 width=16) (actual time=22.517..22.518 rows=3,496 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 196kB
11. 3.966 19.822 ↓ 11.5 3,496 1

Hash Join (cost=91.72..140.53 rows=305 width=16) (actual time=14.992..19.822 rows=3,496 loops=1)

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

Seq Scan on activity_parent ap1 (cost=0.00..37.92 rows=2,092 width=8) (actual time=0.008..1.335 rows=2,092 loops=1)

13. 0.738 14.521 ↓ 19.0 968 1

Hash (cost=91.08..91.08 rows=51 width=16) (actual time=14.521..14.521 rows=968 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
14. 1.770 13.783 ↓ 19.0 968 1

Hash Join (cost=42.29..91.08 rows=51 width=16) (actual time=8.616..13.783 rows=968 loops=1)

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

Hash Join (cost=38.55..85.43 rows=112 width=16) (actual time=7.761..11.824 rows=1,644 loops=1)

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

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

17. 1.222 7.723 ↓ 12.9 1,556 1

Hash (cost=37.03..37.03 rows=121 width=16) (actual time=7.722..7.723 rows=1,556 loops=1)

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

Nested Loop (cost=2.46..37.03 rows=121 width=16) (actual time=0.089..6.501 rows=1,556 loops=1)

19. 0.878 1.379 ↓ 14.1 521 1

Hash Join (cost=2.17..18.06 rows=37 width=12) (actual time=0.076..1.379 rows=521 loops=1)

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

Seq Scan on activity a2 (cost=0.00..12.92 rows=692 width=8) (actual time=0.006..0.461 rows=692 loops=1)

21. 0.008 0.040 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.032 0.032 ↑ 1.0 4 1

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

  • Filter: ((shortlabel)::text = ANY ('{COURS,TD,TP,COURS/TD}'::text[]))
  • Rows Removed by Filter: 71
23. 2.605 2.605 ↑ 1.0 3 521

Index Scan using activity_year_id_activity_year_key on activity_year ay2 (cost=0.28..0.48 rows=3 width=8) (actual time=0.002..0.005 rows=3 loops=521)

  • Index Cond: (id_activity = a2.id)
24. 0.092 0.189 ↑ 1.0 116 1

Hash (cost=2.24..2.24 rows=120 width=8) (actual time=0.188..0.189 rows=116 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
25. 0.097 0.097 ↑ 1.0 116 1

Seq Scan on ue_ancestor ua (cost=0.00..2.24 rows=120 width=8) (actual time=0.009..0.097 rows=116 loops=1)

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

Initplan (forUnique)

27. 0.012 0.012 ↑ 1.0 1 1

Index Scan using params_key_key on params (cost=0.14..8.17 rows=1 width=4) (actual time=0.010..0.012 rows=1 loops=1)

  • Index Cond: ((key)::text = 'current_year'::text)
28. 3.048 4,393.617 ↓ 1,670.0 1,670 1

Sort (cost=323.75..323.76 rows=1 width=28) (actual time=4,392.506..4,393.617 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
29. 6.334 4,390.569 ↓ 1,670.0 1,670 1

Hash Right Join (cost=235.80..323.74 rows=1 width=28) (actual time=4,389.300..4,390.569 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
30. 2.619 2.619 ↓ 1.0 4,069 1

Seq Scan on service_item si3 (cost=0.00..72.65 rows=4,065 width=12) (actual time=0.020..2.619 rows=4,069 loops=1)

31. 2.460 4,381.616 ↓ 2,756.0 2,756 1

Hash (cost=235.79..235.79 rows=1 width=36) (actual time=4,381.616..4,381.616 rows=2,756 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 218kB
32. 368.164 4,379.156 ↓ 2,756.0 2,756 1

Nested Loop Left Join (cost=147.71..235.79 rows=1 width=36) (actual time=3,643.609..4,379.156 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
33. 7.177 3,649.956 ↓ 2,756.0 2,756 1

Hash Right Join (cost=147.71..235.65 rows=1 width=32) (actual time=3,643.337..3,649.956 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
34. 2.715 2.715 ↓ 1.0 4,069 1

Seq Scan on service_item si2 (cost=0.00..72.65 rows=4,065 width=12) (actual time=0.016..2.715 rows=4,069 loops=1)

35. 2.472 3,640.064 ↓ 2,541.0 2,541 1

Hash (cost=147.70..147.70 rows=1 width=32) (actual time=3,640.064..3,640.064 rows=2,541 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 181kB
36. 321.674 3,637.592 ↓ 2,541.0 2,541 1

Nested Loop Left Join (cost=51.46..147.70 rows=1 width=32) (actual time=1,657.894..3,637.592 rows=2,541 loops=1)

  • Join Filter: (pla2.act_id = si1.id_activity)
  • Rows Removed by Join Filter: 487617
37. 6.473 2,985.588 ↓ 2,541.0 2,541 1

Nested Loop Left Join (cost=51.46..147.57 rows=1 width=28) (actual time=1,657.627..2,985.588 rows=2,541 loops=1)

38. 718.335 2,971.492 ↓ 2,541.0 2,541 1

Hash Left Join (cost=51.18..139.26 rows=1 width=28) (actual time=1,657.607..2,971.492 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: 1030165
39. 656.368 2,216.612 ↓ 73,809.6 885,715 1

Hash Join (cost=51.05..139.07 rows=12 width=32) (actual time=1,561.623..2,216.612 rows=885,715 loops=1)

  • Hash Cond: (si1.id_service = s1.id)
40. 2.875 2.875 ↓ 1.0 4,069 1

Seq Scan on service_item si1 (cost=0.00..72.65 rows=4,065 width=12) (actual time=0.010..2.875 rows=4,069 loops=1)

41. 179.878 1,557.369 ↓ 54,226.2 216,905 1

Hash (cost=51.00..51.00 rows=4 width=24) (actual time=1,557.368..1,557.369 rows=216,905 loops=1)

  • Buckets: 262144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 13859kB
42. 331.647 1,377.491 ↓ 54,226.2 216,905 1

Nested Loop (cost=1.54..51.00 rows=4 width=24) (actual time=0.157..1,377.491 rows=216,905 loops=1)

  • Join Filter: (t.id = s0.id_teacher)
43. 91.253 751.389 ↓ 42,065.0 42,065 1

Nested Loop Left Join (cost=1.26..50.54 rows=1 width=28) (actual time=0.142..751.389 rows=42,065 loops=1)

44. 98.850 533.941 ↓ 42,065.0 42,065 1

Nested Loop (cost=0.98..42.23 rows=1 width=24) (actual time=0.133..533.941 rows=42,065 loops=1)

45. 78.533 308.896 ↓ 42,065.0 42,065 1

Nested Loop Semi Join (cost=0.71..41.91 rows=1 width=20) (actual time=0.115..308.896 rows=42,065 loops=1)

46. 67.745 129.783 ↓ 7,184.3 50,290 1

Nested Loop (cost=0.56..40.46 rows=7 width=24) (actual time=0.095..129.783 rows=50,290 loops=1)

47. 0.935 7.518 ↓ 235.0 235 1

Nested Loop (cost=0.28..25.27 rows=1 width=12) (actual time=0.080..7.518 rows=235 loops=1)

48. 2.179 5.020 ↓ 521.0 521 1

Nested Loop (cost=0.00..16.96 rows=1 width=4) (actual time=0.042..5.020 rows=521 loops=1)

  • Join Filter: (a.id_activity_name = an.id)
  • Rows Removed by Join Filter: 2247
49. 0.033 0.033 ↑ 1.0 4 1

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

  • Filter: ((shortlabel)::text = ANY ('{COURS,TD,TP,COURS/TD}'::text[]))
  • Rows Removed by Filter: 71
50. 2.232 2.808 ↓ 230.7 692 4

Materialize (cost=0.00..14.67 rows=3 width=8) (actual time=0.005..0.702 rows=692 loops=4)

51. 0.576 0.576 ↓ 230.7 692 1

Seq Scan on activity a (cost=0.00..14.65 rows=3 width=8) (actual time=0.011..0.576 rows=692 loops=1)

  • Filter: (id = COALESCE(id))
52. 1.563 1.563 ↓ 0.0 0 521

Index Only Scan using activity_year_id_activity_year_key on activity_year ay (cost=0.28..8.30 rows=1 width=8) (actual time=0.002..0.003 rows=0 loops=521)

  • Index Cond: ((id_activity = a.id) AND (year = COALESCE($2)))
  • Heap Fetches: 235
53. 54.520 54.520 ↓ 1.1 214 235

Index Scan using service_id_teacher_year_key on service s1 (cost=0.28..13.17 rows=202 width=16) (actual time=0.006..0.232 rows=214 loops=235)

  • Index Cond: (year = (ay.year - 1))
54. 100.580 100.580 ↑ 1.0 1 50,290

Index Scan using status_pkey on status (cost=0.14..0.20 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=50,290)

  • Index Cond: (id = s1.id_status)
  • Filter: ((code)::text = ANY ('{PERM,NPERM}'::text[]))
  • Rows Removed by Filter: 0
55. 126.195 126.195 ↑ 1.0 1 42,065

Index Only Scan using teacher_pkey on teacher t (cost=0.27..0.32 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=42,065)

  • Index Cond: (id = s1.id_teacher)
  • Filter: (id = COALESCE(id))
  • Heap Fetches: 42065
56. 126.195 126.195 ↑ 1.0 1 42,065

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

  • Index Cond: ((id_teacher = t.id) AND (year = (ay.year - 2)))
57. 294.455 294.455 ↓ 1.2 5 42,065

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

  • Index Cond: (id_teacher = s1.id_teacher)
  • Heap Fetches: 216905
58. 0.156 36.545 ↓ 48.0 192 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
59. 36.389 36.389 ↓ 48.0 192 1

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

60. 7.623 7.623 ↑ 1.0 1 2,541

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

  • Index Cond: ((id_teacher = t.id) AND (year = (ay.year - 3)))
61. 330.330 330.330 ↓ 48.0 192 2,541

CTE Scan on prio_linked_act pla2 (cost=0.00..0.08 rows=4 width=8) (actual time=0.001..0.130 rows=192 loops=2,541)

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