explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NEe1 : Optimization for: Optimization for: plan #DCuI; plan #l38n

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.017 18,582.573 ↓ 6.2 50 1

Limit (cost=9,830.83..42,865.38 rows=8 width=2,082) (actual time=13,256.992..18,582.573 rows=50 loops=1)

2.          

CTE permitted_organizations

3. 0.338 764.032 ↓ 5.5 547 1

Hash Join (cost=3.01..86.45 rows=100 width=12) (actual time=762.808..764.032 rows=547 loops=1)

  • Hash Cond: (t_6.id = (unnest(d.d)))
4. 0.894 0.894 ↑ 1.0 1,341 1

Seq Scan on m_orgreestr t_6 (cost=0.00..77.41 rows=1,341 width=12) (actual time=0.002..0.894 rows=1,341 loops=1)

5. 0.097 762.800 ↓ 5.5 547 1

Hash (cost=1.76..1.76 rows=100 width=4) (actual time=762.800..762.800 rows=547 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 28kB
6. 762.703 762.703 ↓ 5.5 547 1

Function Scan on get_orgreestr_ids_by_permision_and_filter d (cost=0.26..0.76 rows=100 width=4) (actual time=762.617..762.703 rows=547 loops=1)

7. 0.802 18,582.556 ↓ 6.2 50 1

Nested Loop Semi Join (cost=9,744.38..42,778.93 rows=8 width=2,082) (actual time=13,256.991..18,582.556 rows=50 loops=1)

8. 0.847 17,228.454 ↓ 2.9 50 1

Nested Loop (cost=9,743.96..41,371.00 rows=17 width=907) (actual time=13,191.875..17,228.454 rows=50 loops=1)

9. 1.471 12,943.471 ↓ 1.1 1,156 1

Nested Loop (cost=9,743.54..40,085.91 rows=1,087 width=533) (actual time=12,927.990..12,943.471 rows=1,156 loops=1)

10. 0.599 12,938.532 ↑ 2.1 1,156 1

Unique (cost=9,743.11..24,923.55 rows=2,466 width=16) (actual time=12,927.981..12,938.532 rows=1,156 loops=1)

11. 1.729 12,937.933 ↑ 2.1 1,156 1

Nested Loop (cost=9,743.11..24,917.38 rows=2,466 width=16) (actual time=12,927.980..12,937.933 rows=1,156 loops=1)

12. 0.593 12,929.268 ↑ 2.1 1,156 1

Unique (cost=9,742.69..9,755.02 rows=2,466 width=16) (actual time=12,927.916..12,929.268 rows=1,156 loops=1)

13. 49.161 12,928.675 ↑ 2.1 1,164 1

Sort (cost=9,742.69..9,748.86 rows=2,466 width=16) (actual time=12,927.916..12,928.675 rows=1,164 loops=1)

  • Sort Key: cr.id, t_3.priority DESC
  • Sort Method: quicksort Memory: 6,559kB
14. 42.823 12,879.514 ↓ 30.2 74,385 1

Hash Join (cost=5.49..9,603.76 rows=2,466 width=16) (actual time=806.903..12,879.514 rows=74,385 loops=1)

  • Hash Cond: (p_1.f_data_source = t_3.id)
15. 68.753 12,836.679 ↓ 30.2 74,385 1

Nested Loop (cost=4.38..9,568.74 rows=2,466 width=12) (actual time=806.878..12,836.679 rows=74,385 loops=1)

16. 17.817 1,758.946 ↓ 13.3 74,385 1

Nested Loop (cost=3.95..3,303.01 rows=5,592 width=12) (actual time=783.270..1,758.946 rows=74,385 loops=1)

17. 0.634 781.144 ↓ 5.5 547 1

Nested Loop (cost=3.53..254.35 rows=100 width=8) (actual time=764.426..781.144 rows=547 loops=1)

18. 0.840 765.741 ↓ 5.5 547 1

Hash Join (cost=3.25..86.69 rows=100 width=16) (actual time=764.376..765.741 rows=547 loops=1)

  • Hash Cond: (t_4.id = perm_org.organization_reestr_id)
19. 0.538 0.538 ↑ 1.0 1,341 1

Seq Scan on m_orgreestr t_4 (cost=0.00..77.41 rows=1,341 width=12) (actual time=0.004..0.538 rows=1,341 loops=1)

20. 0.111 764.363 ↓ 5.5 547 1

Hash (cost=2.00..2.00 rows=100 width=4) (actual time=764.363..764.363 rows=547 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 28kB
21. 764.252 764.252 ↓ 5.5 547 1

CTE Scan on permitted_organizations perm_org (cost=0.00..2.00 rows=100 width=4) (actual time=762.811..764.252 rows=547 loops=1)

22. 14.769 14.769 ↑ 1.0 1 547

Index Only Scan using m_organization_id_idx on m_organization t_5 (cost=0.28..1.67 rows=1 width=8) (actual time=0.027..0.027 rows=1 loops=547)

  • Index Cond: (id = t_4.f_max_priority)
  • Heap Fetches: 547
23. 959.985 959.985 ↑ 1.0 136 547

Index Scan using m_person_education_orgreestr_id_idx on m_person_education p_1 (cost=0.42..29.07 rows=142 width=16) (actual time=0.518..1.755 rows=136 loops=547)

  • Index Cond: (orgreestr_id = t_4.id)
  • Filter: (39 = ANY (learning_years))
  • Rows Removed by Filter: 384
24. 11,008.980 11,008.980 ↑ 1.0 1 74,385

Index Scan using m_childreestr_id_idx on m_childreestr cr (cost=0.42..1.11 rows=1 width=4) (actual time=0.148..0.148 rows=1 loops=74,385)

  • Index Cond: (id = p_1.childreestr_id)
  • Filter: (f_uptodate AND (NOT deleted))
25. 0.004 0.012 ↑ 1.0 5 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
26. 0.008 0.008 ↑ 1.0 5 1

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

27. 6.936 6.936 ↑ 1.0 1 1,156

Index Only Scan using m_childreestr_id_idx on m_childreestr t_2 (cost=0.42..6.13 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1,156)

  • Index Cond: (id = cr.id)
  • Heap Fetches: 1,156
28. 3.468 3.468 ↑ 1.0 1 1,156

Index Scan using m_childreestr_id_idx on m_childreestr t (cost=0.42..6.13 rows=1 width=533) (actual time=0.003..0.003 rows=1 loops=1,156)

  • Index Cond: (id = cr.id)
  • Filter: (f_uptodate AND (NOT deleted))
29. 4,284.136 4,284.136 ↓ 0.0 0 1,156

Index Scan using m_person_id_idx on m_person p (cost=0.42..1.17 rows=1 width=382) (actual time=3.706..3.706 rows=0 loops=1,156)

  • Index Cond: (id = t.f_max_priority)
  • Filter: (f_uptodate AND (NOT deleted) AND ((f_personlastname)::text ~~ 'А%'::text))
  • Rows Removed by Filter: 1
30. 2.950 12.000 ↑ 1.0 1 50

Nested Loop (cost=0.42..1.11 rows=1 width=5) (actual time=0.240..0.240 rows=1 loops=50)

31. 8.600 8.600 ↑ 1.0 1 50

Index Scan using i_urprivs_cat on urprivs t_1 (cost=0.28..0.30 rows=1 width=11) (actual time=0.172..0.172 rows=1 loops=50)

  • Index Cond: ((catalog = (p.cid)::numeric) AND (catalog IS NOT NULL))
  • Filter: ((unitcode)::text = 'orgreestr'::text)
32. 0.450 0.450 ↑ 1.0 1 50

Index Only Scan using uk_userroles on userroles t1 (cost=0.14..0.79 rows=1 width=5) (actual time=0.009..0.009 rows=1 loops=50)

  • Index Cond: ((roleid = t_1.roleid) AND (sysuser = (core.f_sys_get_config('sysuser'::text))::numeric))
  • Heap Fetches: 50
33.          

SubPlan (for Nested Loop Semi Join)

34. 0.050 1,339.900 ↑ 1.0 1 50

Limit (cost=0.98..17.06 rows=1 width=97) (actual time=26.798..26.798 rows=1 loops=50)

35. 0.158 1,339.850 ↑ 1.0 1 50

Nested Loop (cost=0.98..17.06 rows=1 width=97) (actual time=26.797..26.797 rows=1 loops=50)

36. 0.250 1,339.350 ↑ 1.0 1 50

Nested Loop (cost=0.84..16.89 rows=1 width=8) (actual time=26.787..26.787 rows=1 loops=50)

37. 733.950 733.950 ↑ 1.0 1 50

Index Scan using m_documentchild2child_f_child_idx on m_documentchild2child t_7 (cost=0.42..8.44 rows=1 width=8) (actual time=14.679..14.679 rows=1 loops=50)

  • Index Cond: (f_child = t.f_max_priority)
38. 605.150 605.150 ↑ 1.0 1 38

Index Scan using m_documentchild_id_idx on m_documentchild t_8 (cost=0.42..8.44 rows=1 width=16) (actual time=15.925..15.925 rows=1 loops=38)

  • Index Cond: (id = t_7.f_document)
  • Filter: (f_uptodate AND (NOT deleted))
39. 0.342 0.342 ↑ 1.0 1 38

Index Scan using m_doctypes_id_idx on m_doctypes t_9 (cost=0.14..0.16 rows=1 width=101) (actual time=0.009..0.009 rows=1 loops=38)

  • Index Cond: (id = t_8.f_doc_type)
40. 0.050 0.700 ↑ 1.0 1 50

Limit (cost=0.98..17.06 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=50)

41. 0.036 0.650 ↑ 1.0 1 50

Nested Loop (cost=0.98..17.06 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=50)

42. 0.148 0.500 ↑ 1.0 1 50

Nested Loop (cost=0.84..16.89 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=50)

43. 0.200 0.200 ↑ 1.0 1 50

Index Scan using m_documentchild2child_f_child_idx on m_documentchild2child t_10 (cost=0.42..8.44 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=50)

  • Index Cond: (f_child = t.f_max_priority)
44. 0.152 0.152 ↑ 1.0 1 38

Index Scan using m_documentchild_id_idx on m_documentchild t_11 (cost=0.42..8.44 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=38)

  • Index Cond: (id = t_10.f_document)
  • Filter: (f_uptodate AND (NOT deleted))
45. 0.114 0.114 ↑ 1.0 1 38

Index Only Scan using m_doctypes_id_idx on m_doctypes t_12 (cost=0.14..0.16 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=38)

  • Index Cond: (id = t_11.f_doc_type)
  • Heap Fetches: 38
46. 0.000 0.350 ↑ 1.0 1 50

Limit (cost=0.84..16.89 rows=1 width=6) (actual time=0.007..0.007 rows=1 loops=50)

47. 0.086 0.350 ↑ 1.0 1 50

Nested Loop (cost=0.84..16.89 rows=1 width=6) (actual time=0.007..0.007 rows=1 loops=50)

48. 0.150 0.150 ↑ 1.0 1 50

Index Scan using m_documentchild2child_f_child_idx on m_documentchild2child t_13 (cost=0.42..8.44 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=50)

  • Index Cond: (f_child = t.f_max_priority)
49. 0.114 0.114 ↑ 1.0 1 38

Index Scan using m_documentchild_id_idx on m_documentchild t_14 (cost=0.42..8.44 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=38)

  • Index Cond: (id = t_13.f_document)
  • Filter: (f_uptodate AND (NOT deleted))
50. 0.050 0.350 ↑ 1.0 1 50

Limit (cost=0.84..16.89 rows=1 width=7) (actual time=0.007..0.007 rows=1 loops=50)

51. 0.036 0.300 ↑ 1.0 1 50

Nested Loop (cost=0.84..16.89 rows=1 width=7) (actual time=0.006..0.006 rows=1 loops=50)

52. 0.150 0.150 ↑ 1.0 1 50

Index Scan using m_documentchild2child_f_child_idx on m_documentchild2child t_15 (cost=0.42..8.44 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=50)

  • Index Cond: (f_child = t.f_max_priority)
53. 0.114 0.114 ↑ 1.0 1 38

Index Scan using m_documentchild_id_idx on m_documentchild t_16 (cost=0.42..8.44 rows=1 width=15) (actual time=0.003..0.003 rows=1 loops=38)

  • Index Cond: (id = t_15.f_document)
  • Filter: f_uptodate
54. 0.000 0.000 ↓ 0.0 0

Subquery Scan on data (cost=15.43..52.84 rows=1 width=32) (never executed)

55.          

Initplan (for Subquery Scan)

56. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_declarationtype t_17 (cost=0.00..1.07 rows=1 width=23) (never executed)

  • Filter: ((code)::text = '3'::text)
57. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_declarationtype t_18 (cost=0.00..1.07 rows=1 width=23) (never executed)

  • Filter: ((code)::text = '1'::text)
58. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_declarationtype t_19 (cost=0.00..1.07 rows=1 width=23) (never executed)

  • Filter: ((code)::text = '2'::text)
59. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_declarationtype t_20 (cost=0.00..1.07 rows=1 width=23) (never executed)

  • Filter: ((code)::text = '3'::text)
60. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_declarationtype t_21 (cost=0.00..1.07 rows=1 width=23) (never executed)

  • Filter: ((code)::text = '0'::text)
61. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_declarationtype t_22 (cost=0.00..1.07 rows=1 width=23) (never executed)

  • Filter: ((code)::text = '1'::text)
62. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_declarationtype t_23 (cost=0.00..1.07 rows=1 width=23) (never executed)

  • Filter: ((code)::text = '2'::text)
63. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_declarationtype t_24 (cost=0.00..1.07 rows=1 width=23) (never executed)

  • Filter: ((code)::text = '3'::text)
64. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_declarationtype t_25 (cost=0.00..1.07 rows=1 width=23) (never executed)

  • Filter: ((code)::text = '4'::text)
65. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=5.75..43.13 rows=1 width=40) (never executed)

  • Group Key: p_2.f_link2reestr
66. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.75..43.11 rows=1 width=10) (never executed)

67. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.62..42.94 rows=1 width=16) (never executed)

68. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.20..42.46 rows=1 width=16) (never executed)

69. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=4.78..18.38 rows=1 width=16) (never executed)

  • Join Filter: ((p_2.cid)::numeric = t_28.catalog)
70. 0.000 0.000 ↓ 0.0 0

Index Scan using m_person_f_link2reestr_idx on m_person p_2 (cost=0.42..8.44 rows=1 width=24) (never executed)

  • Index Cond: (f_link2reestr = t.id)
  • Filter: (f_uptodate AND (NOT deleted))
71. 0.000 0.000 ↓ 0.0 0

Materialize (cost=4.35..9.83 rows=6 width=5) (never executed)

72. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.35..9.80 rows=6 width=5) (never executed)

73. 0.000 0.000 ↓ 0.0 0

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

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

Bitmap Heap Scan on urprivs t_28 (cost=4.35..8.44 rows=6 width=11) (never executed)

  • Recheck Cond: (((unitcode)::text = 'orgreestr'::text) AND (roleid = t1_1.roleid) AND (catalog IS NOT NULL))
75. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on i_urprivs_uc (cost=0.00..4.35 rows=6 width=0) (never executed)

  • Index Cond: (((unitcode)::text = 'orgreestr'::text) AND (roleid = t1_1.roleid) AND (catalog IS NOT NULL))
76. 0.000 0.000 ↓ 0.0 0

Index Scan using m_declaration2child_f_child_idx on m_declaration2child t_27 (cost=0.42..24.03 rows=5 width=16) (never executed)

  • Index Cond: (f_child = p_2.id)
77. 0.000 0.000 ↓ 0.0 0

Index Scan using m_declaration_id_idx on m_declaration t_26 (cost=0.42..0.47 rows=1 width=12) (never executed)

  • Index Cond: (id = t_27.f_decl)
  • Filter: (f_uptodate AND (NOT deleted))
78. 0.000 0.000 ↓ 0.0 0

Index Scan using m_declarationtype_id_idx on m_declarationtype t_29 (cost=0.13..0.16 rows=1 width=6) (never executed)

  • Index Cond: (id = t_26.f_delc_type)
  • Filter: ((code)::text = ANY ('{0,1,2,3,4}'::text[]))
79. 0.000 0.000 ↓ 0.0 0

Subquery Scan on data_1 (cost=15.47..52.88 rows=1 width=32) (never executed)

80.          

Initplan (for Subquery Scan)

81. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_declarationtype t_30 (cost=0.00..1.08 rows=1 width=32) (never executed)

  • Filter: ((code)::text = '3'::text)
82. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_declarationtype t_31 (cost=0.00..1.08 rows=1 width=32) (never executed)

  • Filter: ((code)::text = '1'::text)
83. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_declarationtype t_32 (cost=0.00..1.08 rows=1 width=32) (never executed)

  • Filter: ((code)::text = '2'::text)
84. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_declarationtype t_33 (cost=0.00..1.08 rows=1 width=32) (never executed)

  • Filter: ((code)::text = '3'::text)
85. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_declarationtype t_34 (cost=0.00..1.08 rows=1 width=32) (never executed)

  • Filter: ((code)::text = '0'::text)
86. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_declarationtype t_35 (cost=0.00..1.08 rows=1 width=32) (never executed)

  • Filter: ((code)::text = '1'::text)
87. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_declarationtype t_36 (cost=0.00..1.08 rows=1 width=32) (never executed)

  • Filter: ((code)::text = '2'::text)
88. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_declarationtype t_37 (cost=0.00..1.08 rows=1 width=32) (never executed)

  • Filter: ((code)::text = '3'::text)
89. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_declarationtype t_38 (cost=0.00..1.08 rows=1 width=32) (never executed)

  • Filter: ((code)::text = '4'::text)
90. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=5.75..43.13 rows=1 width=40) (never executed)

  • Group Key: p_3.f_link2reestr
91. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.75..43.11 rows=1 width=10) (never executed)

92. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.62..42.94 rows=1 width=16) (never executed)

93. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.20..42.46 rows=1 width=16) (never executed)

94. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=4.78..18.38 rows=1 width=16) (never executed)

  • Join Filter: ((p_3.cid)::numeric = t_41.catalog)
95. 0.000 0.000 ↓ 0.0 0

Index Scan using m_person_f_link2reestr_idx on m_person p_3 (cost=0.42..8.44 rows=1 width=24) (never executed)

  • Index Cond: (f_link2reestr = t.id)
  • Filter: (f_uptodate AND (NOT deleted))
96. 0.000 0.000 ↓ 0.0 0

Materialize (cost=4.35..9.83 rows=6 width=5) (never executed)

97. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.35..9.80 rows=6 width=5) (never executed)

98. 0.000 0.000 ↓ 0.0 0

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

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

Bitmap Heap Scan on urprivs t_41 (cost=4.35..8.44 rows=6 width=11) (never executed)

  • Recheck Cond: (((unitcode)::text = 'orgreestr'::text) AND (roleid = t1_2.roleid) AND (catalog IS NOT NULL))
100. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on i_urprivs_uc (cost=0.00..4.35 rows=6 width=0) (never executed)

  • Index Cond: (((unitcode)::text = 'orgreestr'::text) AND (roleid = t1_2.roleid) AND (catalog IS NOT NULL))
101. 0.000 0.000 ↓ 0.0 0

Index Scan using m_declaration2child_f_child_idx on m_declaration2child t_40 (cost=0.42..24.03 rows=5 width=16) (never executed)

  • Index Cond: (f_child = p_3.id)
102. 0.000 0.000 ↓ 0.0 0

Index Scan using m_declaration_id_idx on m_declaration t_39 (cost=0.42..0.47 rows=1 width=12) (never executed)

  • Index Cond: (id = t_40.f_decl)
  • Filter: (f_uptodate AND (NOT deleted))
103. 0.000 0.000 ↓ 0.0 0

Index Scan using m_declarationtype_id_idx on m_declarationtype t_42 (cost=0.13..0.16 rows=1 width=6) (never executed)

  • Index Cond: (id = t_39.f_delc_type)
  • Filter: ((code)::text = ANY ('{0,1,2,3,4}'::text[]))
Planning time : 2,603.118 ms
Execution time : 18,586.174 ms