explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MaDe

Settings
# exclusive inclusive rows x rows loops node
1. 1.488 5,136.169 ↓ 327.0 327 1

Unique (cost=537.10..537.12 rows=1 width=28) (actual time=5,133.566..5,136.169 rows=327 loops=1)

2.          

CTE prio_linked_act

3. 1.097 36.847 ↓ 48.0 192 1

Unique (cost=208.43..208.46 rows=4 width=8) (actual time=34.729..36.847 rows=192 loops=1)

4. 2.211 35.750 ↓ 373.5 1,494 1

Sort (cost=208.43..208.44 rows=4 width=8) (actual time=34.726..35.750 rows=1,494 loops=1)

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

Hash Join (cost=192.24..208.39 rows=4 width=8) (actual time=31.484..33.539 rows=1,494 loops=1)

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

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

7. 2.903 31.432 ↓ 12.6 3,584 1

Hash (cost=187.97..187.97 rows=285 width=16) (actual time=31.431..31.432 rows=3,584 loops=1)

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

Hash Join (cost=139.10..187.97 rows=285 width=16) (actual time=22.896..28.529 rows=3,584 loops=1)

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

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

10. 2.802 22.766 ↓ 12.6 3,584 1

Hash (cost=135.54..135.54 rows=285 width=16) (actual time=22.765..22.766 rows=3,584 loops=1)

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

Hash Join (cost=87.92..135.54 rows=285 width=16) (actual time=15.099..19.964 rows=3,584 loops=1)

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

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

13. 0.741 14.641 ↓ 20.3 973 1

Hash (cost=87.32..87.32 rows=48 width=16) (actual time=14.640..14.641 rows=973 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
14. 1.816 13.900 ↓ 20.3 973 1

Hash Join (cost=39.84..87.32 rows=48 width=16) (actual time=8.694..13.900 rows=973 loops=1)

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

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

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

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

17. 1.230 7.795 ↓ 12.9 1,556 1

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

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

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

19. 0.869 1.390 ↓ 14.1 521 1

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

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

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

21. 0.008 0.035 ↑ 1.0 4 1

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

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

Seq Scan on activity_name an1 (cost=0.00..2.12 rows=4 width=4) (actual time=0.013..0.027 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.097 0.190 ↑ 1.0 119 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
25. 0.093 0.093 ↑ 1.0 119 1

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

  • Filter: (priority_disable IS FALSE)
  • Rows Removed by Filter: 4
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.078 5,134.670 ↓ 1,670.0 1,670 1

Sort (cost=327.58..327.59 rows=1 width=28) (actual time=5,133.562..5,134.670 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.444 5,131.592 ↓ 1,670.0 1,670 1

Hash Right Join (cost=239.59..327.57 rows=1 width=28) (actual time=5,130.313..5,131.592 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.662 2.662 ↑ 1.0 4,068 1

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

31. 2.374 5,122.486 ↓ 2,756.0 2,756 1

Hash (cost=239.58..239.58 rows=1 width=36) (actual time=5,122.485..5,122.486 rows=2,756 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 218kB
32. 364.060 5,120.112 ↓ 2,756.0 2,756 1

Nested Loop Left Join (cost=151.46..239.58 rows=1 width=36) (actual time=4,394.405..5,120.112 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.052 4,400.528 ↓ 2,756.0 2,756 1

Hash Right Join (cost=151.46..239.44 rows=1 width=32) (actual time=4,394.138..4,400.528 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.564 2.564 ↑ 1.0 4,068 1

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

35. 2.366 4,390.912 ↓ 2,541.0 2,541 1

Hash (cost=151.45..151.45 rows=1 width=32) (actual time=4,390.912..4,390.912 rows=2,541 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 181kB
36. 317.816 4,388.546 ↓ 2,541.0 2,541 1

Nested Loop Left Join (cost=55.17..151.45 rows=1 width=32) (actual time=2,442.706..4,388.546 rows=2,541 loops=1)

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

Nested Loop Left Join (cost=55.17..151.32 rows=1 width=28) (actual time=2,442.445..3,740.400 rows=2,541 loops=1)

38. 707.676 3,726.672 ↓ 2,541.0 2,541 1

Hash Left Join (cost=54.89..143.01 rows=1 width=24) (actual time=2,442.429..3,726.672 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. 636.316 2,981.732 ↓ 73,711.7 884,540 1

Hash Join (cost=54.76..142.82 rows=12 width=28) (actual time=2,346.784..2,981.732 rows=884,540 loops=1)

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

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

41. 178.331 2,342.787 ↓ 54,226.2 216,905 1

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

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

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

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

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

44. 181.565 1,400.795 ↓ 42,065.0 42,065 1

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

45. 383.335 1,032.712 ↓ 93,259.0 93,259 1

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

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

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

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

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

48. 158.611 248.776 ↓ 61,934.0 123,868 1

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

49. 0.460 2.992 ↓ 179.0 179 1

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

50. 1.016 2.104 ↓ 214.0 214 1

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

51. 0.316 0.316 ↓ 193.0 386 1

Seq Scan on teacher t (cost=0.00..9.82 rows=2 width=4) (actual time=0.010..0.316 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.173 87.173 ↓ 230.7 692 179

Seq Scan on activity a (cost=0.00..12.65 rows=3 width=8) (actual time=0.002..0.487 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.149 37.264 ↓ 48.0 192 1

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

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

CTE Scan on prio_linked_act pla1 (cost=0.00..0.08 rows=4 width=8) (actual time=34.731..37.115 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=8) (actual time=0.002..0.003 rows=1 loops=2,541)

  • Index Cond: ((id_teacher = t.id) AND (year = 2016))
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. 355.524 355.524 ↓ 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.129 rows=192 loops=2,756)