explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oTFX

Settings
# exclusive inclusive rows x rows loops node
1. 172.184 15,657.224 ↓ 5.0 32,600 1

Merge Join (cost=60.72..392,292.31 rows=6,525 width=49) (actual time=0.672..15,657.224 rows=32,600 loops=1)

  • Merge Cond: (entityunitvariables.variable_id = variables.id)
2. 156.447 15,481.298 ↓ 5.0 32,601 1

Nested Loop Left Join (cost=60.44..392,534.34 rows=6,525 width=18) (actual time=0.641..15,481.298 rows=32,601 loops=1)

  • Join Filter: (e.default_responsibles IS NOT TRUE)
  • Rows Removed by Join Filter: 32601
3. 142.222 12,977.579 ↓ 5.0 32,601 1

Nested Loop Left Join (cost=25.88..166,825.97 rows=6,525 width=27) (actual time=0.509..12,977.579 rows=32,601 loops=1)

  • Join Filter: e.default_responsibles
4. 84.161 446.977 ↓ 5.0 32,601 1

Nested Loop (cost=0.57..1,466.82 rows=6,525 width=26) (actual time=0.116..446.977 rows=32,601 loops=1)

5. 41.947 258.480 ↓ 5.0 6,521 1

Nested Loop (cost=0.28..569.00 rows=1,305 width=16) (actual time=0.094..258.480 rows=6,521 loops=1)

  • Join Filter: (entityunitvariables.entity_unit_id = entity_units.id)
  • Rows Removed by Join Filter: 13043
6. 20.903 20.903 ↑ 1.0 6,521 1

Index Scan using idx_entity_unit_variables_variable_id on entity_unit_variables entityunitvariables (cost=0.28..470.06 rows=6,525 width=12) (actual time=0.040..20.903 rows=6,521 loops=1)

7. 195.596 195.630 ↓ 3.0 3 6,521

Materialize (cost=0.00..1.07 rows=1 width=8) (actual time=0.028..0.030 rows=3 loops=6,521)

8. 0.034 0.034 ↓ 5.0 5 1

Seq Scan on entity_units (cost=0.00..1.06 rows=1 width=8) (actual time=0.028..0.034 rows=5 loops=1)

  • Filter: (entity_id = 9)
9. 104.336 104.336 ↑ 1.0 5 6,521

Index Scan using idx_entity_unit_variable_entity_variable_dimensions on entity_unit_variable_dimensions e (cost=0.29..0.64 rows=5 width=14) (actual time=0.009..0.016 rows=5 loops=6,521)

  • Index Cond: (entity_unit_variable_id = entityunitvariables.id)
10. 97.803 12,388.380 ↑ 1.0 1 32,601

Aggregate (cost=25.31..25.32 rows=1 width=1) (actual time=0.380..0.380 rows=1 loops=32,601)

11. 652.020 12,290.577 ↑ 1.0 1 32,601

Result (cost=0.97..25.31 rows=1 width=0) (actual time=0.259..0.377 rows=1 loops=32,601)

  • One-Time Filter: e.active
12. 260.808 11,638.557 ↑ 1.0 1 32,601

Nested Loop (cost=0.97..25.31 rows=1 width=0) (actual time=0.248..0.357 rows=1 loops=32,601)

  • Join Filter: (u.id = COALESCE(ledfr.user_id, luug.user_id))
  • Rows Removed by Join Filter: 1
13. 326.010 326.010 ↑ 1.0 1 32,601

Seq Scan on users u (cost=0.00..1.07 rows=1 width=4) (actual time=0.008..0.010 rows=1 loops=32,601)

  • Filter: ((external_id)::text = '79f30646-d7b1-11e8-8dbb-f2801f1b9fd1'::text)
  • Rows Removed by Filter: 5
14. 1,727.881 11,051.739 ↑ 1.0 2 32,601

Nested Loop Left Join (cost=0.97..24.21 rows=2 width=8) (actual time=0.203..0.339 rows=2 loops=32,601)

  • Join Filter: (luug.user_group_id = ug.id)
  • Rows Removed by Join Filter: 25
15. 293.412 8,411.058 ↑ 1.0 1 32,601

Nested Loop Left Join (cost=0.97..22.60 rows=1 width=8) (actual time=0.166..0.258 rows=1 loops=32,601)

16. 391.218 8,019.846 ↑ 1.0 1 32,601

Nested Loop Left Join (cost=0.83..22.43 rows=1 width=8) (actual time=0.159..0.246 rows=1 loops=32,601)

17. 358.614 7,433.028 ↑ 1.0 1 32,601

Nested Loop (cost=0.56..22.09 rows=1 width=4) (actual time=0.145..0.228 rows=1 loops=32,601)

  • Join Filter: (v.type_id = vt.id)
18. 358.615 6,976.614 ↑ 1.0 1 32,601

Nested Loop (cost=0.56..21.05 rows=1 width=8) (actual time=0.132..0.214 rows=1 loops=32,601)

19. 2,705.976 6,487.599 ↑ 1.0 1 32,601

Nested Loop (cost=0.56..19.97 rows=1 width=12) (actual time=0.122..0.199 rows=1 loops=32,601)

  • Join Filter: (v.family_id = edf.family_id)
  • Rows Removed by Join Filter: 66
20. 391.212 749.823 ↑ 1.0 1 32,601

Nested Loop (cost=0.28..9.68 rows=1 width=16) (actual time=0.016..0.023 rows=1 loops=32,601)

21. 195.606 195.606 ↑ 1.0 1 32,601

Seq Scan on entity_unit_dimensions ed (cost=0.00..1.38 rows=1 width=8) (actual time=0.004..0.006 rows=1 loops=32,601)

  • Filter: ((dimension_id = e.dimension_id) AND (entity_unit_id = entityunitvariables.entity_unit_id))
  • Rows Removed by Filter: 24
22. 163.005 163.005 ↑ 1.0 1 32,601

Index Scan using idx_variables_id on variables v (cost=0.28..8.30 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=32,601)

  • Index Cond: (id = entityunitvariables.variable_id)
  • Filter: (status_id <> 4)
  • Rows Removed by Filter: 0
23. 3,031.800 3,031.800 ↑ 1.0 67 32,600

Index Scan using idx_entity_unit_dimension_families_entity_unit_dimension_id on entity_unit_dimension_families edf (cost=0.28..9.45 rows=67 width=12) (actual time=0.007..0.093 rows=67 loops=32,600)

  • Index Cond: (entity_unit_dimension_id = ed.id)
24. 130.400 130.400 ↑ 1.0 1 32,600

Seq Scan on entity_units eu (cost=0.00..1.07 rows=1 width=4) (actual time=0.002..0.004 rows=1 loops=32,600)

  • Filter: ((id = entityunitvariables.entity_unit_id) AND (entity_id = 9))
  • Rows Removed by Filter: 4
25. 97.800 97.800 ↑ 1.0 1 32,600

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

  • Filter: ((system_slug)::text = 'base'::text)
  • Rows Removed by Filter: 1
26. 195.600 195.600 ↑ 1.0 1 32,600

Index Scan using idx_entity_unit_dimension_family_responsibles_entity_dimension_ on lnk_entity_unit_dimension_family_responsibles ledfr (cost=0.28..0.33 rows=1 width=12) (actual time=0.005..0.006 rows=1 loops=32,600)

  • Index Cond: (entity_unit_dimension_family_id = edf.id)
27. 97.800 97.800 ↑ 1.0 1 32,600

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

  • Index Cond: (id = ledfr.user_group_id)
  • Filter: (active IS NOT FALSE)
28. 912.800 912.800 ↑ 1.0 27 32,600

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

29. 97.803 2,347.272 ↑ 1.0 1 32,601

Aggregate (cost=34.56..34.57 rows=1 width=1) (actual time=0.072..0.072 rows=1 loops=32,601)

30. 65.202 2,249.469 ↓ 0.0 0 32,601

Result (cost=7.79..34.56 rows=1 width=0) (actual time=0.069..0.069 rows=0 loops=32,601)

  • One-Time Filter: e.active
31. 65.202 2,184.267 ↓ 0.0 0 32,601

Hash Left Join (cost=7.79..34.56 rows=1 width=0) (actual time=0.067..0.067 rows=0 loops=32,601)

  • Hash Cond: (ug_1.id = luug_1.user_group_id)
  • Filter: (u_1.id = COALESCE(evdr.user_id, luug_1.user_id))
32. 130.420 2,119.065 ↓ 0.0 0 32,601

Nested Loop (cost=6.18..32.89 rows=2 width=12) (actual time=0.065..0.065 rows=0 loops=32,601)

33. 130.407 1,467.045 ↑ 1.0 1 32,601

Nested Loop (cost=0.69..17.88 rows=1 width=4) (actual time=0.035..0.045 rows=1 loops=32,601)

34. 260.808 1,238.838 ↑ 1.0 1 32,601

Nested Loop (cost=0.56..17.69 rows=1 width=8) (actual time=0.029..0.038 rows=1 loops=32,601)

35. 456.414 847.626 ↑ 1.0 1 32,601

Nested Loop (cost=0.28..9.38 rows=1 width=8) (actual time=0.018..0.026 rows=1 loops=32,601)

36. 260.808 260.808 ↑ 1.0 1 32,601

Index Scan using variable_units_pkey on entity_unit_variables ev (cost=0.28..8.30 rows=1 width=4) (actual time=0.005..0.008 rows=1 loops=32,601)

  • Index Cond: (id = e.entity_unit_variable_id)
37. 130.404 130.404 ↑ 1.0 1 32,601

Seq Scan on users u_1 (cost=0.00..1.07 rows=1 width=4) (actual time=0.002..0.004 rows=1 loops=32,601)

  • Filter: ((external_id)::text = '79f30646-d7b1-11e8-8dbb-f2801f1b9fd1'::text)
  • Rows Removed by Filter: 5
38. 130.404 130.404 ↑ 1.0 1 32,601

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

  • Index Cond: (id = ev.variable_id)
  • Filter: (status_id <> 4)
  • Rows Removed by Filter: 0
39. 97.800 97.800 ↑ 1.0 1 32,600

Index Scan using variable_types_pkey on variable_types vt_1 (cost=0.13..0.16 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=32,600)

  • Index Cond: (id = variables_1.type_id)
  • Filter: ((system_slug)::text = 'base'::text)
40. 260.800 521.600 ↓ 0.0 0 32,600

Hash Left Join (cost=5.49..14.98 rows=3 width=8) (actual time=0.016..0.016 rows=0 loops=32,600)

  • Hash Cond: (evdr.user_group_id = ug_1.id)
  • Filter: (ug_1.active IS NOT FALSE)
41. 65.200 260.800 ↓ 0.0 0 32,600

Bitmap Heap Scan on entity_unit_variable_dimension_responsibles evdr (cost=4.20..13.67 rows=6 width=8) (actual time=0.008..0.008 rows=0 loops=32,600)

  • Recheck Cond: (e.id = entity_unit_variable_dimension_id)
42. 195.600 195.600 ↓ 0.0 0 32,600

Bitmap Index Scan on idx_entity_unit_variable_dimension_responsibles_entity_variable (cost=0.00..4.20 rows=6 width=0) (actual time=0.006..0.006 rows=0 loops=32,600)

  • Index Cond: (entity_unit_variable_dimension_id = e.id)
43. 0.000 0.000 ↓ 0.0 0

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

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

45. 0.000 0.000 ↓ 0.0 0

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

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

47. 3.742 3.742 ↑ 1.0 1,304 1

Index Scan using idx_variables_id on variables (cost=0.28..258.35 rows=1,305 width=4) (actual time=0.015..3.742 rows=1,304 loops=1)

  • Filter: (status_id <> 4)
  • Rows Removed by Filter: 1
Planning time : 10.470 ms