explain.depesz.com

PostgreSQL's explain analyze made readable

Result: X1rb

Settings
# exclusive inclusive rows x rows loops node
1. 2,410.782 6,055.655 ↓ 3,272.0 3,272 1

Nested Loop Left Join (cost=961.93..20,731.61 rows=1 width=673) (actual time=966.942..6,055.655 rows=3,272 loops=1)

  • Join Filter: (concat(vl.entity_id, vl.dimension_id, vl.variable_id, COALESCE(vl.entity_unit_id, '-1'::integer)) = concat(vp.entity_id, entity_variable_dimensions.dimension_id, variables.id, COALESCE(vp.entity_unit_id, '-1'::integer)))
  • Rows Removed by Join Filter: 848130
2.          

CTE x

3. 2.730 2.733 ↓ 30.3 3,028 1

ProjectSet (cost=0.00..0.52 rows=100 width=96) (actual time=0.009..2.733 rows=3,028 loops=1)

4. 0.003 0.003 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.003 rows=1 loops=1)

5. 11.978 1,202.693 ↓ 3,030.0 3,030 1

Nested Loop (cost=961.41..20,701.77 rows=1 width=644) (actual time=965.095..1,202.693 rows=3,030 loops=1)

  • Join Filter: (x."CodeDimension" = (dimensions.acronym)::text)
  • Rows Removed by Join Filter: 90
6. 11.800 1,181.355 ↓ 3,120.0 3,120 1

Nested Loop (cost=961.27..20,701.56 rows=1 width=160) (actual time=965.081..1,181.355 rows=3,120 loops=1)

7. 8.826 1,163.315 ↓ 3,120.0 3,120 1

Nested Loop (cost=961.13..20,701.23 rows=1 width=128) (actual time=965.067..1,163.315 rows=3,120 loops=1)

8. 7.898 1,145.129 ↓ 3,120.0 3,120 1

Hash Join (cost=960.98..20,700.90 rows=1 width=96) (actual time=965.041..1,145.129 rows=3,120 loops=1)

  • Hash Cond: ((vp.entity_id = entities.id) AND (x."CodeEntity" = (entities.code)::text))
9. 10.153 1,137.184 ↓ 21.7 3,120 1

Hash Left Join (cost=959.85..20,699.02 rows=144 width=96) (actual time=964.982..1,137.184 rows=3,120 loops=1)

  • Hash Cond: ((variables.id = "UserVariableResponsible".variable_id) AND (vp.entity_id = "UserVariableResponsible".entity_id) AND (entity_variable_dimensions.dimension_id = "UserVariableResponsible".dimension_id) AND (COALESCE(vp.entity_unit_id, 0) = COALESCE("UserVariableResponsible".entity_unit_id, 0)))
10. 9.364 172.818 ↓ 21.7 3,120 1

Hash Join (cost=29.69..19,766.68 rows=144 width=92) (actual time=10.755..172.818 rows=3,120 loops=1)

  • Hash Cond: ((variables.code)::text = x."CodeVariable")
11. 5.858 152.868 ↓ 10.7 3,086 1

Append (cost=26.44..19,760.91 rows=289 width=28) (actual time=0.160..152.868 rows=3,086 loops=1)

12. 8.958 131.624 ↓ 3,078.0 3,078 1

Nested Loop (cost=26.44..9,813.26 rows=1 width=28) (actual time=0.158..131.624 rows=3,078 loops=1)

13. 21.310 110.354 ↓ 3,078.0 3,078 1

Nested Loop (cost=26.16..9,806.29 rows=1 width=16) (actual time=0.142..110.354 rows=3,078 loops=1)

  • Join Filter: (entity_dimensions.entity_id = entity_variables.entity_id)
  • Rows Removed by Join Filter: 3030
14. 10.957 70.720 ↓ 122.2 6,108 1

Hash Join (cost=25.88..9,785.20 rows=50 width=28) (actual time=0.110..70.720 rows=6,108 loops=1)

  • Hash Cond: (entity_variable_dimensions.dimension_id = entity_dimensions.dimension_id)
15. 14.559 59.730 ↓ 146.6 3,078 1

Nested Loop (cost=0.28..9,757.79 rows=21 width=24) (actual time=0.058..59.730 rows=3,078 loops=1)

16. 5.327 20.931 ↓ 144.3 3,030 1

Subquery Scan on vp (cost=0.00..9,643.33 rows=21 width=16) (actual time=0.031..20.931 rows=3,030 loops=1)

  • Filter: (vp.entity_unit_id IS NULL)
  • Rows Removed by Filter: 3
17. 5.842 15.604 ↑ 1.4 3,033 1

Result (cost=0.00..9,601.42 rows=4,191 width=54) (actual time=0.029..15.604 rows=3,033 loops=1)

18. 4.851 9.762 ↑ 1.4 3,033 1

Append (cost=0.00..9,559.51 rows=4,191 width=16) (actual time=0.026..9.762 rows=3,033 loops=1)

19. 4.860 4.860 ↑ 1.0 3,030 1

Seq Scan on entity_variables entityvariables (cost=0.00..72.31 rows=3,031 width=16) (actual time=0.024..4.860 rows=3,030 loops=1)

20. 0.021 0.051 ↑ 386.7 3 1

Seq Scan on entity_unit_variables entityunitvariables (cost=0.00..9,487.20 rows=1,160 width=16) (actual time=0.039..0.051 rows=3 loops=1)

21.          

SubPlan (forSeq Scan)

22. 0.030 0.030 ↑ 1.0 1 3

Index Scan using "EntityUnit_pkey" on entity_units entity_units_1 (cost=0.14..8.16 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=3)

  • Index Cond: (id = entityunitvariables.entity_unit_id)
23. 24.240 24.240 ↑ 1.0 1 3,030

Index Scan using idx_entity_variable_entity_variable_dimensions on entity_variable_dimensions (cost=0.28..5.44 rows=1 width=8) (actual time=0.006..0.008 rows=1 loops=3,030)

  • Index Cond: (entity_variable_id = vp.internal_id)
24. 0.013 0.033 ↑ 96.0 5 1

Hash (cost=19.60..19.60 rows=480 width=8) (actual time=0.033..0.033 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.020 0.020 ↑ 96.0 5 1

Seq Scan on entity_dimensions (cost=0.00..19.60 rows=480 width=8) (actual time=0.013..0.020 rows=5 loops=1)

  • Filter: active
  • Rows Removed by Filter: 1
26. 18.324 18.324 ↑ 1.0 1 6,108

Index Scan using entity_variable_pkey on entity_variables (cost=0.28..0.41 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=6,108)

  • Index Cond: (id = entity_variable_dimensions.entity_variable_id)
27. 12.312 12.312 ↑ 1.0 1 3,078

Index Scan using variables_pkey on variables (cost=0.28..6.96 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=3,078)

  • Index Cond: (id = vp.variable_id)
28. 0.022 15.386 ↑ 36.0 8 1

Nested Loop (cost=172.89..9,944.76 rows=288 width=28) (actual time=15.317..15.386 rows=8 loops=1)

29. 0.040 15.332 ↑ 36.0 8 1

Hash Join (cost=172.61..9,834.50 rows=288 width=16) (actual time=15.296..15.332 rows=8 loops=1)

  • Hash Cond: (vp_1.internal_id = entity_unit_variable_dimensions.entity_unit_variable_id)
30. 2.286 15.109 ↑ 1,390.0 3 1

Subquery Scan on vp_1 (cost=0.00..9,643.33 rows=4,170 width=16) (actual time=15.083..15.109 rows=3 loops=1)

  • Filter: (vp_1.entity_unit_id IS NOT NULL)
  • Rows Removed by Filter: 3030
31. 5.142 12.823 ↑ 1.4 3,033 1

Result (cost=0.00..9,601.42 rows=4,191 width=54) (actual time=0.014..12.823 rows=3,033 loops=1)

32. 4.802 7.681 ↑ 1.4 3,033 1

Append (cost=0.00..9,559.51 rows=4,191 width=16) (actual time=0.011..7.681 rows=3,033 loops=1)

33. 2.818 2.818 ↑ 1.0 3,030 1

Seq Scan on entity_variables entityvariables_1 (cost=0.00..72.31 rows=3,031 width=16) (actual time=0.010..2.818 rows=3,030 loops=1)

34. 0.034 0.061 ↑ 386.7 3 1

Seq Scan on entity_unit_variables entityunitvariables_1 (cost=0.00..9,487.20 rows=1,160 width=16) (actual time=0.047..0.061 rows=3 loops=1)

35.          

SubPlan (forSeq Scan)

36. 0.027 0.027 ↑ 1.0 1 3

Index Scan using "EntityUnit_pkey" on entity_units entity_units_2 (cost=0.14..8.16 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=3)

  • Index Cond: (id = entityunitvariables_1.entity_unit_id)
37. 0.018 0.183 ↑ 1.8 8 1

Hash (cost=172.43..172.43 rows=14 width=12) (actual time=0.182..0.183 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 0.037 0.165 ↑ 1.8 8 1

Merge Join (cost=160.07..172.43 rows=14 width=12) (actual time=0.116..0.165 rows=8 loops=1)

  • Merge Cond: ((entity_unit_variables.entity_unit_id = entity_unit_dimensions.entity_unit_id) AND (entity_unit_variable_dimensions.dimension_id = entity_unit_dimensions.dimension_id))
39. 0.031 0.099 ↑ 143.8 8 1

Sort (cost=119.09..121.97 rows=1,150 width=16) (actual time=0.088..0.099 rows=8 loops=1)

  • Sort Key: entity_unit_variables.entity_unit_id, entity_unit_variable_dimensions.dimension_id
  • Sort Method: quicksort Memory: 25kB
40. 0.029 0.068 ↑ 143.8 8 1

Hash Join (cost=36.10..60.63 rows=1,150 width=16) (actual time=0.049..0.068 rows=8 loops=1)

  • Hash Cond: (entity_unit_variable_dimensions.entity_unit_variable_id = entity_unit_variables.id)
41. 0.014 0.014 ↑ 143.8 8 1

Seq Scan on entity_unit_variable_dimensions (cost=0.00..21.50 rows=1,150 width=8) (actual time=0.008..0.014 rows=8 loops=1)

42. 0.017 0.025 ↑ 386.7 3 1

Hash (cost=21.60..21.60 rows=1,160 width=8) (actual time=0.024..0.025 rows=3 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
43. 0.008 0.008 ↑ 386.7 3 1

Seq Scan on entity_unit_variables (cost=0.00..21.60 rows=1,160 width=8) (actual time=0.004..0.008 rows=3 loops=1)

44. 0.015 0.029 ↑ 68.6 7 1

Sort (cost=40.98..42.18 rows=480 width=8) (actual time=0.022..0.029 rows=7 loops=1)

  • Sort Key: entity_unit_dimensions.entity_unit_id, entity_unit_dimensions.dimension_id
  • Sort Method: quicksort Memory: 25kB
45. 0.014 0.014 ↑ 96.0 5 1

Seq Scan on entity_unit_dimensions (cost=0.00..19.60 rows=480 width=8) (actual time=0.009..0.014 rows=5 loops=1)

  • Filter: active
  • Rows Removed by Filter: 7
46. 0.032 0.032 ↑ 1.0 1 8

Index Scan using variables_pkey on variables variables_1 (cost=0.28..0.38 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=8)

  • Index Cond: (id = vp_1.variable_id)
47. 2.674 10.586 ↓ 30.3 3,028 1

Hash (cost=2.00..2.00 rows=100 width=96) (actual time=10.585..10.586 rows=3,028 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 162kB
48. 7.912 7.912 ↓ 30.3 3,028 1

CTE Scan on x (cost=0.00..2.00 rows=100 width=96) (actual time=0.013..7.912 rows=3,028 loops=1)

49. 2.923 954.213 ↓ 763.5 3,054 1

Hash (cost=930.08..930.08 rows=4 width=16) (actual time=954.213..954.213 rows=3,054 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 164kB
50. 4.417 951.290 ↓ 763.5 3,054 1

Subquery Scan on UserVariableResponsible (cost=929.98..930.08 rows=4 width=16) (actual time=939.616..951.290 rows=3,054 loops=1)

51. 4.992 946.873 ↓ 763.5 3,054 1

Group (cost=929.98..930.04 rows=4 width=532) (actual time=939.614..946.873 rows=3,054 loops=1)

  • Group Key: "entitiesNotDefault".entity_id, "entitiesNotDefault".entity_unit_id, "entitiesNotDefault".dimension_id, "entitiesNotDefault".variable_id, "entitiesNotDefault".user_id
52.          

CTE entitiesNotDefault

53. 0.008 0.891 ↓ 0.0 0 1

Sort (cost=23.47..23.48 rows=1 width=53) (actual time=0.890..0.891 rows=0 loops=1)

  • Sort Key: variables_2.id
  • Sort Method: quicksort Memory: 25kB
54. 0.002 0.883 ↓ 0.0 0 1

Nested Loop (cost=16.61..23.46 rows=1 width=53) (actual time=0.883..0.883 rows=0 loops=1)

55. 0.001 0.881 ↓ 0.0 0 1

Nested Loop Left Join (cost=16.46..23.24 rows=1 width=53) (actual time=0.881..0.881 rows=0 loops=1)

  • Join Filter: (u.id = COALESCE(evdr.user_id, luug.user_id))
56. 0.002 0.880 ↓ 0.0 0 1

Nested Loop Left Join (cost=16.46..22.04 rows=1 width=24) (actual time=0.879..0.880 rows=0 loops=1)

57. 0.003 0.878 ↓ 0.0 0 1

Merge Join (cost=13.21..17.25 rows=1 width=24) (actual time=0.877..0.878 rows=0 loops=1)

  • Merge Cond: (evd.id = evdr.entity_variable_dimension_id)
  • Join Filter: (ugg.id = evdr.user_group_id)
58. 0.001 0.875 ↓ 0.0 0 1

Nested Loop (cost=0.84..198.82 rows=50 width=24) (actual time=0.875..0.875 rows=0 loops=1)

59. 0.002 0.874 ↓ 0.0 0 1

Nested Loop (cost=0.84..187.32 rows=1 width=20) (actual time=0.873..0.874 rows=0 loops=1)

60. 0.002 0.872 ↓ 0.0 0 1

Nested Loop (cost=0.56..186.91 rows=1 width=16) (actual time=0.871..0.872 rows=0 loops=1)

61. 0.870 0.870 ↓ 0.0 0 1

Index Scan using entity_variable_dimensions_pkey on entity_variable_dimensions evd (cost=0.28..178.61 rows=1 width=12) (actual time=0.869..0.870 rows=0 loops=1)

  • Filter: (NOT default_responsibles)
  • Rows Removed by Filter: 3078
62. 0.000 0.000 ↓ 0.0 0

Index Scan using entity_variable_pkey on entity_variables ev (cost=0.28..8.30 rows=1 width=12) (never executed)

  • Index Cond: (id = evd.entity_variable_id)
63. 0.000 0.000 ↓ 0.0 0

Index Scan using variables_pkey on variables variables_2 (cost=0.28..0.41 rows=1 width=8) (never executed)

  • Index Cond: (id = ev.variable_id)
64. 0.000 0.000 ↓ 0.0 0

Seq Scan on user_groups ugg (cost=0.00..11.00 rows=50 width=4) (never executed)

  • Filter: active
65. 0.000 0.000 ↓ 0.0 0

Sort (cost=12.37..12.40 rows=13 width=12) (never executed)

  • Sort Key: evdr.entity_variable_dimension_id
66. 0.000 0.000 ↓ 0.0 0

Seq Scan on entity_variable_dimension_responsibles evdr (cost=0.00..12.13 rows=13 width=12) (never executed)

67. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=3.25..4.78 rows=1 width=8) (never executed)

  • Hash Cond: (luug.user_group_id = ug.id)
68. 0.000 0.000 ↓ 0.0 0

Seq Scan on lnk_user_user_groups luug (cost=0.00..1.42 rows=42 width=8) (never executed)

69. 0.000 0.000 ↓ 0.0 0

Hash (cost=3.24..3.24 rows=1 width=4) (never executed)

70. 0.000 0.000 ↓ 0.0 0

Index Only Scan using user_groups_pkey on user_groups ug (cost=0.14..3.24 rows=1 width=4) (never executed)

  • Index Cond: (id = evdr.user_group_id)
  • Heap Fetches: 0
71. 0.000 0.000 ↓ 0.0 0

Seq Scan on users u (cost=0.00..1.09 rows=9 width=41) (never executed)

72. 0.000 0.000 ↓ 0.0 0

Index Scan using variable_types_pkey on variable_types vt (cost=0.15..0.18 rows=1 width=4) (never executed)

  • Index Cond: (id = variables_2.type_id)
  • Filter: ((system_slug)::text = 'base'::text)
73.          

CTE entitiesDefault

74. 184.125 891.910 ↓ 6,253.0 6,253 1

Nested Loop (cost=78.94..349.29 rows=1 width=53) (actual time=2.138..891.910 rows=6,253 loops=1)

  • Join Filter: (ed.dimension_id = evd_1.dimension_id)
  • Rows Removed by Join Filter: 57581
75. 146.544 517.513 ↓ 63,424.0 63,424 1

Nested Loop (cost=78.66..348.92 rows=1 width=53) (actual time=2.119..517.513 rows=63,424 loops=1)

76. 111.231 180.634 ↓ 1,762.4 63,445 1

Hash Join (cost=78.38..324.32 rows=36 width=49) (actual time=2.098..180.634 rows=63,445 loops=1)

  • Hash Cond: (variables_3.type_id = vt_1.id)
77. 64.372 69.382 ↓ 6.3 63,676 1

Hash Join (cost=64.87..283.78 rows=10,146 width=53) (actual time=2.068..69.382 rows=63,676 loops=1)

  • Hash Cond: (variables_3.family_id = edf.family_id)
78. 2.958 2.958 ↑ 1.0 3,033 1

Seq Scan on variables variables_3 (cost=0.00..83.33 rows=3,033 width=12) (actual time=0.007..2.958 rows=3,033 loops=1)

79. 0.296 2.052 ↓ 5.2 237 1

Hash (cost=64.29..64.29 rows=46 width=49) (actual time=2.052..2.052 rows=237 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
80. 0.454 1.756 ↓ 5.2 237 1

Hash Left Join (cost=60.86..64.29 rows=46 width=49) (actual time=0.413..1.756 rows=237 loops=1)

  • Hash Cond: (COALESCE(ledfr.user_id, luug_1.user_id) = u_1.id)
81. 0.326 1.251 ↓ 5.2 237 1

Hash Left Join (cost=59.66..62.97 rows=46 width=20) (actual time=0.355..1.251 rows=237 loops=1)

  • Hash Cond: (ug_1.id = luug_1.user_group_id)
82. 0.124 0.844 ↓ 1.4 65 1

Hash Left Join (cost=57.71..60.26 rows=46 width=20) (actual time=0.267..0.844 rows=65 loops=1)

  • Hash Cond: (ledfr.user_group_id = ug_1.id)
83. 0.129 0.677 ↓ 1.4 65 1

Hash Join (cost=45.46..47.89 rows=46 width=20) (actual time=0.216..0.677 rows=65 loops=1)

  • Hash Cond: (edf.entity_dimension_id = ed.id)
84. 0.128 0.529 ↓ 1.4 65 1

Hash Join (cost=13.86..16.17 rows=46 width=16) (actual time=0.190..0.529 rows=65 loops=1)

  • Hash Cond: (ledfr.entity_dimension_family_id = edf.id)
85. 0.153 0.277 ↓ 1.4 65 1

Hash Join (cost=11.63..13.80 rows=46 width=12) (actual time=0.058..0.277 rows=65 loops=1)

  • Hash Cond: (ledfr.user_group_id = ugg_1.id)
86. 0.087 0.087 ↑ 1.0 92 1

Seq Scan on lnk_entity_dimension_family_responsibles ledfr (cost=0.00..1.92 rows=92 width=12) (actual time=0.009..0.087 rows=92 loops=1)

87. 0.016 0.037 ↑ 3.3 15 1

Hash (cost=11.00..11.00 rows=50 width=4) (actual time=0.037..0.037 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
88. 0.021 0.021 ↑ 3.3 15 1

Seq Scan on user_groups ugg_1 (cost=0.00..11.00 rows=50 width=4) (actual time=0.008..0.021 rows=15 loops=1)

  • Filter: active
  • Rows Removed by Filter: 1
89. 0.060 0.124 ↑ 1.0 55 1

Hash (cost=1.55..1.55 rows=55 width=12) (actual time=0.123..0.124 rows=55 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
90. 0.064 0.064 ↑ 1.0 55 1

Seq Scan on entity_dimension_families edf (cost=0.00..1.55 rows=55 width=12) (actual time=0.014..0.064 rows=55 loops=1)

91. 0.009 0.019 ↑ 160.0 6 1

Hash (cost=19.60..19.60 rows=960 width=12) (actual time=0.018..0.019 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
92. 0.010 0.010 ↑ 160.0 6 1

Seq Scan on entity_dimensions ed (cost=0.00..19.60 rows=960 width=12) (actual time=0.005..0.010 rows=6 loops=1)

93. 0.027 0.043 ↑ 6.2 16 1

Hash (cost=11.00..11.00 rows=100 width=4) (actual time=0.042..0.043 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
94. 0.016 0.016 ↑ 6.2 16 1

Seq Scan on user_groups ug_1 (cost=0.00..11.00 rows=100 width=4) (actual time=0.004..0.016 rows=16 loops=1)

95. 0.038 0.081 ↑ 1.0 42 1

Hash (cost=1.42..1.42 rows=42 width=8) (actual time=0.081..0.081 rows=42 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
96. 0.043 0.043 ↑ 1.0 42 1

Seq Scan on lnk_user_user_groups luug_1 (cost=0.00..1.42 rows=42 width=8) (actual time=0.011..0.043 rows=42 loops=1)

97. 0.025 0.051 ↓ 2.6 23 1

Hash (cost=1.09..1.09 rows=9 width=41) (actual time=0.050..0.051 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
98. 0.026 0.026 ↓ 2.6 23 1

Seq Scan on users u_1 (cost=0.00..1.09 rows=9 width=41) (actual time=0.009..0.026 rows=23 loops=1)

99. 0.008 0.021 ↑ 1.0 1 1

Hash (cost=13.50..13.50 rows=1 width=4) (actual time=0.020..0.021 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
100. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on variable_types vt_1 (cost=0.00..13.50 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=1)

  • Filter: ((system_slug)::text = 'base'::text)
  • Rows Removed by Filter: 1
101. 190.335 190.335 ↑ 1.0 1 63,445

Index Scan using entity_variables_entity_id_variable_id_key on entity_variables ev_1 (cost=0.28..0.68 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=63,445)

  • Index Cond: ((entity_id = ed.entity_id) AND (variable_id = variables_3.id))
102. 190.272 190.272 ↑ 1.0 1 63,424

Index Scan using idx_entity_variable_entity_variable_dimensions on entity_variable_dimensions evd_1 (cost=0.28..0.36 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=63,424)

  • Index Cond: (entity_variable_id = ev_1.id)
  • Filter: default_responsibles
103.          

CTE entitiesUnitNotDefault

104. 0.058 10.335 ↓ 0.0 0 1

Hash Join (cost=210.41..243.35 rows=1 width=53) (actual time=10.334..10.335 rows=0 loops=1)

  • Hash Cond: (ev_2.variable_id = variables_4.id)
105. 0.016 0.133 ↓ 0.0 0 1

Hash Join (cost=105.35..137.15 rows=300 width=53) (actual time=0.132..0.133 rows=0 loops=1)

  • Hash Cond: (ev_2.entity_unit_id = eu.id)
106. 0.001 0.085 ↓ 0.0 0 1

Hash Left Join (cost=92.65..123.64 rows=300 width=49) (actual time=0.085..0.085 rows=0 loops=1)

  • Hash Cond: (COALESCE(evdr_1.user_id, luug_2.user_id) = u_2.id)
107. 0.008 0.084 ↓ 0.0 0 1

Hash Join (cost=91.44..121.62 rows=300 width=20) (actual time=0.083..0.084 rows=0 loops=1)

  • Hash Cond: (evd_2.entity_unit_variable_id = ev_2.id)
108. 0.001 0.057 ↓ 0.0 0 1

Hash Left Join (cost=55.34..84.74 rows=300 width=16) (actual time=0.057..0.057 rows=0 loops=1)

  • Hash Cond: (evdr_1.user_group_id = ug_2.id)
109. 0.009 0.056 ↓ 0.0 0 1

Hash Join (cost=40.31..67.08 rows=300 width=16) (actual time=0.055..0.056 rows=0 loops=1)

  • Hash Cond: (evdr_1.user_group_id = ugg_2.id)
110. 0.002 0.009 ↓ 0.0 0 1

Hash Join (cost=28.69..53.85 rows=600 width=16) (actual time=0.009..0.009 rows=0 loops=1)

  • Hash Cond: (evdr_1.entity_unit_variable_dimension_id = evd_2.id)
111. 0.007 0.007 ↓ 0.0 0 1

Seq Scan on entity_unit_variable_dimension_responsibles evdr_1 (cost=0.00..22.00 rows=1,200 width=12) (actual time=0.007..0.007 rows=0 loops=1)

112. 0.000 0.000 ↓ 0.0 0

Hash (cost=21.50..21.50 rows=575 width=12) (never executed)

113. 0.000 0.000 ↓ 0.0 0

Seq Scan on entity_unit_variable_dimensions evd_2 (cost=0.00..21.50 rows=575 width=12) (never executed)

  • Filter: (NOT default_responsibles)
114. 0.018 0.038 ↑ 3.3 15 1

Hash (cost=11.00..11.00 rows=50 width=4) (actual time=0.037..0.038 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
115. 0.020 0.020 ↑ 3.3 15 1

Seq Scan on user_groups ugg_2 (cost=0.00..11.00 rows=50 width=4) (actual time=0.007..0.020 rows=15 loops=1)

  • Filter: active
  • Rows Removed by Filter: 1
116. 0.000 0.000 ↓ 0.0 0

Hash (cost=13.78..13.78 rows=100 width=8) (never executed)

117. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=12.25..13.78 rows=100 width=8) (never executed)

  • Hash Cond: (luug_2.user_group_id = ug_2.id)
118. 0.000 0.000 ↓ 0.0 0

Seq Scan on lnk_user_user_groups luug_2 (cost=0.00..1.42 rows=42 width=8) (never executed)

119. 0.000 0.000 ↓ 0.0 0

Hash (cost=11.00..11.00 rows=100 width=4) (never executed)

120. 0.000 0.000 ↓ 0.0 0

Seq Scan on user_groups ug_2 (cost=0.00..11.00 rows=100 width=4) (never executed)

121. 0.009 0.019 ↑ 386.7 3 1

Hash (cost=21.60..21.60 rows=1,160 width=12) (actual time=0.018..0.019 rows=3 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
122. 0.010 0.010 ↑ 386.7 3 1

Seq Scan on entity_unit_variables ev_2 (cost=0.00..21.60 rows=1,160 width=12) (actual time=0.007..0.010 rows=3 loops=1)

123. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.09..1.09 rows=9 width=41) (never executed)

124. 0.000 0.000 ↓ 0.0 0

Seq Scan on users u_2 (cost=0.00..1.09 rows=9 width=41) (never executed)

125. 0.013 0.032 ↑ 15.0 8 1

Hash (cost=11.20..11.20 rows=120 width=8) (actual time=0.031..0.032 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
126. 0.019 0.019 ↑ 15.0 8 1

Seq Scan on entity_units eu (cost=0.00..11.20 rows=120 width=8) (actual time=0.012..0.019 rows=8 loops=1)

127. 2.619 10.144 ↓ 274.7 3,022 1

Hash (cost=104.93..104.93 rows=11 width=4) (actual time=10.143..10.144 rows=3,022 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 139kB
128. 5.053 7.525 ↓ 274.7 3,022 1

Hash Join (cost=13.51..104.93 rows=11 width=4) (actual time=0.039..7.525 rows=3,022 loops=1)

  • Hash Cond: (variables_4.type_id = vt_2.id)
129. 2.457 2.457 ↑ 1.0 3,033 1

Seq Scan on variables variables_4 (cost=0.00..83.33 rows=3,033 width=8) (actual time=0.014..2.457 rows=3,033 loops=1)

130. 0.006 0.015 ↑ 1.0 1 1

Hash (cost=13.50..13.50 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
131. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on variable_types vt_2 (cost=0.00..13.50 rows=1 width=4) (actual time=0.007..0.009 rows=1 loops=1)

  • Filter: ((system_slug)::text = 'base'::text)
  • Rows Removed by Filter: 1
132.          

CTE entitiesUnitDefault

133. 0.002 0.175 ↓ 0.0 0 1

Nested Loop (cost=208.01..313.57 rows=1 width=53) (actual time=0.175..0.175 rows=0 loops=1)

134. 0.001 0.173 ↓ 0.0 0 1

Nested Loop (cost=207.86..313.35 rows=1 width=57) (actual time=0.173..0.173 rows=0 loops=1)

135. 0.008 0.172 ↓ 0.0 0 1

Hash Join (cost=207.70..301.11 rows=55 width=57) (actual time=0.171..0.172 rows=0 loops=1)

  • Hash Cond: (variables_5.family_id = edf_1.family_id)
136. 0.009 0.031 ↑ 11.0 1 1

Hash Join (cost=13.51..104.93 rows=11 width=8) (actual time=0.030..0.031 rows=1 loops=1)

  • Hash Cond: (variables_5.type_id = vt_3.id)
137. 0.007 0.007 ↑ 3,033.0 1 1

Seq Scan on variables variables_5 (cost=0.00..83.33 rows=3,033 width=12) (actual time=0.006..0.007 rows=1 loops=1)

138. 0.007 0.015 ↑ 1.0 1 1

Hash (cost=13.50..13.50 rows=1 width=4) (actual time=0.014..0.015 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
139. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on variable_types vt_3 (cost=0.00..13.50 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=1)

  • Filter: ((system_slug)::text = 'base'::text)
  • Rows Removed by Filter: 1
140. 0.002 0.133 ↓ 0.0 0 1

Hash (cost=181.44..181.44 rows=1,020 width=57) (actual time=0.132..0.133 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
141. 0.001 0.131 ↓ 0.0 0 1

Hash Left Join (cost=125.81..181.44 rows=1,020 width=57) (actual time=0.131..0.131 rows=0 loops=1)

  • Hash Cond: (COALESCE(ledfr_1.user_id, luug_3.user_id) = u_3.id)
142. 0.055 0.130 ↓ 0.0 0 1

Hash Join (cost=124.61..177.51 rows=1,020 width=28) (actual time=0.129..0.130 rows=0 loops=1)

  • Hash Cond: (ed_1.entity_unit_id = eu_1.id)
143. 0.010 0.053 ↓ 0.0 0 1

Hash Join (cost=111.91..162.07 rows=1,020 width=20) (actual time=0.052..0.053 rows=0 loops=1)

  • Hash Cond: (edf_1.entity_unit_dimension_id = ed_1.id)
144. 0.002 0.010 ↓ 0.0 0 1

Hash Join (cost=80.31..127.79 rows=1,020 width=16) (actual time=0.009..0.010 rows=0 loops=1)

  • Hash Cond: (ledfr_1.entity_unit_dimension_family_id = edf_1.id)
145. 0.002 0.008 ↓ 0.0 0 1

Hash Left Join (cost=26.66..71.45 rows=1,020 width=12) (actual time=0.007..0.008 rows=0 loops=1)

  • Hash Cond: (ledfr_1.user_group_id = ug_3.id)
146. 0.001 0.006 ↓ 0.0 0 1

Hash Join (cost=11.63..47.49 rows=1,020 width=12) (actual time=0.006..0.006 rows=0 loops=1)

  • Hash Cond: (ledfr_1.user_group_id = ugg_3.id)
147. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on lnk_entity_unit_dimension_family_responsibles ledfr_1 (cost=0.00..30.40 rows=2,040 width=12) (actual time=0.004..0.005 rows=0 loops=1)

148. 0.000 0.000 ↓ 0.0 0

Hash (cost=11.00..11.00 rows=50 width=4) (never executed)

149. 0.000 0.000 ↓ 0.0 0

Seq Scan on user_groups ugg_3 (cost=0.00..11.00 rows=50 width=4) (never executed)

  • Filter: active
150. 0.000 0.000 ↓ 0.0 0

Hash (cost=13.78..13.78 rows=100 width=8) (never executed)

151. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=12.25..13.78 rows=100 width=8) (never executed)

  • Hash Cond: (luug_3.user_group_id = ug_3.id)
152. 0.000 0.000 ↓ 0.0 0

Seq Scan on lnk_user_user_groups luug_3 (cost=0.00..1.42 rows=42 width=8) (never executed)

153. 0.000 0.000 ↓ 0.0 0

Hash (cost=11.00..11.00 rows=100 width=4) (never executed)

154. 0.000 0.000 ↓ 0.0 0

Seq Scan on user_groups ug_3 (cost=0.00..11.00 rows=100 width=4) (never executed)

155. 0.000 0.000 ↓ 0.0 0

Hash (cost=29.40..29.40 rows=1,940 width=12) (never executed)

156. 0.000 0.000 ↓ 0.0 0

Seq Scan on entity_unit_dimension_families edf_1 (cost=0.00..29.40 rows=1,940 width=12) (never executed)

157. 0.016 0.033 ↑ 80.0 12 1

Hash (cost=19.60..19.60 rows=960 width=12) (actual time=0.033..0.033 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
158. 0.017 0.017 ↑ 80.0 12 1

Seq Scan on entity_unit_dimensions ed_1 (cost=0.00..19.60 rows=960 width=12) (actual time=0.007..0.017 rows=12 loops=1)

159. 0.011 0.022 ↑ 15.0 8 1

Hash (cost=11.20..11.20 rows=120 width=8) (actual time=0.021..0.022 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
160. 0.011 0.011 ↑ 15.0 8 1

Seq Scan on entity_units eu_1 (cost=0.00..11.20 rows=120 width=8) (actual time=0.005..0.011 rows=8 loops=1)

161. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.09..1.09 rows=9 width=41) (never executed)

162. 0.000 0.000 ↓ 0.0 0

Seq Scan on users u_3 (cost=0.00..1.09 rows=9 width=41) (never executed)

163. 0.000 0.000 ↓ 0.0 0

Index Scan using entity_unit_variables_entity_unit_id_variable_id_key on entity_unit_variables ev_3 (cost=0.15..0.22 rows=1 width=12) (never executed)

  • Index Cond: ((entity_unit_id = ed_1.entity_unit_id) AND (variable_id = variables_5.id))
164. 0.000 0.000 ↓ 0.0 0

Index Scan using entity_unit_variable_dimensi_entity_unit_variable_id_dime_key on entity_unit_variable_dimensions evd_3 (cost=0.15..0.22 rows=1 width=8) (never executed)

  • Index Cond: ((entity_unit_variable_id = ev_3.id) AND (dimension_id = ed_1.dimension_id))
  • Filter: default_responsibles
165. 10.584 941.881 ↓ 763.5 3,054 1

Sort (cost=0.30..0.31 rows=4 width=532) (actual time=939.610..941.881 rows=3,054 loops=1)

  • Sort Key: "entitiesNotDefault".entity_id, "entitiesNotDefault".entity_unit_id, "entitiesNotDefault".dimension_id, "entitiesNotDefault".variable_id
  • Sort Method: quicksort Memory: 526kB
166. 9.998 931.297 ↓ 763.5 3,054 1

HashAggregate (cost=0.18..0.22 rows=4 width=532) (actual time=928.230..931.297 rows=3,054 loops=1)

  • Group Key: "entitiesNotDefault".entity_id, "entitiesNotDefault".entity_unit_id, "entitiesNotDefault".dimension_id, "entitiesNotDefault".variable_id, "entitiesNotDefault".user_id
167. 5.001 921.299 ↓ 769.0 3,076 1

Append (cost=0.00..0.13 rows=4 width=532) (actual time=3.038..921.299 rows=3,076 loops=1)

168. 0.893 0.893 ↓ 0.0 0 1

CTE Scan on "entitiesNotDefault" (cost=0.00..0.02 rows=1 width=532) (actual time=0.892..0.893 rows=0 loops=1)

  • Filter: ((user_id)::text = '79f30646-d7b1-11e8-8dbb-f2801f1b9fd1'::text)
169. 904.890 904.890 ↓ 3,076.0 3,076 1

CTE Scan on "entitiesDefault" (cost=0.00..0.02 rows=1 width=532) (actual time=2.143..904.890 rows=3,076 loops=1)

  • Filter: ((user_id)::text = '79f30646-d7b1-11e8-8dbb-f2801f1b9fd1'::text)
  • Rows Removed by Filter: 3177
170. 10.337 10.337 ↓ 0.0 0 1

CTE Scan on "entitiesUnitNotDefault" (cost=0.00..0.02 rows=1 width=532) (actual time=10.337..10.337 rows=0 loops=1)

  • Filter: ((user_id)::text = '79f30646-d7b1-11e8-8dbb-f2801f1b9fd1'::text)
171. 0.178 0.178 ↓ 0.0 0 1

CTE Scan on "entitiesUnitDefault" (cost=0.00..0.02 rows=1 width=532) (actual time=0.177..0.178 rows=0 loops=1)

  • Filter: ((user_id)::text = '79f30646-d7b1-11e8-8dbb-f2801f1b9fd1'::text)
172. 0.018 0.047 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=36) (actual time=0.047..0.047 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
173. 0.029 0.029 ↑ 1.0 5 1

Seq Scan on entities (cost=0.00..1.05 rows=5 width=36) (actual time=0.023..0.029 rows=5 loops=1)

174. 9.360 9.360 ↑ 1.0 1 3,120

Index Scan using variable_types_pkey on variable_types (cost=0.15..0.33 rows=1 width=36) (actual time=0.003..0.003 rows=1 loops=3,120)

  • Index Cond: (id = variables.type_id)
175. 6.240 6.240 ↑ 1.0 1 3,120

Index Scan using nature_pkey on natures nature (cost=0.15..0.33 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=3,120)

  • Index Cond: (id = variables.nature_id)
176. 9.360 9.360 ↑ 1.0 1 3,120

Index Scan using dimensions_pkey on dimensions (cost=0.14..0.19 rows=1 width=520) (actual time=0.003..0.003 rows=1 loops=3,120)

  • Index Cond: (id = entity_variable_dimensions.dimension_id)
177. 1,275.630 2,442.180 ↑ 1.0 280 3,030

Append (cost=0.00..16.24 rows=281 width=16) (actual time=0.007..0.806 rows=280 loops=3,030)

178. 733.260 733.260 ↑ 1.0 174 3,030

Seq Scan on variables_values_2018 vl (cost=0.00..10.00 rows=175 width=16) (actual time=0.006..0.242 rows=174 loops=3,030)

  • Filter: ((year >= 2018) AND (year <= 2019) AND (year_month >= '201801'::numeric) AND (year_month <= '201912'::numeric))
  • Rows Removed by Filter: 26
179. 433.290 433.290 ↑ 1.0 106 3,030

Seq Scan on variables_values_2019 vl_1 (cost=0.00..6.24 rows=106 width=16) (actual time=0.003..0.143 rows=106 loops=3,030)

  • Filter: ((year >= 2018) AND (year <= 2019) AND (year_month >= '201801'::numeric) AND (year_month <= '201912'::numeric))
  • Rows Removed by Filter: 6
180.          

SubPlan (forNested Loop Left Join)

181. 0.000 0.000 ↓ 0.0 0

Index Only Scan using "EntityUnit_pkey" on entity_units (cost=0.14..8.16 rows=1 width=32) (never executed)

  • Index Cond: (id = vp.entity_unit_id)
  • Heap Fetches: 0