explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.006 722.099 ↑ 1.0 50 1

Limit (cost=54,865.37..71,454.11 rows=50 width=2,082) (actual time=711.965..722.099 rows=50 loops=1)

2. 0.451 722.093 ↑ 14.6 50 1

Nested Loop Semi Join (cost=54,865.37..297,392.77 rows=731 width=2,082) (actual time=711.965..722.093 rows=50 loops=1)

3. 1.020 717.792 ↑ 29.2 50 1

Nested Loop (cost=54,864.95..168,850.48 rows=1,461 width=907) (actual time=711.710..717.792 rows=50 loops=1)

4. 0.284 712.148 ↑ 28.6 1,156 1

Unique (cost=54,864.52..55,029.86 rows=33,068 width=549) (actual time=711.509..712.148 rows=1,156 loops=1)

5. 38.628 711.864 ↑ 28.6 1,156 1

Sort (cost=54,864.52..54,947.19 rows=33,068 width=549) (actual time=711.507..711.864 rows=1,156 loops=1)

  • Sort Key: d.person_reestr_id, d.priority DESC
  • Sort Method: quicksort Memory: 8875kB
6. 130.082 673.236 ↓ 2.2 74,273 1

Hash Join (cost=34,121.75..52,382.25 rows=33,068 width=549) (actual time=475.685..673.236 rows=74,273 loops=1)

  • Hash Cond: (t_1.id = d.person_reestr_id)
7. 67.656 67.656 ↑ 1.0 487,114 1

Seq Scan on m_childreestr t_1 (cost=0.00..16,103.14 rows=487,114 width=533) (actual time=0.010..67.656 rows=487,114 loops=1)

8. 16.479 475.498 ↓ 2.2 74,273 1

Hash (cost=33,708.40..33,708.40 rows=33,068 width=8) (actual time=475.498..475.498 rows=74,273 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 3926kB
9. 12.626 459.019 ↓ 2.2 74,273 1

Subquery Scan on d (cost=33,212.38..33,708.40 rows=33,068 width=8) (actual time=420.409..459.019 rows=74,273 loops=1)

10. 13.836 446.393 ↓ 2.2 74,273 1

Unique (cost=33,212.38..33,377.72 rows=33,068 width=16) (actual time=420.408..446.393 rows=74,273 loops=1)

11. 47.000 432.557 ↓ 2.2 74,385 1

Sort (cost=33,212.38..33,295.05 rows=33,068 width=16) (actual time=420.407..432.557 rows=74,385 loops=1)

  • Sort Key: cr.id, t_2.priority DESC
  • Sort Method: quicksort Memory: 6559kB
12. 26.842 385.557 ↓ 2.2 74,385 1

Hash Join (cost=19,400.46..30,730.11 rows=33,068 width=16) (actual time=217.703..385.557 rows=74,385 loops=1)

  • Hash Cond: (p_1.f_data_source = t_2.id)
13. 23.212 358.705 ↓ 2.2 74,385 1

Hash Join (cost=19,399.35..30,274.31 rows=33,068 width=12) (actual time=217.674..358.705 rows=74,385 loops=1)

  • Hash Cond: (p_1.orgreestr_id = t_3.id)
14. 46.333 332.895 ↓ 2.2 74,385 1

Hash Join (cost=18,788.08..29,208.36 rows=33,068 width=16) (actual time=215.069..332.895 rows=74,385 loops=1)

  • Hash Cond: (p_1.childreestr_id = cr.id)
15. 72.010 72.010 ↑ 1.0 74,385 1

Seq Scan on m_person_education p_1 (cost=0.00..9,808.38 rows=74,991 width=16) (actual time=0.019..72.010 rows=74,385 loops=1)

  • Filter: (39 = ANY (learning_years))
  • Rows Removed by Filter: 210032
16. 44.493 214.552 ↓ 1.0 214,992 1

Hash (cost=16,103.14..16,103.14 rows=214,795 width=4) (actual time=214.552..214.552 rows=214,992 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 9607kB
17. 170.059 170.059 ↓ 1.0 214,992 1

Seq Scan on m_childreestr cr (cost=0.00..16,103.14 rows=214,795 width=4) (actual time=0.096..170.059 rows=214,992 loops=1)

  • Filter: (f_uptodate AND (NOT deleted))
  • Rows Removed by Filter: 272122
18. 0.274 2.598 ↑ 1.0 1,341 1

Hash (cost=594.50..594.50 rows=1,341 width=4) (actual time=2.598..2.598 rows=1,341 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 64kB
19. 0.781 2.324 ↑ 1.0 1,341 1

Hash Join (cost=94.17..594.50 rows=1,341 width=4) (actual time=0.705..2.324 rows=1,341 loops=1)

  • Hash Cond: (t_4.id = t_3.f_max_priority)
20. 0.852 0.852 ↑ 1.0 2,758 1

Seq Scan on m_organization t_4 (cost=0.00..476.58 rows=2,758 width=8) (actual time=0.004..0.852 rows=2,758 loops=1)

21. 0.236 0.691 ↑ 1.0 1,341 1

Hash (cost=77.41..77.41 rows=1,341 width=12) (actual time=0.691..0.691 rows=1,341 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 79kB
22. 0.455 0.455 ↑ 1.0 1,341 1

Seq Scan on m_orgreestr t_3 (cost=0.00..77.41 rows=1,341 width=12) (actual time=0.003..0.455 rows=1,341 loops=1)

23. 0.003 0.010 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.007 0.007 ↑ 1.0 5 1

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

25. 4.624 4.624 ↓ 0.0 0 1,156

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

  • Index Cond: (id = t_1.f_max_priority)
  • Filter: ((f_personlastname)::text ~~ 'А%'::text)
  • Rows Removed by Filter: 1
26. 1.500 2.050 ↑ 1.0 1 50

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

27. 0.250 0.250 ↑ 1.0 1 50

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

  • Index Cond: ((catalog = (p.cid)::numeric) AND (catalog IS NOT NULL))
  • Filter: ((unitcode)::text = 'orgreestr'::text)
28. 0.300 0.300 ↑ 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.006..0.006 rows=1 loops=50)

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

SubPlan (for Nested Loop Semi Join)

30. 0.050 0.800 ↑ 1.0 1 50

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

31. 0.036 0.750 ↑ 1.0 1 50

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

32. 0.072 0.600 ↑ 1.0 1 50

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

33. 0.300 0.300 ↑ 1.0 1 50

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

  • Index Cond: (f_child = t_1.f_max_priority)
34. 0.228 0.228 ↑ 1.0 1 38

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

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

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

  • Index Cond: (id = t_6.f_doc_type)
36. 0.000 0.400 ↑ 1.0 1 50

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

37. 0.024 0.400 ↑ 1.0 1 50

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

38. 0.036 0.300 ↑ 1.0 1 50

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

39. 0.150 0.150 ↑ 1.0 1 50

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

  • Index Cond: (f_child = t_1.f_max_priority)
40. 0.114 0.114 ↑ 1.0 1 38

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

  • Index Cond: (id = t_8.f_document)
  • Filter: (f_uptodate AND (NOT deleted))
41. 0.076 0.076 ↑ 1.0 1 38

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

  • Index Cond: (id = t_9.f_doc_type)
  • Heap Fetches: 38
42. 0.000 0.300 ↑ 1.0 1 50

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

43. 0.036 0.300 ↑ 1.0 1 50

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

44. 0.150 0.150 ↑ 1.0 1 50

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

  • Index Cond: (f_child = t_1.f_max_priority)
45. 0.114 0.114 ↑ 1.0 1 38

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

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

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

47. 0.036 0.250 ↑ 1.0 1 50

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

48. 0.100 0.100 ↑ 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.002..0.002 rows=1 loops=50)

  • Index Cond: (f_child = t_1.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=15) (actual time=0.003..0.003 rows=1 loops=38)

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

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

51.          

Initplan (for Subquery Scan)

52. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Filter: ((code)::text = '1'::text)
54. 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 = '2'::text)
55. 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 = '3'::text)
56. 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 = '0'::text)
57. 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 = '1'::text)
58. 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 = '2'::text)
59. 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 = '3'::text)
60. 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 = '4'::text)
61. 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
62. 0.000 0.000 ↓ 0.0 0

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

63. 0.000 0.000 ↓ 0.0 0

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

64. 0.000 0.000 ↓ 0.0 0

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

65. 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_26.catalog)
66. 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_1.id)
  • Filter: (f_uptodate AND (NOT deleted))
67. 0.000 0.000 ↓ 0.0 0

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

68. 0.000 0.000 ↓ 0.0 0

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

69. 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)
70. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on urprivs t_26 (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))
71. 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))
72. 0.000 0.000 ↓ 0.0 0

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

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

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

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

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

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

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

76.          

Initplan (for Subquery Scan)

77. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Filter: ((code)::text = '1'::text)
79. 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 = '2'::text)
80. 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 = '3'::text)
81. 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 = '0'::text)
82. 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 = '1'::text)
83. 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 = '2'::text)
84. 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 = '3'::text)
85. 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 = '4'::text)
86. 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
87. 0.000 0.000 ↓ 0.0 0

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

88. 0.000 0.000 ↓ 0.0 0

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

89. 0.000 0.000 ↓ 0.0 0

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

90. 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_39.catalog)
91. 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_1.id)
  • Filter: (f_uptodate AND (NOT deleted))
92. 0.000 0.000 ↓ 0.0 0

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

93. 0.000 0.000 ↓ 0.0 0

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

94. 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)
95. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on urprivs t_39 (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))
96. 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))
97. 0.000 0.000 ↓ 0.0 0

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

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

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

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

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

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