explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2wUp

Settings
# exclusive inclusive rows x rows loops node
1. 0.646 11,904.783 ↓ 7.8 2,913 1

Subquery Scan on v_dpromocion (cost=65,645.51..65,650.17 rows=373 width=148) (actual time=11,903.941..11,904.783 rows=2,913 loops=1)

2. 6.330 11,904.137 ↓ 7.8 2,913 1

Sort (cost=65,645.51..65,646.44 rows=373 width=192) (actual time=11,903.937..11,904.137 rows=2,913 loops=1)

  • Sort Key: (COALESCE(reg.reg_nombre, 'SIN REGION'::character varying)), (CASE WHEN ((prm.dpct_cantidad_min)::integer = (prm.dpct_cantidad_validacion)::integer) THEN CASE WHEN (prm.dpv_prod_mes = 1) THEN ('A PARTIR DE '::text || ((prm.dpct_cantidad_validacion)::integer)::text) ELSE ('POR CADA '::text || ((prm.dpct_cantidad_validacion)::integer)::text) END WHEN ((prm.dpct_cantidad_max)::integer >= 10000) THEN (('POR '::text || ((prm.dpct_cantidad_min)::integer)::text) || ' O MÁS'::text) ELSE (((('ENTRE '::text || ((prm.dpct_cantidad_min)::integer)::text) || ' Y '::text) || ((prm.dpct_cantidad_max)::integer)::text) || CASE WHEN (COALESCE(prm.dpct_cantidad_validacion, '0'::numeric) > '0'::numeric) THEN (' POR CADA '::text || ((prm.dpct_cantidad_validacion)::integer)::text) ELSE ''::text END) END)
  • Sort Method: quicksort Memory: 506kB
3. 20.015 11,897.807 ↓ 7.8 2,913 1

Hash Right Join (cost=11,699.14..65,629.57 rows=373 width=192) (actual time=133.135..11,897.807 rows=2,913 loops=1)

  • Hash Cond: (pro1.cla1_id = prm.cla1_id)
  • Join Filter: (prm.cla1_id IS NOT NULL)
4. 11,711.768 11,868.113 ↑ 1.0 5,049 1

Hash Right Join (cost=11,652.72..64,529.92 rows=5,049 width=9,044) (actual time=123.126..11,868.113 rows=5,049 loops=1)

  • Hash Cond: (saldoinv_1.pro_id = pro.pro_id)
5. 79.989 103.002 ↓ 51.9 4,412 1

Bitmap Heap Scan on saldoinv saldoinv_1 (cost=7,207.79..23,478.73 rows=85 width=4) (actual time=65.680..103.002 rows=4,412 loops=1)

  • Recheck 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 Blocks: exact=12809
6. 23.013 23.013 ↓ 20.2 343,583 1

Bitmap Index Scan on idx_saldoinv_bod (cost=0.00..7,207.77 rows=17,034 width=0) (actual time=23.013..23.013 rows=343,583 loops=1)

  • Index Cond: (bod_id = 1)
7. 1.084 53.343 ↑ 1.0 5,049 1

Hash (cost=4,381.82..4,381.82 rows=5,049 width=8) (actual time=53.343..53.343 rows=5,049 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 262kB
8. 1.542 52.259 ↑ 1.0 5,049 1

Hash Left Join (cost=2,055.69..4,381.82 rows=5,049 width=8) (actual time=43.520..52.259 rows=5,049 loops=1)

  • Hash Cond: (cla1.cla1_id = jl_1.cla1_id)
9. 1.344 50.496 ↑ 1.0 5,049 1

Hash Left Join (cost=2,033.64..4,285.74 rows=5,049 width=12) (actual time=43.291..50.496 rows=5,049 loops=1)

  • Hash Cond: (cla1.cla1_id = jl.cla1_id)
10. 1.482 48.929 ↑ 1.0 5,049 1

Hash Left Join (cost=2,011.59..4,189.67 rows=5,049 width=12) (actual time=43.058..48.929 rows=5,049 loops=1)

  • Hash Cond: (pro1.prod1_id = p1.prod1_id)
11. 1.381 43.898 ↑ 1.0 5,049 1

Hash Right Join (cost=1,711.94..3,828.90 rows=5,049 width=16) (actual time=39.486..43.898 rows=5,049 loops=1)

  • Hash Cond: (saldoinv.pro_id = pro.pro_id)
12. 19.584 24.678 ↓ 70.2 702 1

Bitmap Heap Scan on saldoinv (cost=819.02..2,935.87 rows=10 width=4) (actual time=21.543..24.678 rows=702 loops=1)

  • Recheck Cond: (bod_id = 6)
  • Filter: ((sal_periodo)::double precision = date_part('year'::text, (('now'::cstring)::date)::timestamp without time zone))
  • Rows Removed by Filter: 661
  • Heap Blocks: exact=8944
13. 5.094 5.094 ↓ 20.6 39,874 1

Bitmap Index Scan on idx_saldoinv_bod (cost=0.00..819.02 rows=1,934 width=0) (actual time=5.094..5.094 rows=39,874 loops=1)

  • Index Cond: (bod_id = 6)
14. 1.448 17.839 ↑ 1.0 5,049 1

Hash (cost=829.81..829.81 rows=5,049 width=16) (actual time=17.839..17.839 rows=5,049 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 301kB
15. 1.379 16.391 ↑ 1.0 5,049 1

Hash Left Join (cost=543.85..829.81 rows=5,049 width=16) (actual time=9.913..16.391 rows=5,049 loops=1)

  • Hash Cond: (pro1.cla1_id = cla1.cla1_id)
16. 1.716 14.850 ↑ 1.0 5,049 1

Hash Left Join (cost=528.88..801.47 rows=5,049 width=12) (actual time=9.724..14.850 rows=5,049 loops=1)

  • Hash Cond: (pro.pro_id = sthi.pro_id)
17. 2.872 10.665 ↑ 1.0 5,049 1

Hash Right Join (cost=365.60..569.36 rows=5,049 width=12) (actual time=7.143..10.665 rows=5,049 loops=1)

  • Hash Cond: (pro1.pro_id = pro.pro_id)
18. 0.766 0.766 ↑ 1.0 5,049 1

Seq Scan on producto1 pro1 (cost=0.00..190.49 rows=5,049 width=32) (actual time=0.012..0.766 rows=5,049 loops=1)

19. 2.761 7.027 ↑ 1.0 5,049 1

Hash (cost=302.49..302.49 rows=5,049 width=20) (actual time=7.027..7.027 rows=5,049 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 320kB
20. 4.266 4.266 ↑ 1.0 5,049 1

Seq Scan on producto pro (cost=0.00..302.49 rows=5,049 width=20) (actual time=0.016..4.266 rows=5,049 loops=1)

21. 1.373 2.469 ↑ 1.0 4,990 1

Hash (cost=100.90..100.90 rows=4,990 width=4) (actual time=2.469..2.469 rows=4,990 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 240kB
22. 1.096 1.096 ↑ 1.0 4,990 1

Seq Scan on mv_stock_historico sthi (cost=0.00..100.90 rows=4,990 width=4) (actual time=0.019..1.096 rows=4,990 loops=1)

23. 0.089 0.162 ↑ 1.0 443 1

Hash (cost=9.43..9.43 rows=443 width=4) (actual time=0.162..0.162 rows=443 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
24. 0.073 0.073 ↑ 1.0 443 1

Seq Scan on clasificacion1 cla1 (cost=0.00..9.43 rows=443 width=4) (actual time=0.011..0.073 rows=443 loops=1)

25. 0.631 3.549 ↑ 1.0 4,218 1

Hash (cost=246.92..246.92 rows=4,218 width=4) (actual time=3.549..3.549 rows=4,218 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 213kB
26. 0.990 2.918 ↑ 1.0 4,218 1

Hash Left Join (cost=5.38..246.92 rows=4,218 width=4) (actual time=0.139..2.918 rows=4,218 loops=1)

  • Hash Cond: (p1.prod1_contacto = usu.usu_id)
27. 1.815 1.815 ↑ 1.0 4,218 1

Seq Scan on producto1 p1 (cost=0.00..190.49 rows=4,218 width=8) (actual time=0.020..1.815 rows=4,218 loops=1)

  • Filter: (prod1_contacto IS NOT NULL)
  • Rows Removed by Filter: 831
28. 0.011 0.113 ↓ 1.0 70 1

Hash (cost=4.53..4.53 rows=68 width=4) (actual time=0.113..0.113 rows=70 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
29. 0.052 0.102 ↓ 1.0 70 1

Hash Right Join (cost=2.53..4.53 rows=68 width=4) (actual time=0.068..0.102 rows=70 loops=1)

  • Hash Cond: ((emp.emp_abreviacion)::text = (usu.usu_alias)::text)
30. 0.014 0.014 ↓ 1.0 81 1

Seq Scan on empleado emp (cost=0.00..1.78 rows=78 width=10) (actual time=0.007..0.014 rows=81 loops=1)

31. 0.017 0.036 ↓ 1.0 70 1

Hash (cost=1.68..1.68 rows=68 width=9) (actual time=0.036..0.036 rows=70 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
32. 0.019 0.019 ↓ 1.0 70 1

Seq Scan on usuario usu (cost=0.00..1.68 rows=68 width=9) (actual time=0.009..0.019 rows=70 loops=1)

33. 0.073 0.223 ↑ 1.0 428 1

Hash (cost=16.70..16.70 rows=428 width=8) (actual time=0.223..0.223 rows=428 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
34. 0.150 0.150 ↑ 1.0 428 1

Seq Scan on jefe_linea jl (cost=0.00..16.70 rows=428 width=8) (actual time=0.013..0.150 rows=428 loops=1)

  • Filter: (jel_tipo = 1)
  • Rows Removed by Filter: 428
35. 0.078 0.221 ↑ 1.0 428 1

Hash (cost=16.70..16.70 rows=428 width=8) (actual time=0.221..0.221 rows=428 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
36. 0.143 0.143 ↑ 1.0 428 1

Seq Scan on jefe_linea jl_1 (cost=0.00..16.70 rows=428 width=8) (actual time=0.016..0.143 rows=428 loops=1)

  • Filter: (jel_tipo = 2)
  • Rows Removed by Filter: 428
37. 0.310 7.269 ↓ 14.4 359 1

Hash (cost=46.11..46.11 rows=25 width=609) (actual time=7.269..7.269 rows=359 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 49kB
38. 0.199 6.959 ↓ 14.4 359 1

Hash Right Join (cost=45.01..46.11 rows=25 width=609) (actual time=6.795..6.959 rows=359 loops=1)

  • Hash Cond: (reg.reg_id = prm.reg_id)
39. 0.015 0.015 ↑ 1.0 4 1

Seq Scan on region reg (cost=0.00..1.04 rows=4 width=520) (actual time=0.013..0.015 rows=4 loops=1)

40. 0.340 6.745 ↓ 14.4 359 1

Hash (cost=44.70..44.70 rows=25 width=97) (actual time=6.745..6.745 rows=359 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 49kB
41. 0.205 6.405 ↓ 14.4 359 1

Subquery Scan on prm (cost=44.39..44.70 rows=25 width=97) (actual time=6.140..6.405 rows=359 loops=1)

42. 0.671 6.200 ↓ 14.4 359 1

Sort (cost=44.39..44.45 rows=25 width=679) (actual time=6.136..6.200 rows=359 loops=1)

  • Sort Key: prm_1.ppr_id
  • Sort Method: quicksort Memory: 75kB
43. 0.359 5.529 ↓ 14.4 359 1

Nested Loop Left Join (cost=1.69..43.81 rows=25 width=679) (actual time=0.222..5.529 rows=359 loops=1)

44. 0.145 0.145 ↓ 6.2 25 1

Index Scan using idx_promocion_fechas on promocion prm_1 (cost=0.29..15.58 rows=4 width=20) (actual time=0.074..0.145 rows=25 loops=1)

  • Index Cond: ((('now'::cstring)::date >= ppr_fechainicio) AND (('now'::cstring)::date <= ppr_fechafinal) AND (ppr_activo = true))
  • Filter: ppr_activo
45. 0.448 5.025 ↓ 2.3 14 25

Nested Loop Left Join (cost=1.40..6.97 rows=6 width=65) (actual time=0.039..0.201 rows=14 loops=25)

  • Join Filter: (dpv.dpv_prod_mes = 1)
46. 0.405 3.500 ↓ 2.3 14 25

Nested Loop Left Join (cost=1.12..5.01 rows=6 width=57) (actual time=0.036..0.140 rows=14 loops=25)

47. 0.295 1.300 ↓ 2.3 14 25

Nested Loop Left Join (cost=0.84..3.09 rows=6 width=47) (actual time=0.029..0.052 rows=14 loops=25)

48. 0.100 0.575 ↓ 2.0 2 25

Nested Loop Left Join (cost=0.56..2.56 rows=1 width=28) (actual time=0.021..0.023 rows=2 loops=25)

  • Join Filter: (COALESCE(dpv.dpv_prod_mes, 0) = 0)
  • Rows Removed by Join Filter: 11
49. 0.175 0.175 ↑ 1.0 1 25

Index Scan using dpromocion_validacion_llave_cabecera on dpromocion_validacion dpv (cost=0.28..1.94 rows=1 width=16) (actual time=0.006..0.007 rows=1 loops=25)

  • Index Cond: (ppr_id = prm_1.ppr_id)
50. 0.300 0.300 ↓ 1.5 12 25

Index Scan using dpromocion_producto_llave_cabecera on dpromocion_producto dpp (cost=0.28..0.52 rows=8 width=16) (actual time=0.007..0.012 rows=12 loops=25)

  • Index Cond: (dpv_id = dpv.dpv_id)
51. 0.430 0.430 ↓ 1.3 8 43

Index Scan using dpromocion_cantidad_llave_cabecera on dpromocion_cantidad dpc (cost=0.28..0.47 rows=6 width=27) (actual time=0.005..0.010 rows=8 loops=43)

  • Index Cond: (dpv_id = dpv.dpv_id)
52. 1.795 1.795 ↑ 1.0 1 359

Index Scan using dpromocion_despacho_llave_cabecera on dpromocion_despacho dpd (cost=0.28..0.31 rows=1 width=18) (actual time=0.004..0.005 rows=1 loops=359)

  • Index Cond: (dpct_id = dpc.dpct_id)
53. 1.077 1.077 ↑ 1.0 1 359

Index Scan using pk_dpromocion_promocion on dpromocion_producto dpp2 (cost=0.28..0.31 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=359)

  • Index Cond: (dpp_id = dpc.dpp_id)
54.          

SubPlan (forHash Right Join)

55. 2.410 2.410 ↑ 1.0 1 482

Index Scan using producto_pkey on producto (cost=0.28..2.50 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=482)

  • Index Cond: (pro_id = prm.producto_gratis)
Planning time : 17.627 ms