explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yxz4 : Optimization for: Optimization for: plan #naM; plan #ZDSY

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 558.477 106,064.455 ↓ 1.2 49,968 1

Nested Loop (cost=3,591.85..260,278,307.73 rows=41,977 width=964) (actual time=191.560..106,064.455 rows=49,968 loops=1)

2. 0.017 0.017 ↑ 1.0 1 1

Seq Scan on empresas_sucursales_types (cost=0.00..1.04 rows=1 width=40) (actual time=0.014..0.017 rows=1 loops=1)

  • Filter: (id = 1)
  • Rows Removed by Filter: 2
3. 122.696 872.969 ↓ 1.2 49,968 1

Hash Left Join (cost=3,591.85..8,952.96 rows=41,977 width=940) (actual time=191.273..872.969 rows=49,968 loops=1)

  • Hash Cond: (empresas_sucursales.id_activ_econ = actividad_econ.id_activ_econ)
4. 92.924 748.886 ↓ 1.2 49,968 1

Hash Left Join (cost=3,567.10..8,351.02 rows=41,977 width=886) (actual time=189.868..748.886 rows=49,968 loops=1)

  • Hash Cond: (empresas_sucursales.distrito_id = distritos.id)
5. 99.121 655.923 ↓ 1.2 49,968 1

Hash Left Join (cost=3,565.63..7,772.69 rows=41,977 width=876) (actual time=189.810..655.923 rows=49,968 loops=1)

  • Hash Cond: (empresas_sucursales.city_id = city.id)
6. 84.347 556.316 ↓ 1.2 49,968 1

Hash Left Join (cost=3,552.69..7,184.77 rows=41,977 width=840) (actual time=189.299..556.316 rows=49,968 loops=1)

  • Hash Cond: (emp.id_categoria_empresa = empresas_categorias.id_categoria_empresa)
7. 194.744 471.951 ↓ 1.2 49,968 1

Hash Join (cost=3,539.54..7,010.10 rows=41,977 width=324) (actual time=189.262..471.951 rows=49,968 loops=1)

  • Hash Cond: (empresas_sucursales.empresa_id = emp.id)
8. 87.973 87.973 ↑ 1.0 49,970 1

Seq Scan on empresas_sucursales (cost=0.00..2,612.82 rows=50,053 width=237) (actual time=0.008..87.973 rows=49,970 loops=1)

  • Filter: (type = 1)
  • Rows Removed by Filter: 5621
9. 52.592 189.234 ↓ 1.2 49,829 1

Hash (cost=3,017.22..3,017.22 rows=41,786 width=95) (actual time=189.234..189.234 rows=49,829 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 5999kB
10. 77.840 136.642 ↓ 1.2 49,829 1

Hash Join (cost=1.58..3,017.22 rows=41,786 width=95) (actual time=0.078..136.642 rows=49,829 loops=1)

  • Hash Cond: (emp.type = empresas_types.id)
11. 58.751 58.751 ↓ 1.0 49,829 1

Seq Scan on empresas emp (cost=0.00..2,410.94 rows=49,822 width=82) (actual time=0.020..58.751 rows=49,829 loops=1)

  • Filter: (((document)::text <> '0'::text) AND ((document)::text <> ''::text) AND ((document)::text <> '-'::text))
  • Rows Removed by Filter: 2
12. 0.027 0.051 ↓ 1.2 30 1

Hash (cost=1.26..1.26 rows=26 width=21) (actual time=0.051..0.051 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
13. 0.024 0.024 ↓ 1.2 30 1

Seq Scan on empresas_types (cost=0.00..1.26 rows=26 width=21) (actual time=0.002..0.024 rows=30 loops=1)

14. 0.005 0.018 ↑ 35.0 4 1

Hash (cost=11.40..11.40 rows=140 width=520) (actual time=0.018..0.018 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
15. 0.013 0.013 ↑ 35.0 4 1

Seq Scan on empresas_categorias (cost=0.00..11.40 rows=140 width=520) (actual time=0.010..0.013 rows=4 loops=1)

16. 0.236 0.486 ↑ 1.1 251 1

Hash (cost=9.64..9.64 rows=264 width=40) (actual time=0.486..0.486 rows=251 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
17. 0.250 0.250 ↑ 1.1 251 1

Seq Scan on city (cost=0.00..9.64 rows=264 width=40) (actual time=0.006..0.250 rows=251 loops=1)

18. 0.018 0.039 ↑ 1.2 18 1

Hash (cost=1.21..1.21 rows=21 width=18) (actual time=0.039..0.039 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
19. 0.021 0.021 ↑ 1.2 18 1

Seq Scan on distritos (cost=0.00..1.21 rows=21 width=18) (actual time=0.006..0.021 rows=18 loops=1)

20. 0.599 1.387 ↑ 1.0 700 1

Hash (cost=16.00..16.00 rows=700 width=58) (actual time=1.387..1.387 rows=700 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 63kB
21. 0.788 0.788 ↑ 1.0 700 1

Seq Scan on actividad_econ (cost=0.00..16.00 rows=700 width=58) (actual time=0.004..0.788 rows=700 loops=1)

22.          

SubPlan (forNested Loop)

23. 549.648 28,431.792 ↑ 1.0 1 49,968

Aggregate (cost=1,407.13..1,407.14 rows=1 width=8) (actual time=0.568..0.569 rows=1 loops=49,968)

24. 949.392 27,882.144 ↑ 5.8 10 49,968

Nested Loop (cost=0.72..1,406.98 rows=58 width=8) (actual time=0.062..0.558 rows=10 loops=49,968)

25. 249.840 249.840 ↑ 1.0 1 49,968

Index Only Scan using empresas_sucursales_nro_patronal_key on empresas_sucursales es (cost=0.29..3.31 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=49,968)

  • Index Cond: (nro_patronal = empresas_sucursales.nro_patronal)
  • Heap Fetches: 188
26. 4,611.112 26,682.912 ↑ 5.8 10 49,968

Index Scan using empleados_npatronal_id_idx on empleados (cost=0.43..1,403.10 rows=58 width=24) (actual time=0.055..0.534 rows=10 loops=49,968)

  • Index Cond: (npatronal = empresas_sucursales.nro_patronal)
  • Filter: (SubPlan 1)
  • Rows Removed by Filter: 12
27.          

SubPlan (forIndex Scan)

28. 2,207.180 22,071.800 ↑ 1.0 1 1,103,590

Limit (cost=9.06..9.06 rows=1 width=20) (actual time=0.020..0.020 rows=1 loops=1,103,590)

29. 5,517.950 19,864.620 ↑ 2.0 1 1,103,590

Sort (cost=9.06..9.06 rows=2 width=20) (actual time=0.018..0.018 rows=1 loops=1,103,590)

  • Sort Key: empleados_movimientos.fecha, empleados_movimientos.id
  • Sort Method: quicksort Memory: 25kB
30. 5,520.046 14,346.670 ↑ 1.0 2 1,103,590

Result (cost=0.00..9.05 rows=2 width=20) (actual time=0.008..0.013 rows=2 loops=1,103,590)

  • One-Time Filter: (empleados.empresa_id = emp.id)
31. 8,826.624 8,826.624 ↑ 1.0 2 1,103,328

Index Scan using empleados_movimientos_empleado_id_idx on empleados_movimientos (cost=0.00..9.04 rows=2 width=20) (actual time=0.006..0.008 rows=2 loops=1,103,328)

  • Index Cond: (empleado_id = empleados.id)
  • Rows Removed by Index Recheck: 0
32. 699.552 21,486.240 ↑ 1.0 1 49,968

Aggregate (cost=1,272.59..1,272.62 rows=1 width=16) (actual time=0.429..0.430 rows=1 loops=49,968)

33. 3,152.500 20,786.688 ↑ 5.8 12 49,968

Index Only Scan using empleados_empresa_id_id_idx on empleados emplea (cost=0.43..1,271.19 rows=70 width=16) (actual time=0.035..0.416 rows=12 loops=49,968)

  • Index Cond: (empresa_id = emp.id)
  • Filter: (SubPlan 3)
  • Rows Removed by Filter: 15
  • Heap Fetches: 23873
34.          

SubPlan (forIndex Only Scan)

35. 2,712.952 17,634.188 ↑ 1.0 1 1,356,476

Limit (cost=9.05..9.06 rows=1 width=20) (actual time=0.013..0.013 rows=1 loops=1,356,476)

36. 6,782.380 14,921.236 ↑ 2.0 1 1,356,476

Sort (cost=9.05..9.06 rows=2 width=20) (actual time=0.011..0.011 rows=1 loops=1,356,476)

  • Sort Key: empleados_movimientos_1.fecha, empleados_movimientos_1.id
  • Sort Method: quicksort Memory: 25kB
37. 8,138.856 8,138.856 ↑ 1.0 2 1,356,476

Index Scan using empleados_movimientos_empleado_id_idx on empleados_movimientos empleados_movimientos_1 (cost=0.00..9.04 rows=2 width=20) (actual time=0.004..0.006 rows=2 loops=1,356,476)

  • Index Cond: (empleado_id = emplea.id)
  • Rows Removed by Index Recheck: 0
38. 449.712 27,482.400 ↑ 1.0 1 49,968

Aggregate (cost=1,756.76..1,756.77 rows=1 width=8) (actual time=0.549..0.550 rows=1 loops=49,968)

39. 649.584 27,032.688 ↑ 6.3 6 49,968

Nested Loop (cost=0.72..1,756.67 rows=38 width=8) (actual time=0.065..0.541 rows=6 loops=49,968)

40. 149.904 149.904 ↑ 1.0 1 49,968

Index Only Scan using empresas_sucursales_nro_patronal_key on empresas_sucursales es_1 (cost=0.29..3.31 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=49,968)

  • Index Cond: (nro_patronal = empresas_sucursales.nro_patronal)
  • Heap Fetches: 188
41. 1,436.890 26,233.200 ↑ 6.3 6 49,968

Nested Loop (cost=0.43..1,752.98 rows=38 width=16) (actual time=0.059..0.525 rows=6 loops=49,968)

42. 2,575.306 21,336.336 ↑ 5.8 10 49,968

Index Scan using empleados_npatronal_id_idx on empleados empleados_1 (cost=0.43..1,403.10 rows=58 width=24) (actual time=0.037..0.427 rows=10 loops=49,968)

  • Index Cond: (npatronal = empresas_sucursales.nro_patronal)
  • Filter: (SubPlan 5)
  • Rows Removed by Filter: 12
43.          

SubPlan (forIndex Scan)

44. 3,310.770 18,761.030 ↑ 1.0 1 1,103,590

Limit (cost=9.06..9.06 rows=1 width=20) (actual time=0.016..0.017 rows=1 loops=1,103,590)

45. 4,414.360 15,450.260 ↑ 2.0 1 1,103,590

Sort (cost=9.06..9.06 rows=2 width=20) (actual time=0.014..0.014 rows=1 loops=1,103,590)

  • Sort Key: empleados_movimientos_2.fecha, empleados_movimientos_2.id
  • Sort Method: quicksort Memory: 25kB
46. 5,519.260 11,035.900 ↑ 1.0 2 1,103,590

Result (cost=0.00..9.05 rows=2 width=20) (actual time=0.005..0.010 rows=2 loops=1,103,590)

  • One-Time Filter: (empleados_1.empresa_id = emp.id)
47. 5,516.640 5,516.640 ↑ 1.0 2 1,103,328

Index Scan using empleados_movimientos_empleado_id_idx on empleados_movimientos empleados_movimientos_2 (cost=0.00..9.04 rows=2 width=20) (actual time=0.003..0.005 rows=2 loops=1,103,328)

  • Index Cond: (empleado_id = empleados_1.id)
  • Rows Removed by Index Recheck: 0
48. 3,459.974 3,459.974 ↑ 1.0 1 494,282

Index Scan using persons_id_idx on persons (cost=0.00..6.02 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=494,282)

  • Index Cond: (id = empleados_1.persona_id)
  • Rows Removed by Index Recheck: 0
  • Filter: ((sexo)::text = 'M'::text)
  • Rows Removed by Filter: 0
49. 299.808 25,383.744 ↑ 1.0 1 49,968

Aggregate (cost=1,756.54..1,756.55 rows=1 width=8) (actual time=0.507..0.508 rows=1 loops=49,968)

50. 399.744 25,083.936 ↑ 6.7 3 49,968

Nested Loop (cost=0.72..1,756.49 rows=20 width=8) (actual time=0.099..0.502 rows=3 loops=49,968)

51. 149.904 149.904 ↑ 1.0 1 49,968

Index Only Scan using empresas_sucursales_nro_patronal_key on empresas_sucursales es_2 (cost=0.29..3.31 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=49,968)

  • Index Cond: (nro_patronal = empresas_sucursales.nro_patronal)
  • Heap Fetches: 188
52. 1,370.728 24,534.288 ↑ 6.7 3 49,968

Nested Loop (cost=0.43..1,752.98 rows=20 width=16) (actual time=0.093..0.491 rows=3 loops=49,968)

53. 2,425.402 21,186.432 ↑ 5.8 10 49,968

Index Scan using empleados_npatronal_id_idx on empleados empleados_2 (cost=0.43..1,403.10 rows=58 width=24) (actual time=0.037..0.424 rows=10 loops=49,968)

  • Index Cond: (npatronal = empresas_sucursales.nro_patronal)
  • Filter: (SubPlan 7)
  • Rows Removed by Filter: 12
54.          

SubPlan (forIndex Scan)

55. 3,310.770 18,761.030 ↑ 1.0 1 1,103,590

Limit (cost=9.06..9.06 rows=1 width=20) (actual time=0.016..0.017 rows=1 loops=1,103,590)

56. 4,414.360 15,450.260 ↑ 2.0 1 1,103,590

Sort (cost=9.06..9.06 rows=2 width=20) (actual time=0.014..0.014 rows=1 loops=1,103,590)

  • Sort Key: empleados_movimientos_3.fecha, empleados_movimientos_3.id
  • Sort Method: quicksort Memory: 25kB
57. 5,519.260 11,035.900 ↑ 1.0 2 1,103,590

Result (cost=0.00..9.05 rows=2 width=20) (actual time=0.005..0.010 rows=2 loops=1,103,590)

  • One-Time Filter: (empleados_2.empresa_id = emp.id)
58. 5,516.640 5,516.640 ↑ 1.0 2 1,103,328

Index Scan using empleados_movimientos_empleado_id_idx on empleados_movimientos empleados_movimientos_3 (cost=0.00..9.04 rows=2 width=20) (actual time=0.003..0.005 rows=2 loops=1,103,328)

  • Index Cond: (empleado_id = empleados_2.id)
  • Rows Removed by Index Recheck: 0
59. 1,977.128 1,977.128 ↓ 0.0 0 494,282

Index Scan using persons_id_idx on persons persons_1 (cost=0.00..6.02 rows=1 width=8) (actual time=0.003..0.004 rows=0 loops=494,282)

  • Index Cond: (id = empleados_2.persona_id)
  • Rows Removed by Index Recheck: 0
  • Filter: ((sexo)::text = 'F'::text)
  • Rows Removed by Filter: 1
60. 149.904 1,848.816 ↑ 1.0 1 49,968

Limit (cost=7.19..7.19 rows=1 width=134) (actual time=0.036..0.037 rows=1 loops=49,968)

61. 199.872 1,698.912 ↑ 1.0 1 49,968

Sort (cost=7.19..7.19 rows=1 width=134) (actual time=0.034..0.034 rows=1 loops=49,968)

  • Sort Key: cambio_situacion_empresa.fecha_creacion, cambio_situacion_empresa.id_cambio_situacion_empresa
  • Sort Method: quicksort Memory: 25kB
62. 617.405 1,499.040 ↑ 1.0 1 49,968

Nested Loop (cost=0.00..7.18 rows=1 width=134) (actual time=0.018..0.030 rows=1 loops=49,968)

  • Join Filter: (cambio_situacion_empresa.situacion_act = situacion_emp.id_situacion)
  • Rows Removed by Join Filter: 7
63. 499.680 499.680 ↑ 1.0 1 49,968

Index Scan using cambio_situacion_empresa_nro_patronal_idx on cambio_situacion_empresa (cost=0.00..6.02 rows=1 width=20) (actual time=0.007..0.010 rows=1 loops=49,968)

  • Index Cond: (nro_patronal = empresas_sucursales.nro_patronal)
64. 381.955 381.955 ↑ 1.0 7 54,565

Seq Scan on situacion_emp (cost=0.00..1.07 rows=7 width=122) (actual time=0.002..0.007 rows=7 loops=54,565)