explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GrLkp

Settings
# exclusive inclusive rows x rows loops node
1. 1.483 4,936.927 ↓ 323.0 323 1

Unique (cost=534.01..534.03 rows=1 width=28) (actual time=4,934.344..4,936.927 rows=323 loops=1)

2.          

CTE prio_linked_act

3. 0.996 34.306 ↓ 55.7 167 1

Unique (cost=206.34..206.37 rows=3 width=12) (actual time=32.448..34.306 rows=167 loops=1)

4. 2.013 33.310 ↓ 436.0 1,308 1

Sort (cost=206.34..206.34 rows=3 width=12) (actual time=32.446..33.310 rows=1,308 loops=1)

  • Sort Key: a1.id, a2.id, ua.substitution_year
  • Sort Method: quicksort Memory: 110kB
5. 1.487 31.297 ↓ 436.0 1,308 1

Hash Join (cost=190.17..206.31 rows=3 width=12) (actual time=29.627..31.297 rows=1,308 loops=1)

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

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

7. 2.563 29.365 ↓ 12.6 3,055 1

Hash (cost=186.53..186.53 rows=243 width=20) (actual time=29.364..29.365 rows=3,055 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 188kB
8. 3.689 26.802 ↓ 12.6 3,055 1

Hash Join (cost=138.08..186.53 rows=243 width=20) (actual time=21.817..26.802 rows=3,055 loops=1)

  • Hash Cond: (ay1.id = ap1.id_activity_year)
9. 1.419 1.419 ↑ 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.419 rows=2,256 loops=1)

10. 2.490 21.694 ↓ 12.6 3,055 1

Hash (cost=135.04..135.04 rows=243 width=20) (actual time=21.693..21.694 rows=3,055 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 188kB
11. 3.563 19.204 ↓ 12.6 3,055 1

Hash Join (cost=87.85..135.04 rows=243 width=20) (actual time=15.326..19.204 rows=3,055 loops=1)

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

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

13. 0.722 14.343 ↓ 22.1 905 1

Hash (cost=87.33..87.33 rows=41 width=20) (actual time=14.343..14.343 rows=905 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
14. 1.745 13.621 ↓ 22.1 905 1

Hash Join (cost=39.92..87.33 rows=41 width=20) (actual time=8.530..13.621 rows=905 loops=1)

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

Hash Join (cost=36.12..82.00 rows=112 width=16) (actual time=7.686..11.694 rows=1,644 loops=1)

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

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

17. 1.226 7.656 ↓ 12.9 1,556 1

Hash (cost=34.61..34.61 rows=121 width=16) (actual time=7.655..7.656 rows=1,556 loops=1)

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

Nested Loop (cost=2.46..34.61 rows=121 width=16) (actual time=0.063..6.430 rows=1,556 loops=1)

19. 0.853 1.343 ↓ 14.1 521 1

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

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

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

21. 0.010 0.036 ↑ 1.0 4 1

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

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

Seq Scan on activity_name an1 (cost=0.00..2.12 rows=4 width=4) (actual time=0.011..0.026 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.47 rows=3 width=8) (actual time=0.002..0.005 rows=3 loops=521)

  • Index Cond: (id_activity = a2.id)
24. 0.075 0.182 ↓ 1.0 102 1

Hash (cost=2.54..2.54 rows=101 width=12) (actual time=0.181..0.182 rows=102 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
25. 0.107 0.107 ↓ 1.0 102 1

Seq Scan on ue_ancestor ua (cost=0.00..2.54 rows=101 width=12) (actual time=0.016..0.107 rows=102 loops=1)

  • Filter: ((priority_disable IS FALSE) AND (substitution_year >= 2016))
  • Rows Removed by Filter: 21
26.          

Initplan (forUnique)

27. 0.011 0.011 ↑ 1.0 1 1

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

  • Filter: ((key)::text = 'current_year'::text)
  • Rows Removed by Filter: 3
28. 3.029 4,935.433 ↓ 1,665.0 1,665 1

Sort (cost=326.59..326.59 rows=1 width=28) (actual time=4,934.342..4,935.433 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
29. 6.078 4,932.404 ↓ 1,665.0 1,665 1

Hash Right Join (cost=238.89..326.58 rows=1 width=28) (actual time=4,931.158..4,932.404 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
30. 2.772 2.772 ↑ 1.0 3,990 1

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

  • Filter: (NOT priority_canceled)
  • Rows Removed by Filter: 78
31. 2.402 4,923.554 ↓ 2,739.0 2,739 1

Hash (cost=238.88..238.88 rows=1 width=36) (actual time=4,923.553..4,923.554 rows=2,739 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 217kB
32. 319.896 4,921.152 ↓ 2,739.0 2,739 1

Nested Loop Left Join (cost=151.09..238.88 rows=1 width=36) (actual time=4,296.807..4,921.152 rows=2,739 loops=1)

  • Join Filter: ((pla3.act_id = si1.id_activity) OR (pla3.act_id = si2.id_activity))
  • Rows Removed by Join Filter: 457324
33. 6.572 4,302.705 ↓ 2,739.0 2,739 1

Hash Right Join (cost=151.09..238.77 rows=1 width=32) (actual time=4,296.575..4,302.705 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
34. 2.822 2.822 ↑ 1.0 3,990 1

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

  • Filter: (NOT priority_canceled)
  • Rows Removed by Filter: 78
35. 2.393 4,293.311 ↓ 2,524.0 2,524 1

Hash (cost=151.07..151.07 rows=1 width=32) (actual time=4,293.310..4,293.311 rows=2,524 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 179kB
36. 277.808 4,290.918 ↓ 2,524.0 2,524 1

Nested Loop Left Join (cost=55.14..151.07 rows=1 width=32) (actual time=2,442.303..4,290.918 rows=2,524 loops=1)

  • Join Filter: (pla2.act_id = si1.id_activity)
  • Rows Removed by Join Filter: 421419
37. 6.167 3,735.470 ↓ 2,524.0 2,524 1

Nested Loop Left Join (cost=55.14..150.98 rows=1 width=28) (actual time=2,442.077..3,735.470 rows=2,524 loops=1)

38. 707.318 3,721.731 ↓ 2,524.0 2,524 1

Hash Left Join (cost=54.86..142.67 rows=1 width=24) (actual time=2,442.063..3,721.731 rows=2,524 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: 1018020
39. 631.809 2,979.750 ↓ 80,156.4 881,720 1

Hash Join (cost=54.76..142.52 rows=11 width=28) (actual time=2,348.983..2,979.750 rows=881,720 loops=1)

  • Hash Cond: (si1.id_service = s1.id)
40. 2.913 2.913 ↑ 1.0 3,990 1

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

  • Filter: (NOT priority_canceled)
  • Rows Removed by Filter: 78
41. 178.002 2,345.028 ↓ 54,226.2 216,905 1

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

  • Buckets: 262144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 13012kB
42. 342.797 2,167.026 ↓ 54,226.2 216,905 1

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

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

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

44. 180.966 1,403.100 ↓ 42,065.0 42,065 1

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

45. 386.079 1,035.616 ↓ 93,259.0 93,259 1

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

  • Join Filter: (a.id_activity_name = an.id)
  • Rows Removed by Join Filter: 402213
46. 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.010..0.033 rows=4 loops=1)

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

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

48. 158.767 249.307 ↓ 61,934.0 123,868 1

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

49. 0.466 3.009 ↓ 179.0 179 1

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

50. 1.007 2.115 ↓ 214.0 214 1

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

51. 0.336 0.336 ↓ 193.0 386 1

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

  • Filter: (id = COALESCE(id))
52. 0.772 0.772 ↑ 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.002 rows=1 loops=386)

  • Index Cond: ((id_teacher = t.id) AND (year = 2018))
53. 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
54. 87.531 87.531 ↓ 230.7 692 179

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

  • Filter: (id = COALESCE(id))
55. 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($2)))
  • Heap Fetches: 0
56. 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))
57. 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
58. 0.128 34.663 ↓ 55.7 167 1

Hash (cost=0.06..0.06 rows=3 width=8) (actual time=34.662..34.663 rows=167 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
59. 34.535 34.535 ↓ 55.7 167 1

CTE Scan on prio_linked_act pla1 (cost=0.00..0.06 rows=3 width=8) (actual time=32.451..34.535 rows=167 loops=1)

60. 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))
61. 277.640 277.640 ↓ 55.7 167 2,524

CTE Scan on prio_linked_act pla2 (cost=0.00..0.06 rows=3 width=8) (actual time=0.001..0.110 rows=167 loops=2,524)

62. 298.551 298.551 ↓ 55.7 167 2,739

CTE Scan on prio_linked_act pla3 (cost=0.00..0.06 rows=3 width=8) (actual time=0.001..0.109 rows=167 loops=2,739)