explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cRGp

Settings
# exclusive inclusive rows x rows loops node
1. 0.457 12,350.017 ↓ 327.0 327 1

Unique (cost=547.50..547.52 rows=1 width=28) (actual time=12,349.315..12,350.017 rows=327 loops=1)

2.          

CTE prio_linked_act

3. 1.089 36.091 ↓ 48.0 192 1

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

4. 2.168 35.002 ↓ 365.8 1,463 1

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

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

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

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

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

7. 2.767 30.761 ↓ 11.5 3,496 1

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

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

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

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

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

10. 2.692 22.491 ↓ 11.5 3,496 1

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

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

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

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

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

13. 0.729 14.537 ↓ 19.0 968 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
14. 1.783 13.808 ↓ 19.0 968 1

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

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

Hash Join (cost=38.55..85.43 rows=112 width=16) (actual time=7.784..11.844 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.004..1.385 rows=2,092 loops=1)

17. 1.207 7.750 ↓ 12.9 1,556 1

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

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

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

19. 0.881 1.363 ↓ 14.1 521 1

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

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

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

21. 0.008 0.030 ↑ 1.0 4 1

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

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

Seq Scan on activity_name an1 (cost=0.00..2.12 rows=4 width=4) (actual time=0.008..0.022 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.094 0.181 ↑ 1.0 116 1

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

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

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

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

Initplan (forUnique)

27. 0.013 0.013 ↑ 1.0 1 1

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

  • Index Cond: ((key)::text = 'current_year'::text)
28. 0.599 12,349.547 ↓ 327.0 327 1

Sort (cost=323.12..323.12 rows=1 width=28) (actual time=12,349.313..12,349.547 rows=327 loops=1)

  • Sort Key: t.id, ay.id_activity, si1.id_activity, s1.id, si1.id, si2.id, si3.id
  • Sort Method: quicksort Memory: 50kB
29. 3.631 12,348.948 ↓ 327.0 327 1

Hash Right Join (cost=235.17..323.11 rows=1 width=28) (actual time=12,348.678..12,348.948 rows=327 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: 1698
  • Filter: ((si1.id IS NULL) OR (si2.id IS NULL) OR (si3.id IS NULL))
  • Rows Removed by Filter: 249
30. 2.763 2.763 ↓ 1.0 4,069 1

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

31. 0.452 12,342.554 ↓ 495.0 495 1

Hash (cost=235.16..235.16 rows=1 width=36) (actual time=12,342.553..12,342.554 rows=495 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
32. 69.111 12,342.102 ↓ 495.0 495 1

Nested Loop Left Join (cost=147.08..235.16 rows=1 width=36) (actual time=12,202.809..12,342.102 rows=495 loops=1)

  • Join Filter: ((pla3.act_id = si1.id_activity) OR (pla3.act_id = si2.id_activity))
  • Rows Removed by Join Filter: 94984
33. 3.783 12,205.671 ↓ 495.0 495 1

Hash Right Join (cost=147.08..235.02 rows=1 width=32) (actual time=12,202.527..12,205.671 rows=495 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: 1694
34. 2.771 2.771 ↓ 1.0 4,069 1

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

35. 0.439 12,199.117 ↓ 460.0 460 1

Hash (cost=147.07..147.07 rows=1 width=32) (actual time=12,199.116..12,199.117 rows=460 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
36. 60.824 12,198.678 ↓ 460.0 460 1

Nested Loop Left Join (cost=59.00..147.07 rows=1 width=32) (actual time=12,043.785..12,198.678 rows=460 loops=1)

  • Join Filter: (pla2.act_id = si1.id_activity)
  • Rows Removed by Join Filter: 88264
37. 34.707 12,075.754 ↓ 460.0 460 1

Hash Join (cost=59.00..146.94 rows=1 width=28) (actual time=12,043.514..12,075.754 rows=460 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: 184216
38. 2.816 2.816 ↓ 1.0 4,069 1

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

39. 43.937 12,038.231 ↓ 49,046.0 49,046 1

Hash (cost=58.98..58.98 rows=1 width=28) (actual time=12,038.231..12,038.231 rows=49,046 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3225kB
40. 5,375.377 11,994.294 ↓ 49,046.0 49,046 1

Nested Loop Left Join (cost=1.54..58.98 rows=1 width=28) (actual time=36.633..11,994.294 rows=49,046 loops=1)

  • Join Filter: (pla1.act_id = a.id)
  • Rows Removed by Join Filter: 8046587
41. 126.779 982.207 ↓ 42,065.0 42,065 1

Nested Loop Left Join (cost=1.54..58.85 rows=1 width=24) (actual time=0.150..982.207 rows=42,065 loops=1)

42. 96.120 771.298 ↓ 42,065.0 42,065 1

Nested Loop Left Join (cost=1.26..50.54 rows=1 width=24) (actual time=0.141..771.298 rows=42,065 loops=1)

43. 104.197 548.983 ↓ 42,065.0 42,065 1

Nested Loop (cost=0.98..42.23 rows=1 width=20) (actual time=0.132..548.983 rows=42,065 loops=1)

44. 83.043 318.591 ↓ 42,065.0 42,065 1

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

45. 69.005 134.968 ↓ 7,184.3 50,290 1

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

46. 1.184 7.918 ↓ 235.0 235 1

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

47. 2.227 5.171 ↓ 521.0 521 1

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

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

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

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

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

50. 0.643 0.643 ↓ 230.7 692 1

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

  • Filter: (id = COALESCE(id))
51. 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.003..0.003 rows=0 loops=521)

  • Index Cond: ((id_activity = a.id) AND (year = COALESCE($2)))
  • Heap Fetches: 235
52. 58.045 58.045 ↓ 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.007..0.247 rows=214 loops=235)

  • Index Cond: (year = (ay.year - 1))
53. 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
54. 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
55. 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)))
56. 84.130 84.130 ↑ 1.0 1 42,065

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.002 rows=1 loops=42,065)

  • Index Cond: ((id_teacher = t.id) AND (year = (ay.year - 3)))
57. 5,636.710 5,636.710 ↓ 48.0 192 42,065

CTE Scan on prio_linked_act pla1 (cost=0.00..0.08 rows=4 width=8) (actual time=0.002..0.134 rows=192 loops=42,065)

58. 62.100 62.100 ↓ 48.0 192 460

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

59. 67.320 67.320 ↓ 48.0 192 495

CTE Scan on prio_linked_act pla3 (cost=0.00..0.08 rows=4 width=8) (actual time=0.001..0.136 rows=192 loops=495)