explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l5TP

Settings
# exclusive inclusive rows x rows loops node
1. 0.037 40,482.613 ↑ 1.0 30 1

Limit (cost=344,098.62..344,218.00 rows=30 width=4) (actual time=40,481.113..40,482.613 rows=30 loops=1)

2.          

CTE permitted_organizations

3. 1.614 67.921 ↓ 10.1 1,010 1

Hash Join (cost=3.02..79.02 rows=100 width=12) (actual time=65.577..67.921 rows=1,010 loops=1)

  • Hash Cond: (t_37.id = (unnest(d.d)))
4. 0.762 0.762 ↑ 1.0 1,091 1

Seq Scan on m_orgreestr t_37 (cost=0.00..70.91 rows=1,091 width=12) (actual time=0.014..0.762 rows=1,091 loops=1)

5. 0.717 65.545 ↓ 10.1 1,010 1

Hash (cost=1.77..1.77 rows=100 width=4) (actual time=65.545..65.545 rows=1,010 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
6. 64.828 64.828 ↓ 10.1 1,010 1

Function Scan on get_orgreestr_ids_by_permision_and_filter d (cost=0.26..0.77 rows=100 width=4) (actual time=64.159..64.828 rows=1,010 loops=1)

7. 0.495 40,482.576 ↑ 338.3 30 1

Nested Loop Semi Join (cost=344,019.60..384,410.30 rows=10,150 width=4) (actual time=40,481.111..40,482.576 rows=30 loops=1)

  • Join Filter: ((p.cid)::numeric = t_1.catalog)
  • Rows Removed by Join Filter: 469
8. 0.126 40,481.511 ↑ 676.7 30 1

Nested Loop (cost=344,019.60..382,273.25 rows=20,300 width=12) (actual time=40,480.843..40,481.511 rows=30 loops=1)

9. 0.365 40,481.115 ↑ 676.7 30 1

Hash Join (cost=344,019.18..360,385.90 rows=20,300 width=12) (actual time=40,480.798..40,481.115 rows=30 loops=1)

  • Hash Cond: (t.id = edu_data.person_reestr_id)
10. 0.219 0.219 ↑ 2,105.4 205 1

Seq Scan on m_childreestr t (cost=0.00..14,545.16 rows=431,616 width=12) (actual time=0.055..0.219 rows=205 loops=1)

11. 221.562 40,480.531 ↓ 12.4 251,043 1

Hash (cost=343,765.43..343,765.43 rows=20,300 width=4) (actual time=40,480.531..40,480.531 rows=251,043 loops=1)

  • Buckets: 262144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 10874kB
12. 348.644 40,258.969 ↓ 12.4 251,043 1

Subquery Scan on edu_data (cost=343,460.93..343,765.43 rows=20,300 width=4) (actual time=39,315.077..40,258.969 rows=251,043 loops=1)

13. 374.674 39,910.325 ↓ 12.4 251,043 1

Unique (cost=343,460.93..343,562.43 rows=20,300 width=20) (actual time=39,315.073..39,910.325 rows=251,043 loops=1)

14. 535.847 39,535.651 ↓ 14.5 293,854 1

Sort (cost=343,460.93..343,511.68 rows=20,300 width=20) (actual time=39,315.070..39,535.651 rows=293,854 loops=1)

  • Sort Key: cr.id, (NULL::date) DESC NULLS LAST, (NULL::integer) DESC
  • Sort Method: quicksort Memory: 26063kB
15. 545.565 38,999.804 ↓ 14.5 293,854 1

Hash Join (cost=15,893.96..342,008.54 rows=20,300 width=20) (actual time=19,624.545..38,999.804 rows=293,854 loops=1)

  • Hash Cond: (((NULL::integer)::bigint) = cr.id)
16. 361.824 37,270.962 ↓ 3.6 293,960 1

Append (cost=0.00..324,795.10 rows=81,199 width=24) (actual time=0.006..37,270.962 rows=293,960 loops=1)

17. 0.008 0.010 ↑ 1.0 1 1

Subquery Scan on *SELECT* 1 (cost=0.00..0.03 rows=1 width=24) (actual time=0.005..0.010 rows=1 loops=1)

18. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=1)

19. 92.849 18,590.349 ↓ 1.0 72,018 1

Unique (cost=62,725.71..63,083.10 rows=71,478 width=24) (actual time=18,440.772..18,590.349 rows=72,018 loops=1)

20. 202.666 18,497.500 ↓ 1.0 72,134 1

Sort (cost=62,725.71..62,904.41 rows=71,478 width=24) (actual time=18,440.769..18,497.500 rows=72,134 loops=1)

  • Sort Key: p_1.f_link2reestr, t_8.priority DESC, t_4.f_enroll_date DESC
  • Sort Method: quicksort Memory: 8708kB
21. 240.829 18,294.834 ↓ 1.0 72,134 1

Nested Loop (cost=13.78..56,962.72 rows=71,478 width=24) (actual time=85.206..18,294.834 rows=72,134 loops=1)

22. 434.384 17,404.799 ↓ 12.8 72,134 1

Nested Loop (cost=13.63..51,136.07 rows=5,643 width=24) (actual time=74.148..17,404.799 rows=72,134 loops=1)

  • Join Filter: (t_4.f_data_source = t_8.id)
  • Rows Removed by Join Filter: 288536
23. 280.263 16,681.879 ↓ 12.8 72,134 1

Hash Join (cost=13.63..50,711.78 rows=5,643 width=28) (actual time=74.128..16,681.879 rows=72,134 loops=1)

  • Hash Cond: ((p_1.cid)::numeric = t_2.catalog)
24. 226.843 16,400.959 ↓ 6.4 72,134 1

Nested Loop (cost=7.95..50,648.87 rows=11,285 width=36) (actual time=73.446..16,400.959 rows=72,134 loops=1)

25. 1,140.202 13,235.116 ↓ 6.3 73,475 1

Nested Loop (cost=7.53..44,117.85 rows=11,731 width=28) (actual time=73.354..13,235.116 rows=73,475 loops=1)

26. 1,134.618 3,345.470 ↓ 18.1 795,404 1

Nested Loop (cost=7.10..11,367.00 rows=43,860 width=24) (actual time=71.560..3,345.470 rows=795,404 loops=1)

27. 2.355 80.097 ↓ 13.8 1,045 1

Hash Join (cost=6.67..952.38 rows=76 width=16) (actual time=71.474..80.097 rows=1,045 loops=1)

  • Hash Cond: (t_5.f_link2reestr = perm_org.organization_reestr_id)
28. 6.313 6.313 ↑ 1.0 1,046 1

Seq Scan on m_organization t_5 (cost=0.00..940.98 rows=1,058 width=24) (actual time=0.025..6.313 rows=1,046 loops=1)

  • Filter: (f_uptodate AND (NOT deleted))
  • Rows Removed by Filter: 3447
29. 0.705 71.429 ↓ 10.1 1,010 1

Hash (cost=5.42..5.42 rows=100 width=4) (actual time=71.429..71.429 rows=1,010 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
30. 1.322 70.724 ↓ 10.1 1,010 1

Nested Loop (cost=0.00..5.42 rows=100 width=4) (actual time=65.603..70.724 rows=1,010 loops=1)

31. 0.033 0.033 ↑ 1.0 1 1

Seq Scan on m_years t_6 (cost=0.00..2.42 rows=1 width=0) (actual time=0.018..0.033 rows=1 loops=1)

  • Filter: (id = 6)
  • Rows Removed by Filter: 113
32. 69.369 69.369 ↓ 10.1 1,010 1

CTE Scan on permitted_organizations perm_org (cost=0.00..2.00 rows=100 width=4) (actual time=65.581..69.369 rows=1,010 loops=1)

33. 2,130.755 2,130.755 ↑ 2.3 761 1,045

Index Scan using m_preschoolchildeducation2child_f_org_idx on m_preschoolchildeducation2child t_3 (cost=0.43..119.52 rows=1,751 width=24) (actual time=0.054..2.039 rows=761 loops=1,045)

  • Index Cond: (f_org = t_5.id)
34. 8,749.444 8,749.444 ↓ 0.0 0 795,404

Index Scan using m_preschoolchildeducation_id_idx on m_preschoolchildeducation t_4 (cost=0.42..0.74 rows=1 width=16) (actual time=0.010..0.011 rows=0 loops=795,404)

  • Index Cond: (id = t_3.f_edu_do)
  • Filter: (f_uptodate AND (NOT deleted) AND (f_get_learning_status_by_date(f_enroll_date, f_exclude_date, (now())::date) IS TRUE))
  • Rows Removed by Filter: 1
35. 2,939.000 2,939.000 ↑ 1.0 1 73,475

Index Scan using m_person_id_idx on m_person p_1 (cost=0.42..0.55 rows=1 width=24) (actual time=0.039..0.040 rows=1 loops=73,475)

  • Index Cond: (id = t_3.f_child)
  • Filter: (f_uptodate AND (NOT deleted) AND (f_link2reestr IS NOT NULL))
36. 0.025 0.657 ↓ 4.0 24 1

Hash (cost=5.60..5.60 rows=6 width=5) (actual time=0.657..0.657 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.050 0.632 ↓ 4.0 24 1

HashAggregate (cost=5.54..5.60 rows=6 width=5) (actual time=0.617..0.632 rows=24 loops=1)

  • Group Key: t_2.catalog
38. 0.040 0.582 ↓ 4.0 24 1

Nested Loop (cost=0.00..5.53 rows=6 width=5) (actual time=0.183..0.582 rows=24 loops=1)

  • Join Filter: (t_2.roleid = t1_1.roleid)
39. 0.497 0.497 ↑ 1.0 1 1

Seq Scan on userroles t1_1 (cost=0.00..1.38 rows=1 width=5) (actual time=0.154..0.497 rows=1 loops=1)

  • Filter: (sysuser = (core.f_sys_get_config('sysuser'::text))::numeric)
  • Rows Removed by Filter: 18
40. 0.045 0.045 ↓ 4.0 24 1

Seq Scan on urprivs t_2 (cost=0.00..4.08 rows=6 width=10) (actual time=0.022..0.045 rows=24 loops=1)

  • Filter: ((catalog IS NOT NULL) AND ((unitcode)::text = 'orgreestr'::text))
  • Rows Removed by Filter: 142
41. 288.522 288.536 ↑ 1.0 5 72,134

Materialize (cost=0.00..1.07 rows=5 width=8) (actual time=0.001..0.004 rows=5 loops=72,134)

42. 0.014 0.014 ↑ 1.0 5 1

Seq Scan on m_dataresource t_8 (cost=0.00..1.05 rows=5 width=8) (actual time=0.008..0.014 rows=5 loops=1)

43. 649.206 649.206 ↑ 13.0 1 72,134

Index Scan using m_years_end_idx on m_years t_7 (cost=0.14..0.90 rows=13 width=8) (actual time=0.005..0.009 rows=1 loops=72,134)

  • Index Cond: (t_4.f_enroll_date <= "end")
  • Filter: (t_4.f_enroll_date >= begin)
  • Rows Removed by Filter: 12
44. 183.210 10,016.374 ↓ 258.2 143,069 1

Unique (cost=161,103.00..161,105.77 rows=554 width=24) (actual time=9,717.267..10,016.374 rows=143,069 loops=1)

45. 362.113 9,833.164 ↓ 258.5 143,211 1

Sort (cost=161,103.00..161,104.38 rows=554 width=24) (actual time=9,717.264..9,833.164 rows=143,211 loops=1)

  • Sort Key: p_2.f_link2reestr, t_15.priority DESC, t_11.f_enroll_date DESC
  • Sort Method: quicksort Memory: 17333kB
46. 235.358 9,471.051 ↓ 258.5 143,211 1

Hash Join (cost=22,196.35..161,077.75 rows=554 width=24) (actual time=268.211..9,471.051 rows=143,211 loops=1)

  • Hash Cond: (t_11.f_data_source = t_15.id)
47. 250.678 9,235.654 ↓ 258.5 143,211 1

Hash Join (cost=22,195.23..161,069.02 rows=554 width=28) (actual time=268.157..9,235.654 rows=143,211 loops=1)

  • Hash Cond: (t_12.f_link2reestr = perm_org_1.organization_reestr_id)
48. 408.634 8,983.538 ↓ 18.7 144,012 1

Hash Join (cost=22,191.98..161,031.34 rows=7,704 width=36) (actual time=266.709..8,983.538 rows=144,012 loops=1)

  • Hash Cond: ((p_2.cid)::numeric = t_9.catalog)
49. 572.486 8,574.289 ↓ 9.3 144,012 1

Nested Loop (cost=22,186.30..160,947.53 rows=15,408 width=44) (actual time=266.074..8,574.289 rows=144,012 loops=1)

50. 384.073 5,953.535 ↓ 10.7 170,689 1

Hash Join (cost=22,185.88..152,352.60 rows=16,016 width=36) (actual time=266.000..5,953.535 rows=170,689 loops=1)

  • Hash Cond: (t_10.f_org = t_12.id)
51. 641.882 5,563.981 ↓ 3.7 252,089 1

Nested Loop (cost=21,231.67..150,982.89 rows=68,091 width=28) (actual time=260.495..5,563.981 rows=252,089 loops=1)

52. 211.038 2,185.871 ↓ 4.2 144,012 1

Nested Loop (cost=21,231.24..111,721.65 rows=34,613 width=16) (actual time=236.377..2,185.871 rows=144,012 loops=1)

53. 0.006 0.058 ↑ 1.0 1 1

Nested Loop (cost=0.00..4.86 rows=1 width=8) (actual time=0.027..0.058 rows=1 loops=1)

54. 0.026 0.026 ↑ 1.0 1 1

Seq Scan on m_years t_13 (cost=0.00..2.42 rows=1 width=4) (actual time=0.014..0.026 rows=1 loops=1)

  • Filter: (id = 6)
  • Rows Removed by Filter: 113
55. 0.026 0.026 ↑ 1.0 1 1

Seq Scan on m_years t_14 (cost=0.00..2.42 rows=1 width=12) (actual time=0.009..0.026 rows=1 loops=1)

  • Filter: (id = 6)
  • Rows Removed by Filter: 113
56. 1,742.268 1,974.775 ↓ 4.2 144,012 1

Bitmap Heap Scan on m_mainchildeducation t_11 (cost=21,231.24..111,370.66 rows=34,613 width=16) (actual time=236.338..1,974.775 rows=144,012 loops=1)

  • Recheck Cond: ((f_enroll_date >= t_14.begin) AND (f_enroll_date <= t_14."end"))
  • Filter: (f_uptodate AND (NOT deleted) AND (f_get_learning_status_by_date(f_enroll_date, f_exclude_date, (now())::date) IS TRUE))
  • Rows Removed by Filter: 8576
  • Heap Blocks: exact=17263
57. 3.533 232.507 ↓ 0.0 0 1

BitmapAnd (cost=21,231.24..21,231.24 rows=104,280 width=0) (actual time=232.507..232.507 rows=0 loops=1)

58. 39.209 39.209 ↑ 1.1 171,931 1

Bitmap Index Scan on m_mainchildeducation_f_enroll_date_idx (cost=0.00..3,805.15 rows=181,272 width=0) (actual time=39.209..39.209 rows=171,931 loops=1)

  • Index Cond: ((f_enroll_date >= t_14.begin) AND (f_enroll_date <= t_14."end"))
59. 189.765 189.765 ↑ 1.0 934,094 1

Bitmap Index Scan on m_mainchildeducation_f_uptodate_idx (cost=0.00..17,339.31 rows=938,518 width=0) (actual time=189.765..189.765 rows=934,094 loops=1)

  • Index Cond: (f_uptodate = true)
60. 2,736.228 2,736.228 ↑ 1.5 2 144,012

Index Scan using m_mainchildeducation2child_f_edu_oo_idx on m_mainchildeducation2child t_10 (cost=0.43..1.10 rows=3 width=24) (actual time=0.012..0.019 rows=2 loops=144,012)

  • Index Cond: (f_edu_oo = t_11.id)
61. 0.827 5.481 ↑ 1.0 1,046 1

Hash (cost=940.98..940.98 rows=1,058 width=24) (actual time=5.481..5.481 rows=1,046 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 74kB
62. 4.654 4.654 ↑ 1.0 1,046 1

Seq Scan on m_organization t_12 (cost=0.00..940.98 rows=1,058 width=24) (actual time=0.014..4.654 rows=1,046 loops=1)

  • Filter: (f_uptodate AND (NOT deleted))
  • Rows Removed by Filter: 3447
63. 2,048.268 2,048.268 ↑ 1.0 1 170,689

Index Scan using m_person_id_idx on m_person p_2 (cost=0.42..0.53 rows=1 width=24) (actual time=0.012..0.012 rows=1 loops=170,689)

  • Index Cond: (id = t_10.f_child)
  • Filter: (f_uptodate AND (NOT deleted) AND (f_link2reestr IS NOT NULL))
64. 0.023 0.615 ↓ 4.0 24 1

Hash (cost=5.60..5.60 rows=6 width=5) (actual time=0.615..0.615 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
65. 0.047 0.592 ↓ 4.0 24 1

HashAggregate (cost=5.54..5.60 rows=6 width=5) (actual time=0.575..0.592 rows=24 loops=1)

  • Group Key: t_9.catalog
66. 0.041 0.545 ↓ 4.0 24 1

Nested Loop (cost=0.00..5.53 rows=6 width=5) (actual time=0.157..0.545 rows=24 loops=1)

  • Join Filter: (t_9.roleid = t1_2.roleid)
67. 0.450 0.450 ↑ 1.0 1 1

Seq Scan on userroles t1_2 (cost=0.00..1.38 rows=1 width=5) (actual time=0.121..0.450 rows=1 loops=1)

  • Filter: (sysuser = (core.f_sys_get_config('sysuser'::text))::numeric)
  • Rows Removed by Filter: 18
68. 0.054 0.054 ↓ 4.0 24 1

Seq Scan on urprivs t_9 (cost=0.00..4.08 rows=6 width=10) (actual time=0.028..0.054 rows=24 loops=1)

  • Filter: ((catalog IS NOT NULL) AND ((unitcode)::text = 'orgreestr'::text))
  • Rows Removed by Filter: 142
69. 0.720 1.438 ↓ 10.1 1,010 1

Hash (cost=2.00..2.00 rows=100 width=4) (actual time=1.438..1.438 rows=1,010 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
70. 0.718 0.718 ↓ 10.1 1,010 1

CTE Scan on permitted_organizations perm_org_1 (cost=0.00..2.00 rows=100 width=4) (actual time=0.002..0.718 rows=1,010 loops=1)

71. 0.007 0.039 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=8) (actual time=0.039..0.039 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
72. 0.032 0.032 ↑ 1.0 5 1

Seq Scan on m_dataresource t_15 (cost=0.00..1.05 rows=5 width=8) (actual time=0.026..0.032 rows=5 loops=1)

73. 85.582 4,794.554 ↓ 26,455.0 52,910 1

Unique (cost=79,750.23..79,750.24 rows=2 width=24) (actual time=4,653.873..4,794.554 rows=52,910 loops=1)

74. 147.522 4,708.972 ↓ 34,112.5 68,225 1

Sort (cost=79,750.23..79,750.23 rows=2 width=24) (actual time=4,653.871..4,708.972 rows=68,225 loops=1)

  • Sort Key: p_3.f_link2reestr, t_22.priority DESC, t_18.f_enroll_date DESC
  • Sort Method: quicksort Memory: 8403kB
75. 110.354 4,561.450 ↓ 34,112.5 68,225 1

Hash Join (cost=48,066.36..79,750.22 rows=2 width=24) (actual time=2,099.940..4,561.450 rows=68,225 loops=1)

  • Hash Cond: (t_19.f_link2reestr = perm_org_2.organization_reestr_id)
76. 113.905 4,449.679 ↓ 2,274.2 68,225 1

Hash Join (cost=48,063.11..79,746.84 rows=30 width=32) (actual time=2,098.508..4,449.679 rows=68,225 loops=1)

  • Hash Cond: (t_18.f_data_source = t_22.id)
77. 152.966 4,335.736 ↓ 57.0 68,225 1

Hash Join (cost=48,062.00..79,740.94 rows=1,196 width=36) (actual time=2,098.459..4,335.736 rows=68,225 loops=1)

  • Hash Cond: ((p_3.cid)::numeric = t_16.catalog)
78. 197.873 4,182.142 ↓ 28.5 68,225 1

Nested Loop (cost=48,056.32..79,723.13 rows=2,391 width=44) (actual time=2,097.808..4,182.142 rows=68,225 loops=1)

79. 128.435 3,431.221 ↓ 27.8 69,131 1

Hash Join (cost=48,055.90..78,009.97 rows=2,486 width=36) (actual time=2,097.665..3,431.221 rows=69,131 loops=1)

  • Hash Cond: (t_17.f_org = t_19.id)
80. 1,044.524 3,297.329 ↓ 7.9 83,698 1

Hash Join (cost=47,101.69..76,991.27 rows=10,568 width=28) (actual time=1,075.900..3,297.329 rows=83,698 loops=1)

  • Hash Cond: (t_17.f_add_edu = t_18.id)
81. 1,326.272 1,326.272 ↑ 1.0 1,141,302 1

Seq Scan on m_additionaledu2child t_17 (cost=0.00..25,504.02 rows=1,141,302 width=24) (actual time=0.099..1,326.272 rows=1,141,302 loops=1)

82. 69.321 926.533 ↓ 10.4 68,279 1

Hash (cost=47,019.38..47,019.38 rows=6,585 width=16) (actual time=926.533..926.533 rows=68,279 loops=1)

  • Buckets: 131072 (originally 8192) Batches: 1 (originally 1) Memory Usage: 4225kB
83. 96.341 857.212 ↓ 10.4 68,279 1

Nested Loop (cost=14,845.07..47,019.38 rows=6,585 width=16) (actual time=244.133..857.212 rows=68,279 loops=1)

84. 0.006 0.053 ↑ 1.0 1 1

Nested Loop (cost=0.00..4.86 rows=1 width=8) (actual time=0.022..0.053 rows=1 loops=1)

85. 0.026 0.026 ↑ 1.0 1 1

Seq Scan on m_years t_20 (cost=0.00..2.42 rows=1 width=4) (actual time=0.014..0.026 rows=1 loops=1)

  • Filter: (id = 6)
  • Rows Removed by Filter: 113
86. 0.021 0.021 ↑ 1.0 1 1

Seq Scan on m_years t_21 (cost=0.00..2.42 rows=1 width=12) (actual time=0.005..0.021 rows=1 loops=1)

  • Filter: (id = 6)
  • Rows Removed by Filter: 113
87. 517.527 760.818 ↓ 10.4 68,279 1

Bitmap Heap Scan on m_additionaledu t_18 (cost=14,845.07..46,948.67 rows=6,585 width=16) (actual time=244.093..760.818 rows=68,279 loops=1)

  • Recheck Cond: ((f_enroll_date >= t_21.begin) AND (f_enroll_date <= t_21."end"))
  • Filter: (f_uptodate AND (NOT deleted) AND (f_get_learning_status_by_date(f_enroll_date, f_exclude_date, (now())::date) IS TRUE))
  • Rows Removed by Filter: 5900
  • Heap Blocks: exact=3887
88. 1.455 243.291 ↓ 0.0 0 1

BitmapAnd (cost=14,845.07..14,845.07 rows=19,755 width=0) (actual time=243.291..243.291 rows=0 loops=1)

89. 20.054 20.054 ↓ 1.2 90,921 1

Bitmap Index Scan on m_additionaledu_f_enroll_date_idx (cost=0.00..1,662.62 rows=79,020 width=0) (actual time=20.054..20.054 rows=90,921 loops=1)

  • Index Cond: ((f_enroll_date >= t_21.begin) AND (f_enroll_date <= t_21."end"))
90. 86.016 86.016 ↓ 1.2 436,610 1

Bitmap Index Scan on m_additionaledu_f_uptodate_idx (cost=0.00..6,575.34 rows=355,588 width=0) (actual time=86.016..86.016 rows=436,610 loops=1)

  • Index Cond: (f_uptodate = true)
91. 135.766 135.766 ↓ 2.0 707,749 1

Bitmap Index Scan on m_additionaledu_deleted_idx (cost=0.00..6,575.34 rows=355,588 width=0) (actual time=135.766..135.766 rows=707,749 loops=1)

  • Index Cond: (deleted = false)
92. 0.839 5.457 ↑ 1.0 1,046 1

Hash (cost=940.98..940.98 rows=1,058 width=24) (actual time=5.457..5.457 rows=1,046 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 74kB
93. 4.618 4.618 ↑ 1.0 1,046 1

Seq Scan on m_organization t_19 (cost=0.00..940.98 rows=1,058 width=24) (actual time=0.020..4.618 rows=1,046 loops=1)

  • Filter: (f_uptodate AND (NOT deleted))
  • Rows Removed by Filter: 3447
94. 553.048 553.048 ↑ 1.0 1 69,131

Index Scan using m_person_id_idx on m_person p_3 (cost=0.42..0.68 rows=1 width=24) (actual time=0.007..0.008 rows=1 loops=69,131)

  • Index Cond: (id = t_17.f_child)
  • Filter: (f_uptodate AND (NOT deleted) AND (f_link2reestr IS NOT NULL))
  • Rows Removed by Filter: 0
95. 0.024 0.628 ↓ 4.0 24 1

Hash (cost=5.60..5.60 rows=6 width=5) (actual time=0.628..0.628 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
96. 0.053 0.604 ↓ 4.0 24 1

HashAggregate (cost=5.54..5.60 rows=6 width=5) (actual time=0.588..0.604 rows=24 loops=1)

  • Group Key: t_16.catalog
97. 0.045 0.551 ↓ 4.0 24 1

Nested Loop (cost=0.00..5.53 rows=6 width=5) (actual time=0.161..0.551 rows=24 loops=1)

  • Join Filter: (t_16.roleid = t1_3.roleid)
98. 0.460 0.460 ↑ 1.0 1 1

Seq Scan on userroles t1_3 (cost=0.00..1.38 rows=1 width=5) (actual time=0.131..0.460 rows=1 loops=1)

  • Filter: (sysuser = (core.f_sys_get_config('sysuser'::text))::numeric)
  • Rows Removed by Filter: 18
99. 0.046 0.046 ↓ 4.0 24 1

Seq Scan on urprivs t_16 (cost=0.00..4.08 rows=6 width=10) (actual time=0.023..0.046 rows=24 loops=1)

  • Filter: ((catalog IS NOT NULL) AND ((unitcode)::text = 'orgreestr'::text))
  • Rows Removed by Filter: 142
100. 0.008 0.038 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=8) (actual time=0.038..0.038 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
101. 0.030 0.030 ↑ 1.0 5 1

Seq Scan on m_dataresource t_22 (cost=0.00..1.05 rows=5 width=8) (actual time=0.026..0.030 rows=5 loops=1)

102. 0.717 1.417 ↓ 10.1 1,010 1

Hash (cost=2.00..2.00 rows=100 width=4) (actual time=1.417..1.417 rows=1,010 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
103. 0.700 0.700 ↓ 10.1 1,010 1

CTE Scan on permitted_organizations perm_org_2 (cost=0.00..2.00 rows=100 width=4) (actual time=0.003..0.700 rows=1,010 loops=1)

104. 36.027 3,507.743 ↓ 2.8 25,962 1

Unique (cost=19,951.72..19,997.51 rows=9,158 width=24) (actual time=3,451.493..3,507.743 rows=25,962 loops=1)

105. 58.849 3,471.716 ↓ 2.8 26,031 1

Sort (cost=19,951.72..19,974.61 rows=9,158 width=24) (actual time=3,451.490..3,471.716 rows=26,031 loops=1)

  • Sort Key: p_4.f_link2reestr, t_29.priority DESC, t_25.f_enroll_date DESC
  • Sort Method: quicksort Memory: 2802kB
106. 78.256 3,412.867 ↓ 2.8 26,031 1

Nested Loop (cost=8.09..19,349.08 rows=9,158 width=24) (actual time=4.845..3,412.867 rows=26,031 loops=1)

107. 154.655 3,152.394 ↓ 36.0 26,031 1

Nested Loop (cost=7.94..18,602.29 rows=723 width=24) (actual time=4.830..3,152.394 rows=26,031 loops=1)

  • Join Filter: (t_25.f_data_source = t_29.id)
  • Rows Removed by Join Filter: 104124
108. 441.758 2,893.615 ↓ 36.0 26,031 1

Nested Loop Semi Join (cost=7.94..18,547.00 rows=723 width=28) (actual time=4.808..2,893.615 rows=26,031 loops=1)

  • Join Filter: ((p_4.cid)::numeric = t_23.catalog)
  • Rows Removed by Join Filter: 391654
109. 82.241 2,165.516 ↓ 18.0 26,031 1

Nested Loop (cost=7.94..18,389.63 rows=1,446 width=36) (actual time=4.644..2,165.516 rows=26,031 loops=1)

110. 166.884 1,847.088 ↓ 17.5 26,243 1

Nested Loop (cost=7.52..17,070.57 rows=1,503 width=28) (actual time=4.566..1,847.088 rows=26,243 loops=1)

111. 127.618 307.132 ↓ 8.0 85,817 1

Nested Loop (cost=7.10..8,768.19 rows=10,754 width=24) (actual time=4.415..307.132 rows=85,817 loops=1)

112. 2.086 10.224 ↓ 13.8 1,045 1

Hash Join (cost=6.67..952.38 rows=76 width=16) (actual time=3.124..10.224 rows=1,045 loops=1)

  • Hash Cond: (t_26.f_link2reestr = perm_org_3.organization_reestr_id)
113. 5.056 5.056 ↑ 1.0 1,046 1

Seq Scan on m_organization t_26 (cost=0.00..940.98 rows=1,058 width=24) (actual time=0.027..5.056 rows=1,046 loops=1)

  • Filter: (f_uptodate AND (NOT deleted))
  • Rows Removed by Filter: 3447
114. 0.770 3.082 ↓ 10.1 1,010 1

Hash (cost=5.42..5.42 rows=100 width=4) (actual time=3.082..3.082 rows=1,010 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
115. 1.500 2.312 ↓ 10.1 1,010 1

Nested Loop (cost=0.00..5.42 rows=100 width=4) (actual time=0.021..2.312 rows=1,010 loops=1)

116. 0.026 0.026 ↑ 1.0 1 1

Seq Scan on m_years t_27 (cost=0.00..2.42 rows=1 width=0) (actual time=0.013..0.026 rows=1 loops=1)

  • Filter: (id = 6)
  • Rows Removed by Filter: 113
117. 0.786 0.786 ↓ 10.1 1,010 1

CTE Scan on permitted_organizations perm_org_3 (cost=0.00..2.00 rows=100 width=4) (actual time=0.001..0.786 rows=1,010 loops=1)

118. 169.290 169.290 ↑ 29.3 82 1,045

Index Scan using m_middleprofeducation2child_f_org_idx on m_middleprofeducation2child t_24 (cost=0.42..78.78 rows=2,406 width=24) (actual time=0.006..0.162 rows=82 loops=1,045)

  • Index Cond: (f_org = t_26.id)
119. 1,373.072 1,373.072 ↓ 0.0 0 85,817

Index Scan using m_middleprofeducation_id_idx on m_middleprofeducation t_25 (cost=0.42..0.76 rows=1 width=16) (actual time=0.015..0.016 rows=0 loops=85,817)

  • Index Cond: (id = t_24.f_edu_spo)
  • Filter: (f_uptodate AND (NOT deleted) AND (f_get_learning_status_by_date(f_enroll_date, f_exclude_date, (now())::date) IS TRUE))
  • Rows Removed by Filter: 1
120. 236.187 236.187 ↑ 1.0 1 26,243

Index Scan using m_person_id_idx on m_person p_4 (cost=0.42..0.87 rows=1 width=24) (actual time=0.008..0.009 rows=1 loops=26,243)

  • Index Cond: (id = t_24.f_child)
  • Filter: (f_uptodate AND (NOT deleted) AND (f_link2reestr IS NOT NULL))
121. 286.141 286.341 ↓ 2.7 16 26,031

Materialize (cost=0.00..5.56 rows=6 width=5) (actual time=0.001..0.011 rows=16 loops=26,031)

122. 0.040 0.200 ↓ 4.0 24 1

Nested Loop (cost=0.00..5.53 rows=6 width=5) (actual time=0.118..0.200 rows=24 loops=1)

  • Join Filter: (t_23.roleid = t1_4.roleid)
123. 0.091 0.091 ↑ 1.0 1 1

Seq Scan on userroles t1_4 (cost=0.00..1.38 rows=1 width=5) (actual time=0.091..0.091 rows=1 loops=1)

  • Filter: (sysuser = (core.f_sys_get_config('sysuser'::text))::numeric)
124. 0.069 0.069 ↓ 4.0 24 1

Seq Scan on urprivs t_23 (cost=0.00..4.08 rows=6 width=10) (actual time=0.021..0.069 rows=24 loops=1)

  • Filter: ((catalog IS NOT NULL) AND ((unitcode)::text = 'orgreestr'::text))
  • Rows Removed by Filter: 140
125. 104.110 104.124 ↑ 1.0 5 26,031

Materialize (cost=0.00..1.07 rows=5 width=8) (actual time=0.001..0.004 rows=5 loops=26,031)

126. 0.014 0.014 ↑ 1.0 5 1

Seq Scan on m_dataresource t_29 (cost=0.00..1.05 rows=5 width=8) (actual time=0.009..0.014 rows=5 loops=1)

127. 182.217 182.217 ↑ 13.0 1 26,031

Index Scan using m_years_end_idx on m_years t_28 (cost=0.14..0.90 rows=13 width=8) (actual time=0.004..0.007 rows=1 loops=26,031)

  • Index Cond: (t_25.f_enroll_date <= "end")
  • Filter: (t_25.f_enroll_date >= begin)
  • Rows Removed by Filter: 11
128. 0.002 0.108 ↓ 0.0 0 1

Unique (cost=46.45..46.48 rows=6 width=24) (actual time=0.108..0.108 rows=0 loops=1)

129. 0.020 0.106 ↓ 0.0 0 1

Sort (cost=46.45..46.46 rows=6 width=24) (actual time=0.106..0.106 rows=0 loops=1)

  • Sort Key: p_5.f_link2reestr, t_36.priority DESC, t_32.f_enroll_date DESC
  • Sort Method: quicksort Memory: 25kB
130. 0.003 0.086 ↓ 0.0 0 1

Nested Loop (cost=28.30..46.37 rows=6 width=24) (actual time=0.086..0.086 rows=0 loops=1)

  • Join Filter: ((t_32.f_enroll_date >= t_35.begin) AND (t_32.f_enroll_date <= t_35."end"))
131. 0.000 0.083 ↓ 0.0 0 1

Nested Loop (cost=28.30..42.52 rows=1 width=24) (actual time=0.083..0.083 rows=0 loops=1)

  • Join Filter: (t_32.f_data_source = t_36.id)
132. 0.002 0.083 ↓ 0.0 0 1

Nested Loop Semi Join (cost=28.30..41.41 rows=1 width=28) (actual time=0.083..0.083 rows=0 loops=1)

133. 0.002 0.081 ↓ 0.0 0 1

Nested Loop (cost=28.15..38.42 rows=1 width=36) (actual time=0.081..0.081 rows=0 loops=1)

134. 0.012 0.079 ↓ 0.0 0 1

Hash Join (cost=27.72..30.11 rows=1 width=28) (actual time=0.079..0.079 rows=0 loops=1)

  • Hash Cond: (perm_org_4.organization_reestr_id = t_33.f_link2reestr)
135. 0.003 0.003 ↑ 100.0 1 1

CTE Scan on permitted_organizations perm_org_4 (cost=0.00..2.00 rows=100 width=4) (actual time=0.003..0.003 rows=1 loops=1)

136. 0.002 0.064 ↓ 0.0 0 1

Hash (cost=27.71..27.71 rows=1 width=36) (actual time=0.064..0.064 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
137. 0.001 0.062 ↓ 0.0 0 1

Nested Loop (cost=0.43..27.71 rows=1 width=36) (actual time=0.062..0.062 rows=0 loops=1)

138. 0.001 0.061 ↓ 0.0 0 1

Nested Loop (cost=0.14..23.26 rows=1 width=28) (actual time=0.061..0.061 rows=0 loops=1)

139. 0.004 0.060 ↓ 0.0 0 1

Nested Loop (cost=0.00..15.08 rows=1 width=16) (actual time=0.060..0.060 rows=0 loops=1)

140. 0.052 0.052 ↑ 1.0 1 1

Seq Scan on m_years t_34 (cost=0.00..2.42 rows=1 width=0) (actual time=0.024..0.052 rows=1 loops=1)

  • Filter: (id = 6)
  • Rows Removed by Filter: 113
141. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on m_professionaledu t_32 (cost=0.00..12.65 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1)

  • Filter: (f_uptodate AND (NOT deleted) AND (f_get_learning_status_by_date(f_enroll_date, f_exclude_date, (now())::date) IS TRUE))
142. 0.000 0.000 ↓ 0.0 0

Index Scan using m_professionaledu2child_f_edu_prof_idx on m_professionaledu2child t_31 (cost=0.14..8.16 rows=1 width=24) (never executed)

  • Index Cond: (f_edu_prof = t_32.id)
143. 0.000 0.000 ↓ 0.0 0

Index Scan using m_organization_id_idx on m_organization t_33 (cost=0.28..4.44 rows=1 width=24) (never executed)

  • Index Cond: (id = t_31.f_org)
  • Filter: (f_uptodate AND (NOT deleted))
144. 0.000 0.000 ↓ 0.0 0

Index Scan using m_person_id_idx on m_person p_5 (cost=0.42..8.30 rows=1 width=24) (never executed)

  • Index Cond: (id = t_31.f_child)
  • Filter: (f_uptodate AND (NOT deleted) AND (f_link2reestr IS NOT NULL))
145. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.15..1.56 rows=1 width=5) (never executed)

  • Join Filter: (t_30.roleid = t1_5.roleid)
146. 0.000 0.000 ↓ 0.0 0

Index Scan using i_urprivs_cat on urprivs t_30 (cost=0.15..0.17 rows=1 width=10) (never executed)

  • Index Cond: ((catalog = (p_5.cid)::numeric) AND (catalog IS NOT NULL))
  • Filter: ((unitcode)::text = 'orgreestr'::text)
147. 0.000 0.000 ↓ 0.0 0

Seq Scan on userroles t1_5 (cost=0.00..1.38 rows=1 width=5) (never executed)

  • Filter: (sysuser = (core.f_sys_get_config('sysuser'::text))::numeric)
148. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_dataresource t_36 (cost=0.00..1.05 rows=5 width=8) (never executed)

149. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_years t_35 (cost=0.00..2.14 rows=114 width=8) (never executed)

150. 379.427 1,183.277 ↓ 4.0 430,168 1

Hash (cost=14,545.16..14,545.16 rows=107,904 width=4) (actual time=1,183.277..1,183.277 rows=430,168 loops=1)

  • Buckets: 524288 (originally 131072) Batches: 1 (originally 1) Memory Usage: 19220kB
151. 803.850 803.850 ↓ 4.0 430,168 1

Seq Scan on m_childreestr cr (cost=0.00..14,545.16 rows=107,904 width=4) (actual time=0.021..803.850 rows=430,168 loops=1)

  • Filter: ((NOT deleted) AND f_uptodate)
  • Rows Removed by Filter: 1448
152. 0.270 0.270 ↑ 1.0 1 30

Index Scan using m_person_id_idx on m_person p (cost=0.42..1.07 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=30)

  • Index Cond: (id = t.f_max_priority)
153. 0.351 0.570 ↓ 2.8 17 30

Materialize (cost=0.00..5.56 rows=6 width=5) (actual time=0.006..0.019 rows=17 loops=30)

154. 0.040 0.219 ↓ 4.0 24 1

Nested Loop (cost=0.00..5.53 rows=6 width=5) (actual time=0.156..0.219 rows=24 loops=1)

  • Join Filter: (t_1.roleid = t1.roleid)
155. 0.128 0.128 ↑ 1.0 1 1

Seq Scan on userroles t1 (cost=0.00..1.38 rows=1 width=5) (actual time=0.128..0.128 rows=1 loops=1)

  • Filter: (sysuser = (core.f_sys_get_config('sysuser'::text))::numeric)
156. 0.051 0.051 ↓ 4.0 24 1

Seq Scan on urprivs t_1 (cost=0.00..4.08 rows=6 width=10) (actual time=0.022..0.051 rows=24 loops=1)

  • Filter: ((catalog IS NOT NULL) AND ((unitcode)::text = 'orgreestr'::text))
  • Rows Removed by Filter: 140
Planning time : 77.842 ms
Execution time : 40,491.017 ms