explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C154 : Optimization for: Optimization for: plan #FiKi; plan #ubkK

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 32.297 185.683 ↑ 6.0 4,616 1

Merge Left Join (cost=15,262.66..1,323,696.30 rows=27,547 width=1,103) (actual time=52.160..185.683 rows=4,616 loops=1)

  • Merge Cond: (pro.pro_id = buf.pro_id)
2. 2.062 56.379 ↓ 1.0 4,616 1

Merge Left Join (cost=14,937.27..15,144.09 rows=4,529 width=973) (actual time=46.322..56.379 rows=4,616 loops=1)

  • Merge Cond: (pro.pro_id = pft.pro_id)
3. 4.270 53.056 ↓ 1.0 4,616 1

Merge Left Join (cost=14,936.99..14,960.43 rows=4,529 width=646) (actual time=46.303..53.056 rows=4,616 loops=1)

  • Merge Cond: (pro.pro_id = saldoinv.pro_id)
4. 5.387 25.717 ↓ 1.0 4,616 1

Sort (cost=1,341.76..1,353.08 rows=4,529 width=646) (actual time=24.281..25.717 rows=4,616 loops=1)

  • Sort Key: pro.pro_id
  • Sort Method: quicksort Memory: 1422kB
5. 1.777 20.330 ↓ 1.0 4,616 1

Hash Left Join (cost=534.89..1,066.74 rows=4,529 width=646) (actual time=7.546..20.330 rows=4,616 loops=1)

  • Hash Cond: (pro.cla1_id = cla1.cla2_id)
6. 2.829 18.365 ↓ 1.0 4,616 1

Hash Right Join (cost=510.92..980.49 rows=4,529 width=646) (actual time=7.353..18.365 rows=4,616 loops=1)

  • Hash Cond: (pro1.pro_id = pro.pro_id)
7. 1.851 10.205 ↓ 1.0 4,616 1

Hash Right Join (cost=187.01..594.71 rows=4,500 width=44) (actual time=2.011..10.205 rows=4,616 loops=1)

  • Hash Cond: (pem.prod1_id = pro1.prod1_id)
8. 4.409 6.398 ↓ 1.1 4,376 1

Hash Join (cost=1.77..352.22 rows=4,163 width=36) (actual time=0.041..6.398 rows=4,376 loops=1)

  • Hash Cond: (pem.emb_id = emb.emb_id)
9. 1.972 1.972 ↓ 1.1 4,376 1

Seq Scan on producto_embalaje pem (cost=0.00..189.14 rows=4,163 width=12) (actual time=0.011..1.972 rows=4,376 loops=1)

  • Filter: (ebp_tipo = 2)
  • Rows Removed by Filter: 4234
10. 0.009 0.017 ↑ 1.0 34 1

Hash (cost=1.34..1.34 rows=34 width=18) (actual time=0.017..0.017 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
11. 0.008 0.008 ↑ 1.0 34 1

Seq Scan on embalaje emb (cost=0.00..1.34 rows=34 width=18) (actual time=0.004..0.008 rows=34 loops=1)

12. 0.769 1.956 ↓ 1.0 4,616 1

Hash (cost=129.00..129.00 rows=4,500 width=12) (actual time=1.956..1.956 rows=4,616 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 263kB
13. 1.187 1.187 ↓ 1.0 4,616 1

Seq Scan on producto1 pro1 (cost=0.00..129.00 rows=4,500 width=12) (actual time=0.010..1.187 rows=4,616 loops=1)

14. 2.858 5.331 ↓ 1.0 4,616 1

Hash (cost=267.29..267.29 rows=4,529 width=606) (actual time=5.331..5.331 rows=4,616 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 895kB
15. 2.473 2.473 ↓ 1.0 4,616 1

Seq Scan on producto pro (cost=0.00..267.29 rows=4,529 width=606) (actual time=0.024..2.473 rows=4,616 loops=1)

16. 0.066 0.188 ↑ 1.0 421 1

Hash (cost=18.70..18.70 rows=422 width=8) (actual time=0.188..0.188 rows=421 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
17. 0.122 0.122 ↑ 1.0 422 1

Index Only Scan using idx_vclasificacion_ids on v_clasificacion1tot cla1 (cost=0.27..18.70 rows=422 width=8) (actual time=0.009..0.122 rows=422 loops=1)

  • Heap Fetches: 0
18. 1.711 23.069 ↓ 73.4 3,888 1

Sort (cost=13,595.23..13,595.37 rows=53 width=4) (actual time=22.016..23.069 rows=3,888 loops=1)

  • Sort Key: saldoinv.pro_id
  • Sort Method: quicksort Memory: 279kB
19. 21.358 21.358 ↓ 73.4 3,888 1

Index Only Scan using saldoinv_pkey on saldoinv (cost=0.41..13,593.72 rows=53 width=4) (actual time=15.562..21.358 rows=3,888 loops=1)

  • Index Cond: (bod_id = 1)
  • Filter: ((sal_periodo)::double precision = date_part('year'::text, (('now'::cstring)::date)::timestamp without time zone))
  • Rows Removed by Filter: 7121
  • Heap Fetches: 4452
20. 1.261 1.261 ↑ 1.0 1,058 1

Index Scan using idx_producto_fichatecnica_pro_id on producto_fichatecnica pft (cost=0.28..158.77 rows=1,085 width=331) (actual time=0.016..1.261 rows=1,058 loops=1)

  • Filter: (idi_id = 4)
  • Rows Removed by Filter: 342
21. 8.517 8.517 ↑ 6.8 4,073 1

Index Only Scan using idx_buffer_pro_id on buffer buf (cost=0.29..732.83 rows=27,547 width=4) (actual time=0.037..8.517 rows=4,073 loops=1)

  • Heap Fetches: 11444
22.          

SubPlan (forMerge Left Join)

23. 4.616 32.312 ↑ 1.0 1 4,616

Aggregate (cost=12.20..12.21 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=4,616)

24. 5.878 27.696 ↑ 3.0 1 4,616

Nested Loop (cost=0.56..12.19 rows=3 width=8) (actual time=0.004..0.006 rows=1 loops=4,616)

25. 13.848 13.848 ↑ 3.0 1 4,616

Index Scan using idx_productos_modelos_hermanos_prod1_id on productos_modelos_hermanos pmh (cost=0.28..4.68 rows=3 width=4) (actual time=0.003..0.003 rows=1 loops=4,616)

  • Index Cond: (pro1.prod1_id = prod1)
  • Filter: (pro1.mdl_id <> mdl_id)
  • Rows Removed by Filter: 0
26. 7.970 7.970 ↑ 1.0 1 3,985

Index Scan using modelo_pk on modelo mdl (cost=0.28..2.50 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=3,985)

  • Index Cond: (mdl_id = pmh.mdl_id)
27. 0.332 4.110 ↓ 16.1 645 1

HashAggregate (cost=324.20..324.60 rows=40 width=4) (actual time=4.039..4.110 rows=645 loops=1)

  • Group Key: dpromocion_producto.pro_id
28. 0.053 3.778 ↓ 16.1 645 1

Append (cost=21.75..324.10 rows=40 width=4) (actual time=0.444..3.778 rows=645 loops=1)

29. 0.066 0.464 ↓ 10.6 149 1

HashAggregate (cost=21.75..21.89 rows=14 width=4) (actual time=0.444..0.464 rows=149 loops=1)

  • Group Key: dpromocion_producto.pro_id
30. 0.035 0.398 ↓ 10.7 150 1

Nested Loop (cost=20.85..21.71 rows=14 width=4) (actual time=0.293..0.398 rows=150 loops=1)

31. 0.007 0.248 ↓ 11.5 23 1

Unique (cost=20.57..20.58 rows=2 width=4) (actual time=0.239..0.248 rows=23 loops=1)

32. 0.021 0.241 ↓ 11.5 23 1

Sort (cost=20.57..20.57 rows=2 width=4) (actual time=0.239..0.241 rows=23 loops=1)

  • Sort Key: dva.dpv_id
  • Sort Method: quicksort Memory: 26kB
33. 0.009 0.220 ↓ 11.5 23 1

Nested Loop (cost=0.27..20.56 rows=2 width=4) (actual time=0.050..0.220 rows=23 loops=1)

34. 0.142 0.142 ↓ 11.5 23 1

Seq Scan on promocion prom (cost=0.00..15.56 rows=2 width=4) (actual time=0.032..0.142 rows=23 loops=1)

  • Filter: (ppr_activo AND (('now'::cstring)::date <= ppr_fechafinal))
  • Rows Removed by Filter: 467
35. 0.069 0.069 ↑ 1.0 1 23

Index Scan using dpromocion_validacion_llave_cabecera on dpromocion_validacion dva (cost=0.27..2.49 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=23)

  • Index Cond: (ppr_id = prom.ppr_id)
36. 0.115 0.115 ↑ 1.0 7 23

Index Scan using dpromocion_producto_llave_cabecera on dpromocion_producto (cost=0.28..0.50 rows=7 width=8) (actual time=0.003..0.005 rows=7 loops=23)

  • Index Cond: (dpv_id = dva.dpv_id)
  • Filter: (cla1_id IS NULL)
  • Rows Removed by Filter: 1
37. 0.094 3.261 ↓ 19.1 496 1

Unique (cost=301.68..301.81 rows=26 width=4) (actual time=3.112..3.261 rows=496 loops=1)

38. 0.255 3.167 ↓ 19.1 496 1

Sort (cost=301.68..301.75 rows=26 width=4) (actual time=3.111..3.167 rows=496 loops=1)

  • Sort Key: pro_1.pro_id
  • Sort Method: quicksort Memory: 48kB
39. 1.820 2.912 ↓ 19.1 496 1

Hash Semi Join (cost=21.60..301.07 rows=26 width=4) (actual time=0.415..2.912 rows=496 loops=1)

  • Hash Cond: (pro_1.cla1_id = dpromocion_producto_1.cla1_id)
40. 0.776 0.776 ↓ 1.0 4,616 1

Seq Scan on producto pro_1 (cost=0.00..267.29 rows=4,529 width=8) (actual time=0.009..0.776 rows=4,616 loops=1)

41. 0.011 0.316 ↓ 25.0 25 1

Hash (cost=21.59..21.59 rows=1 width=4) (actual time=0.316..0.316 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
42. 0.017 0.305 ↓ 25.0 25 1

Nested Loop (cost=20.85..21.59 rows=1 width=4) (actual time=0.216..0.305 rows=25 loops=1)

43. 0.005 0.219 ↓ 11.5 23 1

Unique (cost=20.57..20.58 rows=2 width=4) (actual time=0.211..0.219 rows=23 loops=1)

44. 0.021 0.214 ↓ 11.5 23 1

Sort (cost=20.57..20.57 rows=2 width=4) (actual time=0.210..0.214 rows=23 loops=1)

  • Sort Key: dva_1.dpv_id
  • Sort Method: quicksort Memory: 26kB
45. 0.013 0.193 ↓ 11.5 23 1

Nested Loop (cost=0.27..20.56 rows=2 width=4) (actual time=0.051..0.193 rows=23 loops=1)

46. 0.111 0.111 ↓ 11.5 23 1

Seq Scan on promocion prom_1 (cost=0.00..15.56 rows=2 width=4) (actual time=0.029..0.111 rows=23 loops=1)

  • Filter: (ppr_activo AND (('now'::cstring)::date <= ppr_fechafinal))
  • Rows Removed by Filter: 467
47. 0.069 0.069 ↑ 1.0 1 23

Index Scan using dpromocion_validacion_llave_cabecera on dpromocion_validacion dva_1 (cost=0.27..2.49 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=23)

  • Index Cond: (ppr_id = prom_1.ppr_id)
48. 0.069 0.069 ↑ 1.0 1 23

Index Scan using dpromocion_producto_llave_cabecera on dpromocion_producto dpromocion_producto_1 (cost=0.28..0.50 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=23)

  • Index Cond: (dpv_id = dva_1.dpv_id)
  • Filter: (cla1_id IS NOT NULL)
  • Rows Removed by Filter: 7
49. 0.000 50.776 ↓ 0.0 0 4,616

Limit (cost=9.58..9.59 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=4,616)

50. 13.848 50.776 ↓ 0.0 0 4,616

Sort (cost=9.58..9.59 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=4,616)

  • Sort Key: cor.cor_fecha_disponibilidad
  • Sort Method: quicksort Memory: 25kB
51. 3.278 36.928 ↓ 0.0 0 4,616

Nested Loop (cost=0.42..9.57 rows=1 width=8) (actual time=0.007..0.008 rows=0 loops=4,616)

52. 18.464 18.464 ↑ 1.5 2 4,616

Index Scan using idx_dordcom_pro_id on dordcom dor (cost=0.28..4.63 rows=3 width=4) (actual time=0.003..0.004 rows=2 loops=4,616)

  • Index Cond: (pro_id = pro.pro_id)
53. 15.186 15.186 ↓ 0.0 0 7,593

Index Scan using cordcom_pkey on cordcom cor (cost=0.14..1.64 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=7,593)

  • Index Cond: (cor_id = dor.cor_id)
  • Filter: ((cor_estado <> 7) AND (cor_estado <> 6) AND ((cor_estado = 3) OR (cor_estado = 4) OR (cor_estado = 5)))
  • Rows Removed by Filter: 1
54. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on producto_llegado prll (cost=1.40..3.56 rows=1 width=0) (never executed)

  • Recheck Cond: (pro_id = pro.pro_id)
  • Filter: (prll_estado = ANY ('{2,4}'::integer[]))
55. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_producto_llegado_pro_id (cost=0.00..1.40 rows=2 width=0) (never executed)

  • Index Cond: (pro_id = pro.pro_id)
56. 0.402 0.402 ↑ 2.0 1 1

Seq Scan on producto_llegado prll_1 (cost=0.00..50.73 rows=2 width=4) (actual time=0.147..0.402 rows=1 loops=1)

  • Filter: (prll_estado = ANY ('{2,4}'::integer[]))
  • Rows Removed by Filter: 2377
57. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.67..15.26 rows=1 width=0) (never executed)

  • Join Filter: (COALESCE(dpv.dpv_prod_mes, 0) = 0)
58. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.39..12.49 rows=1 width=8) (never executed)

59. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.10..12.17 rows=1 width=12) (never executed)

60. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.83..10.21 rows=6 width=16) (never executed)

61. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.55..7.54 rows=1 width=8) (never executed)

62. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_producto_mes on dpromocion_validacion dpv (cost=0.27..2.51 rows=2 width=12) (never executed)

  • Index Cond: (dpv_prod_mes = 1)
63. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_promocion on promocion prm (cost=0.27..2.51 rows=1 width=4) (never executed)

  • Index Cond: (ppr_id = dpv.ppr_id)
  • Filter: (ppr_activo AND (('now'::cstring)::date >= ppr_fechainicio) AND (('now'::cstring)::date <= ppr_fechafinal))
64. 0.000 0.000 ↓ 0.0 0

Index Scan using dpromocion_cantidad_llave_cabecera on dpromocion_cantidad dpc (cost=0.28..2.62 rows=5 width=12) (never executed)

  • Index Cond: (dpv_id = dpv.dpv_id)
65. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_dpromocion_promocion on dpromocion_producto dpp2 (cost=0.28..0.32 rows=1 width=4) (never executed)

  • Index Cond: (dpp_id = dpc.dpp_id)
  • Filter: (pro_id = pro.pro_id)
66. 0.000 0.000 ↓ 0.0 0

Index Only Scan using dpromocion_despacho_llave_cabecera on dpromocion_despacho dpd (cost=0.28..0.31 rows=1 width=4) (never executed)

  • Index Cond: (dpct_id = dpc.dpct_id)
  • Heap Fetches: 0
67. 0.000 0.000 ↓ 0.0 0

Index Only Scan using dpromocion_producto_llave_cabecera on dpromocion_producto dpp (cost=0.28..2.68 rows=7 width=4) (never executed)

  • Index Cond: (dpv_id = dpv.dpv_id)
  • Heap Fetches: 0
68. 0.056 0.837 ↓ 23.0 138 1

Nested Loop Left Join (cost=1.67..16.54 rows=6 width=4) (actual time=0.109..0.837 rows=138 loops=1)

  • Join Filter: (dpv_1.dpv_prod_mes = 1)
69. 0.021 0.505 ↓ 23.0 138 1

Nested Loop Left Join (cost=1.39..14.58 rows=6 width=8) (actual time=0.102..0.505 rows=138 loops=1)

70. 0.019 0.098 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.83..10.31 rows=1 width=8) (actual time=0.080..0.098 rows=1 loops=1)

  • Join Filter: (COALESCE(dpv_1.dpv_prod_mes, 0) = 0)
  • Rows Removed by Join Filter: 138
71. 0.004 0.042 ↑ 1.0 1 1

Nested Loop (cost=0.55..7.54 rows=1 width=8) (actual time=0.024..0.042 rows=1 loops=1)

72. 0.008 0.008 ↓ 3.0 6 1

Index Scan using idx_producto_mes on dpromocion_validacion dpv_1 (cost=0.27..2.51 rows=2 width=12) (actual time=0.006..0.008 rows=6 loops=1)

  • Index Cond: (dpv_prod_mes = 1)
73. 0.030 0.030 ↓ 0.0 0 6

Index Scan using pk_promocion on promocion prm_1 (cost=0.27..2.51 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=6)

  • Index Cond: (ppr_id = dpv_1.ppr_id)
  • Filter: (ppr_activo AND (('now'::cstring)::date >= ppr_fechainicio) AND (('now'::cstring)::date <= ppr_fechafinal))
  • Rows Removed by Filter: 1
74. 0.037 0.037 ↓ 19.7 138 1

Index Only Scan using dpromocion_producto_llave_cabecera on dpromocion_producto dpp_1 (cost=0.28..2.68 rows=7 width=4) (actual time=0.011..0.037 rows=138 loops=1)

  • Index Cond: (dpv_id = dpv_1.dpv_id)
  • Heap Fetches: 138
75. 0.084 0.386 ↓ 27.6 138 1

Nested Loop Left Join (cost=0.56..4.22 rows=5 width=8) (actual time=0.020..0.386 rows=138 loops=1)

76. 0.026 0.026 ↓ 27.6 138 1

Index Scan using dpromocion_cantidad_llave_cabecera on dpromocion_cantidad dpc_1 (cost=0.28..2.62 rows=5 width=12) (actual time=0.009..0.026 rows=138 loops=1)

  • Index Cond: (dpv_id = dpv_1.dpv_id)
77. 0.276 0.276 ↑ 1.0 1 138

Index Only Scan using dpromocion_despacho_llave_cabecera on dpromocion_despacho dpd_1 (cost=0.28..0.31 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=138)

  • Index Cond: (dpct_id = dpc_1.dpct_id)
  • Heap Fetches: 138
78. 0.276 0.276 ↑ 1.0 1 138

Index Scan using pk_dpromocion_promocion on dpromocion_producto dpp2_1 (cost=0.28..0.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=138)

  • Index Cond: (dpp_id = dpc_1.dpp_id)
79. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.28..6.81 rows=1 width=0) (never executed)

80. 0.000 0.000 ↓ 0.0 0

Seq Scan on cordcom cor_1 (cost=0.00..4.30 rows=1 width=4) (never executed)

  • Filter: (cor_estado = 5)
81. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_dordcom_corpro on dordcom dor_1 (cost=0.28..2.50 rows=1 width=4) (never executed)

  • Index Cond: ((cor_id = cor_1.cor_id) AND (pro_id = pro.pro_id))
  • Heap Fetches: 0
82. 0.000 0.053 ↓ 0.0 0 1

Nested Loop (cost=0.28..33.92 rows=72 width=4) (actual time=0.053..0.053 rows=0 loops=1)

83. 0.053 0.053 ↓ 0.0 0 1

Seq Scan on cordcom cor_2 (cost=0.00..4.30 rows=1 width=4) (actual time=0.053..0.053 rows=0 loops=1)

  • Filter: (cor_estado = 5)
  • Rows Removed by Filter: 106
84. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_dordcom_corpro on dordcom dor_2 (cost=0.28..28.90 rows=72 width=8) (never executed)

  • Index Cond: (cor_id = cor_2.cor_id)
  • Heap Fetches: 0