explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vZG

Settings
# exclusive inclusive rows x rows loops node
1. 0.414 11,477.594 ↓ 308.0 308 1

Unique (cost=547.87..547.89 rows=1 width=28) (actual time=11,476.968..11,477.594 rows=308 loops=1)

2.          

CTE prio_linked_act

3. 1.088 36.014 ↓ 48.0 192 1

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

4. 2.178 34.926 ↓ 365.8 1,463 1

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

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

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

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

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

7. 2.803 30.679 ↓ 11.5 3,496 1

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

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

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

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

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

10. 2.681 22.416 ↓ 11.5 3,496 1

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

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

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

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

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

13. 0.718 14.462 ↓ 19.0 968 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
14. 1.787 13.744 ↓ 19.0 968 1

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

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

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

  • Hash Cond: (ap2.id_activity_year = ay2.id)
16. 1.377 1.377 ↑ 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.377 rows=2,092 loops=1)

17. 1.151 7.732 ↓ 12.9 1,556 1

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

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

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

19. 0.859 1.358 ↓ 14.1 521 1

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

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

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

21. 0.009 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
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.091 0.183 ↑ 1.0 116 1

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

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

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

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

Initplan (forUnique)

27. 0.015 0.015 ↑ 1.0 1 1

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

  • Index Cond: ((key)::text = 'current_year'::text)
28. 0.579 11,477.165 ↓ 308.0 308 1

Sort (cost=323.49..323.49 rows=1 width=28) (actual time=11,476.964..11,477.165 rows=308 loops=1)

  • Sort Key: t.id, ay.id_activity, si1.id_activity, s1.id, si1.id, si2.id, si3.id
  • Sort Method: quicksort Memory: 49kB
29. 3.498 11,476.586 ↓ 308.0 308 1

Hash Right Join (cost=235.54..323.48 rows=1 width=28) (actual time=11,476.364..11,476.586 rows=308 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: 1680
  • Filter: ((si1.id IS NULL) OR (si2.id IS NULL) OR (si3.id IS NULL))
  • Rows Removed by Filter: 249
30. 2.570 2.570 ↓ 1.0 4,069 1

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

31. 0.411 11,470.518 ↓ 476.0 476 1

Hash (cost=235.53..235.53 rows=1 width=36) (actual time=11,470.517..11,470.518 rows=476 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
32. 63.216 11,470.107 ↓ 476.0 476 1

Nested Loop Left Join (cost=147.45..235.53 rows=1 width=36) (actual time=11,341.831..11,470.107 rows=476 loops=1)

  • Join Filter: ((pla3.act_id = si1.id_activity) OR (pla3.act_id = si2.id_activity))
  • Rows Removed by Join Filter: 91340
33. 3.610 11,344.535 ↓ 476.0 476 1

Hash Right Join (cost=147.45..235.39 rows=1 width=32) (actual time=11,341.559..11,344.535 rows=476 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: 1678
34. 2.641 2.641 ↓ 1.0 4,069 1

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

35. 0.424 11,338.284 ↓ 441.0 441 1

Hash (cost=147.44..147.44 rows=1 width=32) (actual time=11,338.283..11,338.284 rows=441 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
36. 56.011 11,337.860 ↓ 441.0 441 1

Nested Loop Left Join (cost=51.34..147.44 rows=1 width=32) (actual time=11,190.295..11,337.860 rows=441 loops=1)

  • Join Filter: (pla2.act_id = si1.id_activity)
  • Rows Removed by Join Filter: 84620
37. 1.215 11,224.078 ↓ 441.0 441 1

Nested Loop Left Join (cost=51.34..147.31 rows=1 width=28) (actual time=11,190.034..11,224.078 rows=441 loops=1)

38. 34.000 11,221.540 ↓ 441.0 441 1

Hash Join (cost=51.06..139.00 rows=1 width=28) (actual time=11,190.017..11,221.540 rows=441 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: 176289
39. 2.656 2.656 ↓ 1.0 4,069 1

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

40. 41.395 11,184.884 ↓ 46,032.0 46,032 1

Hash (cost=51.04..51.04 rows=1 width=28) (actual time=11,184.883..11,184.884 rows=46,032 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3113kB
41. 5,010.943 11,143.489 ↓ 46,032.0 46,032 1

Nested Loop Left Join (cost=1.54..51.04 rows=1 width=28) (actual time=36.585..11,143.489 rows=46,032 loops=1)

  • Join Filter: (pla1.act_id = a.id)
  • Rows Removed by Join Filter: 7552104
42. 89.317 960.666 ↓ 39,480.0 39,480 1

Nested Loop Left Join (cost=1.54..50.91 rows=1 width=24) (actual time=0.174..960.666 rows=39,480 loops=1)

43. 93.179 752.909 ↓ 39,480.0 39,480 1

Nested Loop (cost=1.26..42.60 rows=1 width=20) (actual time=0.162..752.909 rows=39,480 loops=1)

44. 99.126 541.290 ↓ 39,480.0 39,480 1

Nested Loop (cost=0.98..42.28 rows=1 width=24) (actual time=0.147..541.290 rows=39,480 loops=1)

45. 81.986 315.969 ↓ 42,065.0 42,065 1

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

46. 68.501 133.403 ↓ 7,184.3 50,290 1

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

47. 1.099 7.797 ↓ 235.0 235 1

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

48. 2.229 5.135 ↓ 521.0 521 1

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

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

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

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

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

51. 0.653 0.653 ↓ 230.7 692 1

Seq Scan on activity a (cost=0.00..14.65 rows=3 width=8) (actual time=0.015..0.653 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.003..0.003 rows=0 loops=521)

  • Index Cond: ((id_activity = a.id) AND (year = COALESCE($2)))
  • Heap Fetches: 235
53. 57.105 57.105 ↓ 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.243 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 service_id_teacher_year_key on service s0 (cost=0.28..0.36 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=42,065)

  • Index Cond: ((id_teacher = s1.id_teacher) AND (year = COALESCE($2)))
  • Heap Fetches: 39480
56. 118.440 118.440 ↑ 1.0 1 39,480

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=39,480)

  • Index Cond: (id = s1.id_teacher)
  • Filter: (id = COALESCE(id))
  • Heap Fetches: 39480
57. 118.440 118.440 ↑ 1.0 1 39,480

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=39,480)

  • Index Cond: ((id_teacher = t.id) AND (year = (ay.year - 2)))
58. 5,171.880 5,171.880 ↓ 48.0 192 39,480

CTE Scan on prio_linked_act pla1 (cost=0.00..0.08 rows=4 width=8) (actual time=0.002..0.131 rows=192 loops=39,480)

59. 1.323 1.323 ↑ 1.0 1 441

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=441)

  • Index Cond: ((id_teacher = t.id) AND (year = (ay.year - 3)))
60. 57.771 57.771 ↓ 48.0 192 441

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

61. 62.356 62.356 ↓ 48.0 192 476

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=476)

Planning time : 24.261 ms
Execution time : 11,478.483 ms