explain.depesz.com

PostgreSQL's explain analyze made readable

Result: w1z

Settings
# exclusive inclusive rows x rows loops node
1. 24.249 364,208.283 ↓ 3,272.0 3,272 1

Nested Loop Left Join (cost=958.81..20,730.83 rows=1 width=673) (actual time=873.518..364,208.283 rows=3,272 loops=1)

2.          

CTE x

3. 13.261 13.264 ↓ 30.3 3,028 1

ProjectSet (cost=0.00..0.52 rows=100 width=96) (actual time=0.009..13.264 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.002..0.003 rows=1 loops=1)

5. 13.253 364,050.714 ↓ 3,030.0 3,030 1

Nested Loop (cost=958.15..20,717.64 rows=1 width=644) (actual time=873.480..364,050.714 rows=3,030 loops=1)

6. 20.874 364,019.281 ↓ 3,030.0 3,030 1

Nested Loop (cost=958.00..20,717.30 rows=1 width=612) (actual time=873.467..364,019.281 rows=3,030 loops=1)

7. 8,072.356 363,983.257 ↓ 3,030.0 3,030 1

Nested Loop (cost=957.85..20,716.97 rows=1 width=580) (actual time=873.447..363,983.257 rows=3,030 loops=1)

  • Join Filter: ((vp.entity_id = entities.id) AND (entity_variable_dimensions.dimension_id = dimensions.id) AND ((variables.code)::text = x."CodeVariable"))
  • Rows Removed by Join Filter: 9341378
8. 27.351 99.705 ↓ 3,028.0 3,028 1

Nested Loop (cost=1.25..16.50 rows=1 width=588) (actual time=0.087..99.705 rows=3,028 loops=1)

  • Join Filter: (x."CodeDimension" = (dimensions.acronym)::text)
  • Rows Removed by Join Filter: 24224
9. 0.048 0.048 ↑ 1.0 9 1

Index Scan using dimensions_pkey on dimensions (cost=0.14..12.27 rows=9 width=520) (actual time=0.016..0.048 rows=9 loops=1)

10. 28.304 72.306 ↓ 605.6 3,028 9

Materialize (cost=1.11..3.56 rows=5 width=100) (actual time=0.008..8.034 rows=3,028 loops=9)

11. 14.455 44.002 ↓ 605.6 3,028 1

Hash Join (cost=1.11..3.54 rows=5 width=100) (actual time=0.057..44.002 rows=3,028 loops=1)

  • Hash Cond: (x."CodeEntity" = (entities.code)::text)
12. 29.512 29.512 ↓ 30.3 3,028 1

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

13. 0.009 0.035 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.026 0.026 ↑ 1.0 5 1

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

15. 19,680.978 355,811.196 ↓ 10.7 3,086 3,028

Hash Left Join (cost=956.60..20,695.42 rows=289 width=32) (actual time=0.343..117.507 rows=3,086 loops=3,028)

  • 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)))
16. 13,538.188 335,257.132 ↓ 10.7 3,086 3,028

Append (cost=26.44..19,760.91 rows=289 width=28) (actual time=0.051..110.719 rows=3,086 loops=3,028)

17. 27,645.640 279,420.812 ↓ 3,078.0 3,078 3,028

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

18. 42,837.116 233,134.804 ↓ 3,078.0 3,078 3,028

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

  • Join Filter: (entity_dimensions.entity_id = entity_variables.entity_id)
  • Rows Removed by Join Filter: 3030
19. 23,863.647 134,812.616 ↓ 122.2 6,108 3,028

Hash Join (cost=25.88..9,785.20 rows=50 width=28) (actual time=0.025..44.522 rows=6,108 loops=3,028)

  • Hash Cond: (entity_variable_dimensions.dimension_id = entity_dimensions.dimension_id)
20. 33,889.376 110,948.948 ↓ 146.6 3,078 3,028

Nested Loop (cost=0.28..9,757.79 rows=21 width=24) (actual time=0.022..36.641 rows=3,078 loops=3,028)

21. 13,541.216 49,535.052 ↓ 144.3 3,030 3,028

Subquery Scan on vp (cost=0.00..9,643.33 rows=21 width=16) (actual time=0.011..16.359 rows=3,030 loops=3,028)

  • Filter: (vp.entity_unit_id IS NULL)
  • Rows Removed by Filter: 3
22. 14,334.552 35,993.836 ↑ 1.4 3,033 3,028

Result (cost=0.00..9,601.42 rows=4,191 width=54) (actual time=0.009..11.887 rows=3,033 loops=3,028)

23. 13,138.492 21,659.284 ↑ 1.4 3,033 3,028

Append (cost=0.00..9,559.51 rows=4,191 width=16) (actual time=0.007..7.153 rows=3,033 loops=3,028)

24. 8,426.924 8,426.924 ↑ 1.0 3,030 3,028

Seq Scan on entity_variables entityvariables (cost=0.00..72.31 rows=3,031 width=16) (actual time=0.006..2.783 rows=3,030 loops=3,028)

25. 57.532 93.868 ↑ 386.7 3 3,028

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

26.          

SubPlan (forSeq Scan)

27. 36.336 36.336 ↑ 1.0 1 9,084

Index Scan using "EntityUnit_pkey" on entity_units entity_units_1 (cost=0.14..8.16 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=9,084)

  • Index Cond: (id = entityunitvariables.entity_unit_id)
28. 27,524.520 27,524.520 ↑ 1.0 1 9,174,840

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.003..0.003 rows=1 loops=9,174,840)

  • Index Cond: (entity_variable_id = vp.internal_id)
29. 0.009 0.021 ↑ 96.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.012 0.012 ↑ 96.0 5 1

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

  • Filter: active
  • Rows Removed by Filter: 1
31. 55,485.072 55,485.072 ↑ 1.0 1 18,495,024

Index Scan using entity_variable_pkey on entity_variables (cost=0.28..0.41 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=18,495,024)

  • Index Cond: (id = entity_variable_dimensions.entity_variable_id)
32. 18,640.368 18,640.368 ↑ 1.0 1 9,320,184

Index Scan using variables_pkey on variables (cost=0.28..6.96 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=9,320,184)

  • Index Cond: (id = vp.variable_id)
33. 81.756 42,298.132 ↑ 36.0 8 3,028

Nested Loop (cost=172.89..9,944.76 rows=288 width=28) (actual time=13.894..13.969 rows=8 loops=3,028)

34. 48.263 42,143.704 ↑ 36.0 8 3,028

Hash Join (cost=172.61..9,834.50 rows=288 width=16) (actual time=13.881..13.918 rows=8 loops=3,028)

  • Hash Cond: (vp_1.internal_id = entity_unit_variable_dimensions.entity_unit_variable_id)
35. 6,864.476 42,095.256 ↑ 1,390.0 3 3,028

Subquery Scan on vp_1 (cost=0.00..9,643.33 rows=4,170 width=16) (actual time=13.875..13.902 rows=3 loops=3,028)

  • Filter: (vp_1.entity_unit_id IS NOT NULL)
  • Rows Removed by Filter: 3030
36. 13,965.136 35,230.780 ↑ 1.4 3,033 3,028

Result (cost=0.00..9,601.42 rows=4,191 width=54) (actual time=0.010..11.635 rows=3,033 loops=3,028)

37. 13,077.932 21,265.644 ↑ 1.4 3,033 3,028

Append (cost=0.00..9,559.51 rows=4,191 width=16) (actual time=0.007..7.023 rows=3,033 loops=3,028)

38. 8,069.620 8,069.620 ↑ 1.0 3,030 3,028

Seq Scan on entity_variables entityvariables_1 (cost=0.00..72.31 rows=3,031 width=16) (actual time=0.006..2.665 rows=3,030 loops=3,028)

39. 63.588 118.092 ↑ 386.7 3 3,028

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

40.          

SubPlan (forSeq Scan)

41. 54.504 54.504 ↑ 1.0 1 9,084

Index Scan using "EntityUnit_pkey" on entity_units entity_units_2 (cost=0.14..8.16 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=9,084)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 0.024 0.167 ↑ 1.8 8 1

Merge Join (cost=160.07..172.43 rows=14 width=12) (actual time=0.137..0.167 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))
44. 0.032 0.108 ↑ 143.8 8 1

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

  • Sort Key: entity_unit_variables.entity_unit_id, entity_unit_variable_dimensions.dimension_id
  • Sort Method: quicksort Memory: 25kB
45. 0.036 0.076 ↑ 143.8 8 1

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

  • Hash Cond: (entity_unit_variable_dimensions.entity_unit_variable_id = entity_unit_variables.id)
46. 0.016 0.016 ↑ 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.016 rows=8 loops=1)

47. 0.017 0.024 ↑ 386.7 3 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
48. 0.007 0.007 ↑ 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.007 rows=3 loops=1)

49. 0.019 0.035 ↑ 68.6 7 1

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

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

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

  • Filter: active
  • Rows Removed by Filter: 7
51. 72.672 72.672 ↑ 1.0 1 24,224

Index Scan using variables_pkey on variables variables_1 (cost=0.28..0.38 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=24,224)

  • Index Cond: (id = vp_1.variable_id)
52. 2.967 873.086 ↓ 763.5 3,054 1

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

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 164kB
53. 4.487 870.119 ↓ 763.5 3,054 1

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

54. 5.000 865.632 ↓ 763.5 3,054 1

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

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

CTE entitiesNotDefault

56. 0.005 0.806 ↓ 0.0 0 1

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

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

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

58. 0.001 0.799 ↓ 0.0 0 1

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

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

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

60. 0.002 0.796 ↓ 0.0 0 1

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

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

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

62. 0.002 0.793 ↓ 0.0 0 1

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

63. 0.002 0.791 ↓ 0.0 0 1

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

64. 0.789 0.789 ↓ 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.789..0.789 rows=0 loops=1)

  • Filter: (NOT default_responsibles)
  • Rows Removed by Filter: 3078
65. 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)
66. 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)
67. 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
68. 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
69. 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)

70. 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)
71. 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)

72. 0.000 0.000 ↓ 0.0 0

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

73. 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
74. 0.000 0.000 ↓ 0.0 0

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

75. 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)
76.          

CTE entitiesDefault

77. 148.141 814.200 ↓ 6,253.0 6,253 1

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

  • Join Filter: (ed.dimension_id = evd_1.dimension_id)
  • Rows Removed by Join Filter: 57581
78. 179.837 475.787 ↓ 63,424.0 63,424 1

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

79. 103.722 169.060 ↓ 1,762.4 63,445 1

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

  • Hash Cond: (variables_3.type_id = vt_1.id)
80. 60.482 65.320 ↓ 6.3 63,676 1

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

  • Hash Cond: (variables_3.family_id = edf.family_id)
81. 2.786 2.786 ↑ 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.786 rows=3,033 loops=1)

82. 0.228 2.052 ↓ 5.2 237 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
83. 0.526 1.824 ↓ 5.2 237 1

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

  • Hash Cond: (COALESCE(ledfr.user_id, luug_1.user_id) = u_1.id)
84. 0.286 1.242 ↓ 5.2 237 1

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

  • Hash Cond: (ug_1.id = luug_1.user_group_id)
85. 0.112 0.879 ↓ 1.4 65 1

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

  • Hash Cond: (ledfr.user_group_id = ug_1.id)
86. 0.242 0.726 ↓ 1.4 65 1

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

  • Hash Cond: (edf.entity_dimension_id = ed.id)
87. 0.113 0.459 ↓ 1.4 65 1

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

  • Hash Cond: (ledfr.entity_dimension_family_id = edf.id)
88. 0.130 0.249 ↓ 1.4 65 1

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

  • Hash Cond: (ledfr.user_group_id = ugg_1.id)
89. 0.082 0.082 ↑ 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.012..0.082 rows=92 loops=1)

90. 0.015 0.037 ↑ 3.3 15 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
91. 0.022 0.022 ↑ 3.3 15 1

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

  • Filter: active
  • Rows Removed by Filter: 1
92. 0.048 0.097 ↑ 1.0 55 1

Hash (cost=1.55..1.55 rows=55 width=12) (actual time=0.096..0.097 rows=55 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
93. 0.049 0.049 ↑ 1.0 55 1

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

94. 0.013 0.025 ↑ 160.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
95. 0.012 0.012 ↑ 160.0 6 1

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

96. 0.024 0.041 ↑ 6.2 16 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
97. 0.017 0.017 ↑ 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.017 rows=16 loops=1)

98. 0.038 0.077 ↑ 1.0 42 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
99. 0.039 0.039 ↑ 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.006..0.039 rows=42 loops=1)

100. 0.026 0.056 ↓ 2.6 23 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
101. 0.030 0.030 ↓ 2.6 23 1

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

102. 0.007 0.018 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
103. 0.011 0.011 ↑ 1.0 1 1

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

  • Filter: ((system_slug)::text = 'base'::text)
  • Rows Removed by Filter: 1
104. 126.890 126.890 ↑ 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.002..0.002 rows=1 loops=63,445)

  • Index Cond: ((entity_id = ed.entity_id) AND (variable_id = variables_3.id))
105. 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
106.          

CTE entitiesUnitNotDefault

107. 0.025 10.252 ↓ 0.0 0 1

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

  • Hash Cond: (ev_2.variable_id = variables_4.id)
108. 0.023 0.154 ↓ 0.0 0 1

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

  • Hash Cond: (ev_2.entity_unit_id = eu.id)
109. 0.002 0.087 ↓ 0.0 0 1

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

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

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

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

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

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

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

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

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

  • Hash Cond: (evdr_1.entity_unit_variable_dimension_id = evd_2.id)
114. 0.008 0.008 ↓ 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.008 rows=0 loops=1)

115. 0.000 0.000 ↓ 0.0 0

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

116. 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)
117. 0.019 0.040 ↑ 3.3 15 1

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

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

Seq Scan on user_groups ugg_2 (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
119. 0.000 0.000 ↓ 0.0 0

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

120. 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)
121. 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)

122. 0.000 0.000 ↓ 0.0 0

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

123. 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)

124. 0.006 0.017 ↑ 386.7 3 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
125. 0.011 0.011 ↑ 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.008..0.011 rows=3 loops=1)

126. 0.000 0.000 ↓ 0.0 0

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

127. 0.000 0.000 ↓ 0.0 0

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

128. 0.015 0.044 ↑ 15.0 8 1

Hash (cost=11.20..11.20 rows=120 width=8) (actual time=0.043..0.044 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
129. 0.029 0.029 ↑ 15.0 8 1

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

130. 2.604 10.073 ↓ 274.7 3,022 1

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

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 139kB
131. 5.023 7.469 ↓ 274.7 3,022 1

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

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

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

133. 0.009 0.019 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
134. 0.010 0.010 ↑ 1.0 1 1

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

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

CTE entitiesUnitDefault

136. 0.002 0.136 ↓ 0.0 0 1

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

137. 0.002 0.134 ↓ 0.0 0 1

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

138. 0.010 0.132 ↓ 0.0 0 1

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

  • Hash Cond: (variables_5.family_id = edf_1.family_id)
139. 0.011 0.034 ↑ 11.0 1 1

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

  • Hash Cond: (variables_5.type_id = vt_3.id)
140. 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.007..0.007 rows=1 loops=1)

141. 0.008 0.016 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
142. 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
143. 0.002 0.088 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
144. 0.001 0.086 ↓ 0.0 0 1

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

  • Hash Cond: (COALESCE(ledfr_1.user_id, luug_3.user_id) = u_3.id)
145. 0.008 0.085 ↓ 0.0 0 1

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

  • Hash Cond: (ed_1.entity_unit_id = eu_1.id)
146. 0.008 0.052 ↓ 0.0 0 1

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

  • Hash Cond: (edf_1.entity_unit_dimension_id = ed_1.id)
147. 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)
148. 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)
149. 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)
150. 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)

151. 0.000 0.000 ↓ 0.0 0

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

152. 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
153. 0.000 0.000 ↓ 0.0 0

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

154. 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)
155. 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)

156. 0.000 0.000 ↓ 0.0 0

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

157. 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)

158. 0.000 0.000 ↓ 0.0 0

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

159. 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)

160. 0.017 0.034 ↑ 80.0 12 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
161. 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.008..0.017 rows=12 loops=1)

162. 0.014 0.025 ↑ 15.0 8 1

Hash (cost=11.20..11.20 rows=120 width=8) (actual time=0.024..0.025 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
163. 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)

164. 0.000 0.000 ↓ 0.0 0

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

165. 0.000 0.000 ↓ 0.0 0

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

166. 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))
167. 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
168. 10.165 860.632 ↓ 763.5 3,054 1

Sort (cost=0.30..0.31 rows=4 width=532) (actual time=858.377..860.632 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
169. 8.437 850.467 ↓ 763.5 3,054 1

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

  • Group Key: "entitiesNotDefault".entity_id, "entitiesNotDefault".entity_unit_id, "entitiesNotDefault".dimension_id, "entitiesNotDefault".variable_id, "entitiesNotDefault".user_id
170. 4.712 842.030 ↓ 769.0 3,076 1

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

171. 0.808 0.808 ↓ 0.0 0 1

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

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

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

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

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

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

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

  • Filter: ((user_id)::text = '79f30646-d7b1-11e8-8dbb-f2801f1b9fd1'::text)
175. 15.150 15.150 ↑ 1.0 1 3,030

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

  • Index Cond: (id = variables.type_id)
176. 18.180 18.180 ↑ 1.0 1 3,030

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

  • Index Cond: (id = variables.nature_id)
177. 15.150 133.320 ↓ 0.0 0 3,030

Append (cost=0.14..4.49 rows=2 width=16) (actual time=0.044..0.044 rows=0 loops=3,030)

178. 72.720 72.720 ↓ 0.0 0 3,030

Index Scan using idx_variables_values_2018_entity_dimension_unit_variable on variables_values_2018 vl (cost=0.14..2.82 rows=1 width=16) (actual time=0.024..0.024 rows=0 loops=3,030)

  • Index Cond: ((entity_id = vp.entity_id) AND (dimension_id = entity_variable_dimensions.dimension_id) AND (variable_id = variables.id))
  • Filter: ((year >= 2018) AND (year <= 2019) AND (year_month >= '201801'::numeric) AND (year_month <= '201912'::numeric) AND (COALESCE(entity_unit_id, '-1'::integer) = COALESCE(vp.entity_unit_id, '-1'::integer)))
  • Rows Removed by Filter: 0
179. 45.450 45.450 ↓ 0.0 0 3,030

Index Scan using idx_variables_values_2019_entity_dimension_unit_variable on variables_values_2019 vl_1 (cost=0.14..1.67 rows=1 width=16) (actual time=0.015..0.015 rows=0 loops=3,030)

  • Index Cond: ((entity_id = vp.entity_id) AND (dimension_id = entity_variable_dimensions.dimension_id) AND (variable_id = variables.id))
  • Filter: ((year >= 2018) AND (year <= 2019) AND (year_month >= '201801'::numeric) AND (year_month <= '201912'::numeric) AND (COALESCE(entity_unit_id, '-1'::integer) = COALESCE(vp.entity_unit_id, '-1'::integer)))
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
Planning time : 35.669 ms