explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xxVH

Settings
# exclusive inclusive rows x rows loops node
1. 102.585 9,963.618 ↓ 118.5 32,600 1

HashAggregate (cost=20,184.76..20,187.51 rows=275 width=49) (actual time=9,932.231..9,963.618 rows=32,600 loops=1)

  • Group Key: (concat(entityvariables.variable_id, entityvariables.entity_id, e.dimension_id, 0)), entityvariables.entity_id, (NULL::integer), entityvariables.variable_id, e.dimension_id, (CASE WHEN e.default_responsibles THEN (SubPlan 1) ELSE (SubPlan 2) END)
2. 49.426 9,861.033 ↓ 118.5 32,600 1

Append (cost=0.85..20,180.63 rows=275 width=49) (actual time=0.466..9,861.033 rows=32,600 loops=1)

3. 0.002 0.013 ↓ 0.0 0 1

Nested Loop (cost=0.85..395.97 rows=5 width=49) (actual time=0.012..0.013 rows=0 loops=1)

4. 0.002 0.011 ↓ 0.0 0 1

Nested Loop (cost=0.56..16.57 rows=1 width=12) (actual time=0.010..0.011 rows=0 loops=1)

5. 0.009 0.009 ↓ 0.0 0 1

Index Scan using entity_variables_entity_id_variable_id_key on entity_variables entityvariables (cost=0.29..8.26 rows=1 width=12) (actual time=0.009..0.009 rows=0 loops=1)

  • Index Cond: (entity_id = 9)
6. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_variables_id on variables (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: (id = entityvariables.variable_id)
  • Filter: (status_id <> 4)
7. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_entity_variable_entity_variable_dimensions on entity_variable_dimensions e (cost=0.29..22.70 rows=5 width=14) (never executed)

  • Index Cond: (entity_variable_id = entityvariables.id)
8.          

SubPlan (for Nested Loop)

9. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=36.77..36.78 rows=1 width=1) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Result (cost=5.36..36.76 rows=1 width=0) (never executed)

  • One-Time Filter: e.active
11. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.36..36.76 rows=1 width=0) (never executed)

  • Join Filter: (v.type_id = vt.id)
12. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=5.36..35.73 rows=1 width=4) (never executed)

  • Filter: (u.id = COALESCE(ledfr.user_id, luug.user_id))
13. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.19..34.17 rows=1 width=16) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=5.19..33.11 rows=1 width=12) (never executed)

15. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.91..32.77 rows=1 width=8) (never executed)

  • Join Filter: (ed.id = edf.entity_dimension_id)
16. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.28..9.98 rows=1 width=12) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Seq Scan on entity_dimensions ed (cost=0.00..1.67 rows=1 width=4) (never executed)

  • Filter: ((entity_id = 9) AND (dimension_id = e.dimension_id))
18. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_variables_id on variables v (cost=0.28..8.30 rows=1 width=8) (never executed)

  • Index Cond: (id = entityvariables.variable_id)
  • Filter: (status_id <> 4)
19. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on entity_dimension_families edf (cost=4.64..22.21 rows=46 width=12) (never executed)

  • Recheck Cond: (family_id = v.family_id)
20. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_entity_dimension_families_family_id (cost=0.00..4.63 rows=46 width=0) (never executed)

  • Index Cond: (family_id = v.family_id)
21. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_entity_dimension_family_responsibles_entity_dimension_famil on lnk_entity_dimension_family_responsibles ledfr (cost=0.28..0.33 rows=1 width=12) (never executed)

  • Index Cond: (entity_dimension_family_id = edf.id)
22. 0.000 0.000 ↓ 0.0 0

Seq Scan on users u (cost=0.00..1.05 rows=1 width=4) (never executed)

  • Filter: ((external_id)::text = '79f30646-d7b1-11e8-8dbb-f2801f1b9fd1'::text)
23. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=0.17..1.53 rows=2 width=8) (never executed)

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

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

25. 0.000 0.000 ↓ 0.0 0

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

26. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = ledfr.user_group_id)
  • Filter: (active IS NOT FALSE)
27. 0.000 0.000 ↓ 0.0 0

Seq Scan on variable_types vt (cost=0.00..1.02 rows=1 width=4) (never executed)

  • Filter: ((system_slug)::text = 'base'::text)
28. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=34.54..34.55 rows=1 width=1) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Result (cost=7.79..34.53 rows=1 width=0) (never executed)

  • One-Time Filter: e.active
30. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=7.79..34.53 rows=1 width=0) (never executed)

  • Hash Cond: (ug_1.id = luug_1.user_group_id)
  • Filter: (u_1.id = COALESCE(evdr.user_id, luug_1.user_id))
31. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=6.18..32.86 rows=2 width=12) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.69..17.86 rows=1 width=4) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.56..17.67 rows=1 width=8) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.29..9.36 rows=1 width=8) (never executed)

35. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = e.entity_variable_id)
36. 0.000 0.000 ↓ 0.0 0

Seq Scan on users u_1 (cost=0.00..1.05 rows=1 width=4) (never executed)

  • Filter: ((external_id)::text = '79f30646-d7b1-11e8-8dbb-f2801f1b9fd1'::text)
37. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = ev.variable_id)
  • Filter: (status_id <> 4)
38. 0.000 0.000 ↓ 0.0 0

Index Scan using variable_types_pkey on variable_types vt_1 (cost=0.13..0.16 rows=1 width=4) (never executed)

  • Index Cond: (id = variables_2.type_id)
  • Filter: ((system_slug)::text = 'base'::text)
39. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=5.49..14.98 rows=3 width=8) (never executed)

  • Hash Cond: (evdr.user_group_id = ug_1.id)
  • Filter: (ug_1.active IS NOT FALSE)
40. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on entity_variable_dimension_responsibles evdr (cost=4.20..13.67 rows=6 width=8) (never executed)

  • Recheck Cond: (e.id = entity_variable_dimension_id)
41. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (entity_variable_dimension_id = e.id)
42. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.13..1.13 rows=13 width=5) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Seq Scan on user_groups ug_1 (cost=0.00..1.13 rows=13 width=5) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.27..1.27 rows=27 width=8) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Seq Scan on lnk_user_user_groups luug_1 (cost=0.00..1.27 rows=27 width=8) (never executed)

46. 172.607 9,811.594 ↓ 120.7 32,600 1

Nested Loop (cost=30.96..19,780.54 rows=270 width=49) (actual time=0.451..9,811.594 rows=32,600 loops=1)

47. 16.709 61.107 ↓ 120.7 6,520 1

Nested Loop (cost=30.67..157.71 rows=54 width=16) (actual time=0.100..61.107 rows=6,520 loops=1)

48. 11.806 18.298 ↓ 120.8 6,525 1

Nested Loop (cost=30.40..139.26 rows=54 width=16) (actual time=0.092..18.298 rows=6,525 loops=1)

49. 0.017 0.017 ↓ 5.0 5 1

Seq Scan on entity_units (cost=0.00..11.50 rows=1 width=8) (actual time=0.011..0.017 rows=5 loops=1)

  • Filter: (entity_id = 9)
50. 6.150 6.475 ↑ 1.0 1,305 5

Bitmap Heap Scan on entity_unit_variables entityunitvariables (cost=30.40..114.71 rows=1,305 width=12) (actual time=0.072..1.295 rows=1,305 loops=5)

  • Recheck Cond: (entity_unit_id = entity_units.id)
  • Heap Blocks: exact=72
51. 0.325 0.325 ↑ 1.0 1,305 5

Bitmap Index Scan on entity_unit_variables_entity_unit_id_variable_id_key (cost=0.00..30.07 rows=1,305 width=0) (actual time=0.064..0.065 rows=1,305 loops=5)

  • Index Cond: (entity_unit_id = entity_units.id)
52. 26.100 26.100 ↑ 1.0 1 6,525

Index Scan using idx_variables_id on variables variables_1 (cost=0.28..0.34 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=6,525)

  • Index Cond: (id = entityunitvariables.variable_id)
  • Filter: (status_id <> 4)
  • Rows Removed by Filter: 0
53. 58.680 58.680 ↑ 1.0 5 6,520

Index Scan using idx_entity_unit_variable_entity_variable_dimensions on entity_unit_variable_dimensions e_1 (cost=0.29..0.64 rows=5 width=14) (actual time=0.003..0.009 rows=5 loops=6,520)

  • Index Cond: (entity_unit_variable_id = entityunitvariables.id)
54.          

SubPlan (for Nested Loop)

55. 130.400 9,519.200 ↑ 1.0 1 32,600

Aggregate (cost=37.98..37.99 rows=1 width=1) (actual time=0.291..0.292 rows=1 loops=32,600)

56. 260.800 9,388.800 ↑ 1.0 1 32,600

Result (cost=5.49..37.98 rows=1 width=0) (actual time=0.182..0.288 rows=1 loops=32,600)

  • One-Time Filter: e_1.active
57. 163.000 9,128.000 ↑ 1.0 1 32,600

Nested Loop (cost=5.49..37.98 rows=1 width=0) (actual time=0.176..0.280 rows=1 loops=32,600)

  • Join Filter: (u_2.id = COALESCE(ledfr_1.user_id, luug_2.user_id))
  • Rows Removed by Join Filter: 1
58. 163.000 163.000 ↑ 1.0 1 32,600

Seq Scan on users u_2 (cost=0.00..1.05 rows=1 width=4) (actual time=0.003..0.005 rows=1 loops=32,600)

  • Filter: ((external_id)::text = '79f30646-d7b1-11e8-8dbb-f2801f1b9fd1'::text)
  • Rows Removed by Filter: 5
59. 391.200 8,802.000 ↑ 1.0 2 32,600

Nested Loop Left Join (cost=5.49..36.90 rows=2 width=8) (actual time=0.149..0.270 rows=2 loops=32,600)

60. 130.400 6,487.400 ↑ 1.0 1 32,600

Nested Loop Left Join (cost=5.32..35.35 rows=1 width=8) (actual time=0.114..0.199 rows=1 loops=32,600)

61. 163.000 6,226.600 ↑ 1.0 1 32,600

Nested Loop (cost=5.04..35.01 rows=1 width=4) (actual time=0.108..0.191 rows=1 loops=32,600)

62. 1,369.200 5,965.800 ↑ 1.0 1 32,600

Nested Loop (cost=4.90..26.83 rows=1 width=8) (actual time=0.103..0.183 rows=1 loops=32,600)

63. 1,434.400 2,966.600 ↓ 2.1 25 32,600

Nested Loop (cost=4.75..24.37 rows=12 width=8) (actual time=0.026..0.091 rows=25 loops=32,600)

64. 228.200 423.800 ↑ 1.0 1 32,600

Nested Loop (cost=0.28..9.33 rows=1 width=4) (actual time=0.011..0.013 rows=1 loops=32,600)

  • Join Filter: (v_1.type_id = vt_2.id)
65. 130.400 130.400 ↑ 1.0 1 32,600

Index Scan using idx_variables_id on variables v_1 (cost=0.28..8.30 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=32,600)

  • Index Cond: (id = entityunitvariables.variable_id)
  • Filter: (status_id <> 4)
66. 65.200 65.200 ↑ 1.0 1 32,600

Seq Scan on variable_types vt_2 (cost=0.00..1.02 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=32,600)

  • Filter: ((system_slug)::text = 'base'::text)
  • Rows Removed by Filter: 1
67. 945.400 1,108.400 ↑ 1.0 25 32,600

Bitmap Heap Scan on entity_unit_dimension_families edf_1 (cost=4.47..14.78 rows=25 width=12) (actual time=0.009..0.034 rows=25 loops=32,600)

  • Recheck Cond: (family_id = v_1.family_id)
  • Heap Blocks: exact=299,900
68. 163.000 163.000 ↑ 1.0 25 32,600

Bitmap Index Scan on idx_entity_unit_dimension_families_family_id (cost=0.00..4.46 rows=25 width=0) (actual time=0.005..0.005 rows=25 loops=32,600)

  • Index Cond: (family_id = v_1.family_id)
69. 1,630.000 1,630.000 ↓ 0.0 0 815,000

Index Scan using lnk_dimension_units_pkey on entity_unit_dimensions ed_1 (cost=0.15..0.20 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=815,000)

  • Index Cond: (id = edf_1.entity_unit_dimension_id)
  • Filter: ((dimension_id = e_1.dimension_id) AND (entity_unit_id = entityunitvariables.entity_unit_id))
  • Rows Removed by Filter: 1
70. 97.800 97.800 ↑ 1.0 1 32,600

Index Scan using "EntityUnit_pkey" on entity_units eu (cost=0.14..8.16 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=32,600)

  • Index Cond: (id = entityunitvariables.entity_unit_id)
  • Filter: (entity_id = 9)
71. 130.400 130.400 ↑ 1.0 1 32,600

Index Scan using idx_entity_unit_dimension_family_responsibles_entity_dimension_ on lnk_entity_unit_dimension_family_responsibles ledfr_1 (cost=0.28..0.33 rows=1 width=12) (actual time=0.003..0.004 rows=1 loops=32,600)

  • Index Cond: (entity_unit_dimension_family_id = edf_1.id)
72. 978.000 1,923.400 ↑ 1.0 2 32,600

Hash Right Join (cost=0.17..1.54 rows=2 width=8) (actual time=0.028..0.059 rows=2 loops=32,600)

  • Hash Cond: (luug_2.user_group_id = ug_2.id)
73. 717.200 717.200 ↑ 1.0 27 32,600

Seq Scan on lnk_user_user_groups luug_2 (cost=0.00..1.27 rows=27 width=8) (actual time=0.002..0.022 rows=27 loops=32,600)

74. 97.800 228.200 ↑ 1.0 1 32,600

Hash (cost=0.16..0.16 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=32,600)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
75. 130.400 130.400 ↑ 1.0 1 32,600

Index Scan using user_groups_pkey on user_groups ug_2 (cost=0.14..0.16 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=32,600)

  • Index Cond: (id = ledfr_1.user_group_id)
  • Filter: (active IS NOT FALSE)
76. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=34.53..34.55 rows=1 width=1) (never executed)

77. 0.000 0.000 ↓ 0.0 0

Result (cost=7.79..34.53 rows=1 width=0) (never executed)

  • One-Time Filter: e_1.active
78. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=7.79..34.53 rows=1 width=0) (never executed)

  • Hash Cond: (ug_3.id = luug_3.user_group_id)
  • Filter: (u_3.id = COALESCE(evdr_1.user_id, luug_3.user_id))
79. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=6.18..32.86 rows=2 width=12) (never executed)

80. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.69..17.86 rows=1 width=4) (never executed)

81. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.56..17.67 rows=1 width=8) (never executed)

82. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.28..9.36 rows=1 width=8) (never executed)

83. 0.000 0.000 ↓ 0.0 0

Index Scan using variable_units_pkey on entity_unit_variables ev_1 (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: (id = e_1.entity_unit_variable_id)
84. 0.000 0.000 ↓ 0.0 0

Seq Scan on users u_3 (cost=0.00..1.05 rows=1 width=4) (never executed)

  • Filter: ((external_id)::text = '79f30646-d7b1-11e8-8dbb-f2801f1b9fd1'::text)
85. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_variables_id on variables variables_3 (cost=0.28..8.30 rows=1 width=8) (never executed)

  • Index Cond: (id = ev_1.variable_id)
  • Filter: (status_id <> 4)
86. 0.000 0.000 ↓ 0.0 0

Index Scan using variable_types_pkey on variable_types vt_3 (cost=0.13..0.16 rows=1 width=4) (never executed)

  • Index Cond: (id = variables_3.type_id)
  • Filter: ((system_slug)::text = 'base'::text)
87. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=5.49..14.98 rows=3 width=8) (never executed)

  • Hash Cond: (evdr_1.user_group_id = ug_3.id)
  • Filter: (ug_3.active IS NOT FALSE)
88. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on entity_unit_variable_dimension_responsibles evdr_1 (cost=4.20..13.67 rows=6 width=8) (never executed)

  • Recheck Cond: (e_1.id = entity_unit_variable_dimension_id)
89. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (entity_unit_variable_dimension_id = e_1.id)
90. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.13..1.13 rows=13 width=5) (never executed)

91. 0.000 0.000 ↓ 0.0 0

Seq Scan on user_groups ug_3 (cost=0.00..1.13 rows=13 width=5) (never executed)

92. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.27..1.27 rows=27 width=8) (never executed)

93. 0.000 0.000 ↓ 0.0 0

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