explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 10yb : Optimization for: Optimization for: Optimization for: plan #rjKU; plan #u5Yc; plan #UKLH

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 202.056 14,708.162 ↓ 1.1 58,964 1

Append (cost=233,766.36..240,915.00 rows=53,068 width=579) (actual time=12,081.453..14,708.162 rows=58,964 loops=1)

2. 3,861.226 12,242.813 ↓ 1.0 52,351 1

Sort (cost=233,766.36..233,897.13 rows=52,309 width=461) (actual time=12,081.450..12,242.813 rows=52,351 loops=1)

  • Sort Key: empresas_sucursales.nro_patronal
  • Sort Method: external merge Disk: 19864kB
3. 644.862 8,381.587 ↓ 1.0 52,351 1

Hash Left Join (cost=3,149.63..221,874.20 rows=52,309 width=461) (actual time=657.004..8,381.587 rows=52,351 loops=1)

  • Hash Cond: (empresas_sucursales.id_tipo_inscripcion = empresas_tipo_inscripcion.id_tipo_inscripcion)
4. 294.278 3,600.984 ↓ 1.0 52,351 1

Hash Left Join (cost=3,148.56..10,903.29 rows=52,309 width=441) (actual time=656.872..3,600.984 rows=52,351 loops=1)

  • Hash Cond: (empresas_sucursales.type = empresas_sucursales_types.id)
5. 291.499 3,306.695 ↓ 1.0 52,351 1

Hash Left Join (cost=3,147.51..10,182.99 rows=52,309 width=434) (actual time=656.847..3,306.695 rows=52,351 loops=1)

  • Hash Cond: (empresas_sucursales.id_activ_econ = actividad_econ.id_activ_econ)
6. 309.877 3,013.634 ↓ 1.0 52,351 1

Hash Left Join (cost=3,122.09..9,438.31 rows=52,309 width=380) (actual time=655.272..3,013.634 rows=52,351 loops=1)

  • Hash Cond: (empresas_sucursales.distrito_id = distritos.id)
7. 319.494 2,703.714 ↓ 1.0 52,351 1

Hash Left Join (cost=3,120.68..8,718.08 rows=52,309 width=369) (actual time=655.210..2,703.714 rows=52,351 loops=1)

  • Hash Cond: (empresas_sucursales.city_id = city.id)
8. 297.474 2,381.262 ↓ 1.0 52,351 1

Hash Left Join (cost=3,107.97..7,988.33 rows=52,309 width=333) (actual time=652.236..2,381.262 rows=52,351 loops=1)

  • Hash Cond: (emp.id_categoria_empresa = empresas_categorias.id_categoria_empresa)
9. 277.420 2,083.773 ↓ 1.0 52,351 1

Hash Left Join (cost=3,106.88..7,760.90 rows=52,309 width=319) (actual time=652.207..2,083.773 rows=52,351 loops=1)

  • Hash Cond: (emp.type = empresas_types.id)
10. 469.998 1,806.265 ↓ 1.0 52,351 1

Hash Join (cost=3,105.20..7,039.98 rows=52,309 width=304) (actual time=652.098..1,806.265 rows=52,351 loops=1)

  • Hash Cond: (empresas_sucursales.empresa_id = emp.id)
11. 684.232 684.232 ↓ 1.0 52,353 1

Seq Scan on empresas_sucursales (cost=0.00..2,888.56 rows=52,312 width=239) (actual time=0.038..684.232 rows=52,353 loops=1)

  • Filter: (type = 1)
  • Rows Removed by Filter: 6652
12. 248.688 652.035 ↓ 1.0 52,210 1

Hash (cost=2,452.64..2,452.64 rows=52,205 width=81) (actual time=652.035..652.035 rows=52,210 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 5856kB
13. 403.347 403.347 ↓ 1.0 52,210 1

Seq Scan on empresas emp (cost=0.00..2,452.64 rows=52,205 width=81) (actual time=0.017..403.347 rows=52,210 loops=1)

  • Filter: (((document)::text <> '0'::text) AND ((document)::text <> ''::text) AND ((document)::text <> '-'::text))
  • Rows Removed by Filter: 2
14. 0.048 0.088 ↑ 1.0 30 1

Hash (cost=1.30..1.30 rows=30 width=23) (actual time=0.088..0.088 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
15. 0.040 0.040 ↑ 1.0 30 1

Seq Scan on empresas_types (cost=0.00..1.30 rows=30 width=23) (actual time=0.005..0.040 rows=30 loops=1)

16. 0.007 0.015 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
17. 0.008 0.008 ↑ 1.0 4 1

Seq Scan on empresas_categorias (cost=0.00..1.04 rows=4 width=18) (actual time=0.004..0.008 rows=4 loops=1)

18. 0.286 2.958 ↑ 1.0 254 1

Hash (cost=9.54..9.54 rows=254 width=40) (actual time=2.958..2.958 rows=254 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
19. 2.672 2.672 ↑ 1.0 254 1

Seq Scan on city (cost=0.00..9.54 rows=254 width=40) (actual time=0.006..2.672 rows=254 loops=1)

20. 0.023 0.043 ↑ 1.0 18 1

Hash (cost=1.18..1.18 rows=18 width=19) (actual time=0.043..0.043 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
21. 0.020 0.020 ↑ 1.0 18 1

Seq Scan on distritos (cost=0.00..1.18 rows=18 width=19) (actual time=0.005..0.020 rows=18 loops=1)

22. 0.783 1.562 ↑ 1.0 730 1

Hash (cost=16.30..16.30 rows=730 width=58) (actual time=1.562..1.562 rows=730 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 66kB
23. 0.779 0.779 ↑ 1.0 730 1

Seq Scan on actividad_econ (cost=0.00..16.30 rows=730 width=58) (actual time=0.006..0.779 rows=730 loops=1)

24. 0.004 0.011 ↑ 1.0 1 1

Hash (cost=1.04..1.04 rows=1 width=23) (actual time=0.011..0.011 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
25. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on empresas_sucursales_types (cost=0.00..1.04 rows=1 width=23) (actual time=0.006..0.007 rows=1 loops=1)

  • Filter: (id = 1)
  • Rows Removed by Filter: 2
26. 0.005 0.012 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=24) (actual time=0.012..0.012 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
27. 0.007 0.007 ↑ 1.0 3 1

Seq Scan on empresas_tipo_inscripcion (cost=0.00..1.03 rows=3 width=24) (actual time=0.005..0.007 rows=3 loops=1)

28.          

SubPlan (forHash Left Join)

29. 366.457 4,135.729 ↑ 1.0 1 52,351

Limit (cost=0.42..4.02 rows=1 width=27) (actual time=0.078..0.079 rows=1 loops=52,351)

30. 705.702 3,769.272 ↑ 2.0 1 52,351

Nested Loop (cost=0.42..7.62 rows=2 width=27) (actual time=0.072..0.072 rows=1 loops=52,351)

  • Join Filter: (cambio_situacion_empresa.situacion_act = situacion_emp.id_situacion)
  • Rows Removed by Join Filter: 3
31. 2,669.901 2,669.901 ↑ 2.0 1 52,351

Index Scan using cambio_situacion_empresa_nro_patronal_fecha_creacion_id_cam_idx on cambio_situacion_empresa (cost=0.42..6.32 rows=2 width=20) (actual time=0.051..0.051 rows=1 loops=52,351)

  • Index Cond: (nro_patronal = empresas_sucursales.nro_patronal)
32. 393.657 393.669 ↑ 1.4 5 43,741

Materialize (cost=0.00..1.10 rows=7 width=15) (actual time=0.001..0.009 rows=5 loops=43,741)

33. 0.012 0.012 ↑ 1.0 7 1

Seq Scan on situacion_emp (cost=0.00..1.07 rows=7 width=15) (actual time=0.005..0.012 rows=7 loops=1)

34. 84.184 2,263.293 ↓ 8.7 6,613 1

Sort (cost=6,485.29..6,487.19 rows=759 width=463) (actual time=2,246.140..2,263.293 rows=6,613 loops=1)

  • Sort Key: empresas_sucursales_1.nro_patronal
  • Sort Method: quicksort Memory: 4555kB
35. 164.482 2,179.109 ↓ 8.7 6,613 1

Nested Loop Left Join (cost=14.82..6,448.98 rows=759 width=463) (actual time=8.360..2,179.109 rows=6,613 loops=1)

36. 81.194 1,300.423 ↓ 8.7 6,613 1

Nested Loop Left Join (cost=14.82..3,367.38 rows=759 width=443) (actual time=0.863..1,300.423 rows=6,613 loops=1)

37. 46.702 1,040.678 ↓ 8.7 6,613 1

Hash Left Join (cost=14.82..3,316.51 rows=759 width=389) (actual time=0.824..1,040.678 rows=6,613 loops=1)

  • Hash Cond: (empresas_sucursales_1.distrito_id = distritos_1.id)
38. 50.340 993.934 ↓ 8.7 6,613 1

Nested Loop Left Join (cost=13.42..3,304.68 rows=759 width=378) (actual time=0.766..993.934 rows=6,613 loops=1)

39. 91.840 917.142 ↓ 8.7 6,613 1

Nested Loop Left Join (cost=13.42..3,283.66 rows=759 width=364) (actual time=0.757..917.142 rows=6,613 loops=1)

40. 69.334 633.525 ↓ 8.7 6,613 1

Nested Loop (cost=13.42..3,262.64 rows=759 width=349) (actual time=0.714..633.525 rows=6,613 loops=1)

41. 49.281 165.071 ↓ 8.8 6,652 1

Hash Left Join (cost=13.13..1,506.07 rows=759 width=345) (actual time=0.668..165.071 rows=6,652 loops=1)

  • Hash Cond: (empresas_sucursales_1.city_id = city_1.id)
42. 32.138 115.235 ↓ 8.8 6,652 1

Merge Right Join (cost=0.41..1,482.95 rows=759 width=309) (actual time=0.090..115.235 rows=6,652 loops=1)

  • Merge Cond: (empresas_sucursales_types_1.id = empresas_sucursales_1.type)
43. 0.023 0.023 ↑ 1.0 3 1

Index Scan using empresas_sucursales_types_pkey on empresas_sucursales_types empresas_sucursales_types_1 (cost=0.13..6.17 rows=3 width=23) (actual time=0.017..0.023 rows=3 loops=1)

44. 83.074 83.074 ↓ 8.8 6,652 1

Index Scan using empresas_sucursales_type_idx on empresas_sucursales empresas_sucursales_1 (cost=0.28..1,467.28 rows=759 width=302) (actual time=0.051..83.074 rows=6,652 loops=1)

45. 0.287 0.555 ↑ 1.0 254 1

Hash (cost=9.54..9.54 rows=254 width=40) (actual time=0.555..0.555 rows=254 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
46. 0.268 0.268 ↑ 1.0 254 1

Seq Scan on city city_1 (cost=0.00..9.54 rows=254 width=40) (actual time=0.008..0.268 rows=254 loops=1)

47. 399.120 399.120 ↑ 1.0 1 6,652

Index Scan using empresas_pk on empresas emp_1 (cost=0.29..2.30 rows=1 width=20) (actual time=0.056..0.060 rows=1 loops=6,652)

  • Index Cond: (id = empresas_sucursales_1.empresa_id)
  • Filter: (((document)::text <> '0'::text) AND ((document)::text <> ''::text) AND ((document)::text <> '-'::text))
  • Rows Removed by Filter: 0
48. 191.777 191.777 ↑ 1.0 1 6,613

Index Scan using empresas_types_id_idx on empresas_types empresas_types_1 (cost=0.00..0.02 rows=1 width=23) (actual time=0.027..0.029 rows=1 loops=6,613)

  • Index Cond: (emp_1.type = id)
49. 26.452 26.452 ↓ 0.0 0 6,613

Index Scan using empresas_categorias_id_categoria_empresa_idx on empresas_categorias empresas_categorias_1 (cost=0.00..0.02 rows=1 width=18) (actual time=0.004..0.004 rows=0 loops=6,613)

  • Index Cond: (emp_1.id_categoria_empresa = id_categoria_empresa)
50. 0.022 0.042 ↑ 1.0 18 1

Hash (cost=1.18..1.18 rows=18 width=19) (actual time=0.042..0.042 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
51. 0.020 0.020 ↑ 1.0 18 1

Seq Scan on distritos distritos_1 (cost=0.00..1.18 rows=18 width=19) (actual time=0.005..0.020 rows=18 loops=1)

52. 178.551 178.551 ↑ 1.0 1 6,613

Index Scan using actividad_econ_id_activ_econ_idx on actividad_econ actividad_econ_1 (cost=0.00..0.06 rows=1 width=58) (actual time=0.023..0.027 rows=1 loops=6,613)

  • Index Cond: (empresas_sucursales_1.id_activ_econ = id_activ_econ)
53. 158.712 158.712 ↑ 1.0 1 6,613

Index Scan using empresas_tipo_inscripcion_id_tipo_inscripcion_idx on empresas_tipo_inscripcion empresas_tipo_inscripcion_1 (cost=0.00..0.03 rows=1 width=24) (actual time=0.018..0.024 rows=1 loops=6,613)

  • Index Cond: (empresas_sucursales_1.id_tipo_inscripcion = id_tipo_inscripcion)
54.          

SubPlan (forNested Loop Left Join)

55. 72.743 555.492 ↑ 1.0 1 6,613

Limit (cost=0.42..4.02 rows=1 width=27) (actual time=0.081..0.084 rows=1 loops=6,613)

56. 136.242 482.749 ↑ 2.0 1 6,613

Nested Loop (cost=0.42..7.62 rows=2 width=27) (actual time=0.073..0.073 rows=1 loops=6,613)

  • Join Filter: (cambio_situacion_empresa_1.situacion_act = situacion_emp_1.id_situacion)
  • Rows Removed by Join Filter: 4
57. 277.746 277.746 ↑ 2.0 1 6,613

Index Scan using cambio_situacion_empresa_nro_patronal_fecha_creacion_id_cam_idx on cambio_situacion_empresa cambio_situacion_empresa_1 (cost=0.42..6.32 rows=2 width=20) (actual time=0.042..0.042 rows=1 loops=6,613)

  • Index Cond: (nro_patronal = empresas_sucursales_1.nro_patronal)
58. 68.747 68.761 ↑ 1.4 5 6,251

Materialize (cost=0.00..1.10 rows=7 width=15) (actual time=0.001..0.011 rows=5 loops=6,251)

59. 0.014 0.014 ↑ 1.2 6 1

Seq Scan on situacion_emp situacion_emp_1 (cost=0.00..1.07 rows=7 width=15) (actual time=0.006..0.014 rows=6 loops=1)