explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HTUS

Settings
# exclusive inclusive rows x rows loops node
1. 11.464 1,896.737 ↓ 1,125.3 14,629 1

Unique (cost=59,193.06..59,193.45 rows=13 width=245) (actual time=1,884.632..1,896.737 rows=14,629 loops=1)

2. 104.632 1,885.273 ↓ 1,125.3 14,629 1

Sort (cost=59,193.06..59,193.10 rows=13 width=245) (actual time=1,884.631..1,885.273 rows=14,629 loops=1)

  • Sort Key: producto.descripcion, producto.codigo, referenciacliente.refcli, referenciacliente.refdescripcion, producto.codfamilia, familia.descripcion, producto.codtipoproducto, (CASE WHEN (tstock.tunidades IS NULL) THEN '0'::double precision ELSE t (...)
  • Sort Method: quicksort Memory: 3528kB
3. 13.520 1,780.641 ↓ 1,125.3 14,629 1

Nested Loop Left Join (cost=57,467.62..59,192.82 rows=13 width=245) (actual time=1,457.633..1,780.641 rows=14,629 loops=1)

4. 24.790 1,708.605 ↓ 1,125.3 14,629 1

Hash Left Join (cost=57,467.35..59,188.92 rows=13 width=226) (actual time=1,457.615..1,708.605 rows=14,629 loops=1)

  • Hash Cond: ((producto.codigo)::text = (tstock.codproducto)::text)
5. 10.036 1,565.574 ↓ 1,125.3 14,629 1

Hash Left Join (cost=54,146.72..55,820.17 rows=13 width=218) (actual time=1,338.681..1,565.574 rows=14,629 loops=1)

  • Hash Cond: ((producto.codigo)::text = (productoempresa.codproducto)::text)
  • Filter: CASE WHEN (productoempresa.cod_empresa IS NULL) THEN true WHEN ((productoempresa.cod_empresa)::text = 'Fersay'::text) THEN true ELSE false END
6. 43.440 1,555.521 ↓ 541.8 14,629 1

Hash Left Join (cost=54,116.92..55,787.32 rows=27 width=218) (actual time=1,338.656..1,555.521 rows=14,629 loops=1)

  • Hash Cond: ((producto.codigo)::text = (referenciacliente.codproducto)::text)
  • Filter: ((replace(replace(replace(replace(replace(upper((producto.codigo)::text), 'Á'::text, 'A'::text), 'É'::text, 'E'::text), 'Í'::text, 'I'::text), 'Ó'::text, 'O'::text), 'Ú'::text, 'U'::text) ~~ '%001%'::text) OR (replac (...)
7. 7.985 1,512.079 ↓ 73.1 14,629 1

Nested Loop (cost=54,107.39..55,776.96 rows=200 width=154) (actual time=1,338.630..1,512.079 rows=14,629 loops=1)

8. 12.010 1,343.175 ↓ 73.1 14,629 1

HashAggregate (cost=54,106.96..54,108.96 rows=200 width=10) (actual time=1,338.600..1,343.175 rows=14,629 loops=1)

  • Group Key: producto_1.codigo
9. 1.632 1,331.165 ↓ 1.7 15,667 1

Append (cost=62.26..54,083.62 rows=9,339 width=10) (actual time=0.065..1,331.165 rows=15,667 loops=1)

10. 8.186 431.505 ↓ 3.4 10,779 1

Unique (cost=62.26..21,695.10 rows=3,133 width=10) (actual time=0.064..431.505 rows=10,779 loops=1)

11. 14.116 423.319 ↓ 3.4 10,779 1

Merge Left Join (cost=62.26..21,687.27 rows=3,133 width=10) (actual time=0.061..423.319 rows=10,779 loops=1)

  • Merge Cond: ((producto_1.codigo)::text = (productoempresa_1.codproducto)::text)
  • Filter: CASE WHEN (productoempresa_1.cod_empresa IS NULL) THEN true WHEN ((productoempresa_1.cod_empresa)::text = 'Fersay'::text) THEN true ELSE false END
12. 409.192 409.192 ↓ 1.7 10,779 1

Index Only Scan using producto_pkey on producto producto_1 (cost=0.42..21,605.14 rows=6,266 width=10) (actual time=0.036..409.192 rows=10,779 loops=1)

  • Filter: (replace(replace(replace(replace(replace(upper((codigo)::text), 'Á'::text, 'A'::text), 'É'::text, 'E'::text), 'Í'::text, 'I'::text), 'Ó'::text, 'O'::text), 'Ú'::text, 'U'::text) ~~ (...)
  • Rows Removed by Filter: 299407
  • Heap Fetches: 117
13. 0.006 0.011 ↑ 880.0 1 1

Sort (cost=61.84..64.04 rows=880 width=64) (actual time=0.011..0.011 rows=1 loops=1)

  • Sort Key: productoempresa_1.codproducto
  • Sort Method: quicksort Memory: 25kB
14. 0.005 0.005 ↑ 880.0 1 1

Seq Scan on productoempresa productoempresa_1 (cost=0.00..18.80 rows=880 width=64) (actual time=0.004..0.005 rows=1 loops=1)

15. 0.820 898.016 ↑ 1.3 4,888 1

Hash Right Join (cost=32,230.93..32,253.47 rows=6,204 width=10) (actual time=897.280..898.016 rows=4,888 loops=1)

  • Hash Cond: ((productoempresa_2.codproducto)::text = (producto_2.codigo)::text)
  • Filter: CASE WHEN (productoempresa_2.cod_empresa IS NULL) THEN true WHEN ((productoempresa_2.cod_empresa)::text = 'Fersay'::text) THEN true ELSE false END
16. 0.002 0.002 ↑ 880.0 1 1

Seq Scan on productoempresa productoempresa_2 (cost=0.00..18.80 rows=880 width=64) (actual time=0.002..0.002 rows=1 loops=1)

17. 4.237 897.194 ↑ 2.5 4,888 1

Hash (cost=32,075.84..32,075.84 rows=12,407 width=10) (actual time=897.194..897.194 rows=4,888 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 332kB
18. 892.957 892.957 ↑ 2.5 4,888 1

Seq Scan on producto producto_2 (cost=0.00..32,075.84 rows=12,407 width=10) (actual time=65.421..892.957 rows=4,888 loops=1)

  • Filter: (replace(replace(replace(replace(replace(upper((descripcion)::text), 'Á'::text, 'A'::text), 'É'::text, 'E'::text), 'Í'::text, 'I'::text), 'Ó'::text, 'O'::text), 'Ú'::text, 'U'::tex (...)
  • Rows Removed by Filter: 305298
19. 0.000 0.010 ↓ 0.0 0 1

Nested Loop Left Join (cost=4.74..20.83 rows=1 width=10) (actual time=0.010..0.010 rows=0 loops=1)

  • Filter: CASE WHEN (productoempresa_3.cod_empresa IS NULL) THEN true WHEN ((productoempresa_3.cod_empresa)::text = 'Fersay'::text) THEN true ELSE false END
20. 0.001 0.010 ↓ 0.0 0 1

Nested Loop (cost=4.59..13.99 rows=1 width=10) (actual time=0.010..0.010 rows=0 loops=1)

21. 0.002 0.009 ↓ 0.0 0 1

Bitmap Heap Scan on referenciacliente referenciacliente_1 (cost=4.16..9.54 rows=1 width=32) (actual time=0.009..0.009 rows=0 loops=1)

  • Recheck Cond: ((codcliente)::text = '0012'::text)
  • Filter: (replace(replace(replace(replace(replace(upper((refcli)::text), 'Á'::text, 'A'::text), 'É'::text, 'E'::text), 'Í'::text, 'I'::text), 'Ó'::text, 'O'::text), 'Ú'::text, 'U'::text) ~~ (...)
22. 0.007 0.007 ↓ 0.0 0 1

Bitmap Index Scan on referenciacliente_pkey (cost=0.00..4.16 rows=2 width=0) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: ((codcliente)::text = '0012'::text)
23. 0.000 0.000 ↓ 0.0 0

Index Only Scan using producto_pkey on producto producto_3 (cost=0.42..4.44 rows=1 width=10) (never executed)

  • Index Cond: (codigo = (referenciacliente_1.codproducto)::text)
  • Heap Fetches: 0
24. 0.000 0.000 ↓ 0.0 0

Index Only Scan using productoempresa_pkey on productoempresa productoempresa_3 (cost=0.15..6.79 rows=4 width=64) (never executed)

  • Index Cond: (codproducto = (producto_3.codigo)::text)
  • Heap Fetches: 0
25. 0.000 0.002 ↓ 0.0 0 1

Nested Loop Left Join (cost=4.74..20.83 rows=1 width=10) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: CASE WHEN (productoempresa_4.cod_empresa IS NULL) THEN true WHEN ((productoempresa_4.cod_empresa)::text = 'Fersay'::text) THEN true ELSE false END
26. 0.001 0.002 ↓ 0.0 0 1

Nested Loop (cost=4.59..13.99 rows=1 width=10) (actual time=0.002..0.002 rows=0 loops=1)

27. 0.000 0.001 ↓ 0.0 0 1

Bitmap Heap Scan on referenciacliente referenciacliente_2 (cost=4.16..9.54 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=1)

  • Recheck Cond: ((codcliente)::text = '0012'::text)
  • Filter: (replace(replace(replace(replace(replace(upper((refdescripcion)::text), 'Á'::text, 'A'::text), 'É'::text, 'E'::text), 'Í'::text, 'I'::text), 'Ó'::text, 'O'::text), 'Ú'::text, 'U':: (...)
28. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on referenciacliente_pkey (cost=0.00..4.16 rows=2 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: ((codcliente)::text = '0012'::text)
29. 0.000 0.000 ↓ 0.0 0

Index Only Scan using producto_pkey on producto producto_4 (cost=0.42..4.44 rows=1 width=10) (never executed)

  • Index Cond: (codigo = (referenciacliente_2.codproducto)::text)
  • Heap Fetches: 0
30. 0.000 0.000 ↓ 0.0 0

Index Only Scan using productoempresa_pkey on productoempresa productoempresa_4 (cost=0.15..6.79 rows=4 width=64) (never executed)

  • Index Cond: (codproducto = (producto_4.codigo)::text)
  • Heap Fetches: 0
31. 160.919 160.919 ↑ 1.0 1 14,629

Index Scan using producto_pkey on producto (cost=0.42..8.32 rows=1 width=154) (actual time=0.011..0.011 rows=1 loops=14,629)

  • Index Cond: ((codigo)::text = (producto_1.codigo)::text)
  • Filter: (NOT baja)
32. 0.000 0.002 ↓ 0.0 0 1

Hash (cost=9.50..9.50 rows=2 width=96) (actual time=0.002..0.002 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
33. 0.001 0.002 ↓ 0.0 0 1

Bitmap Heap Scan on referenciacliente (cost=4.16..9.50 rows=2 width=96) (actual time=0.002..0.002 rows=0 loops=1)

  • Recheck Cond: ((codcliente)::text = '0012'::text)
34. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on referenciacliente_pkey (cost=0.00..4.16 rows=2 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: ((codcliente)::text = '0012'::text)
35. 0.015 0.017 ↑ 880.0 1 1

Hash (cost=18.80..18.80 rows=880 width=64) (actual time=0.017..0.017 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.002 0.002 ↑ 880.0 1 1

Seq Scan on productoempresa (cost=0.00..18.80 rows=880 width=64) (actual time=0.002..0.002 rows=1 loops=1)

37. 27.218 118.241 ↓ 2.4 70,090 1

Hash (cost=2,950.95..2,950.95 rows=29,575 width=18) (actual time=118.241..118.241 rows=70,090 loops=1)

  • Buckets: 131072 (originally 32768) Batches: 2 (originally 1) Memory Usage: 3214kB
38. 7.795 91.023 ↓ 2.4 70,090 1

Subquery Scan on tstock (cost=2,359.45..2,950.95 rows=29,575 width=18) (actual time=63.006..91.023 rows=70,090 loops=1)

39. 52.736 83.228 ↓ 2.4 70,090 1

HashAggregate (cost=2,359.45..2,655.20 rows=29,575 width=18) (actual time=63.004..83.228 rows=70,090 loops=1)

  • Group Key: stock.codproducto
40. 22.383 30.492 ↓ 2.5 73,944 1

Hash Join (cost=2.19..2,211.57 rows=29,575 width=18) (actual time=0.040..30.492 rows=73,944 loops=1)

  • Hash Cond: (stock.codubicacion = ubicacion.codigo)
41. 8.082 8.082 ↓ 1.0 73,948 1

Seq Scan on stock (cost=0.00..1,636.37 rows=73,937 width=26) (actual time=0.004..8.082 rows=73,948 loops=1)

42. 0.007 0.027 ↓ 1.5 3 1

Hash (cost=2.16..2.16 rows=2 width=8) (actual time=0.027..0.027 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 0.007 0.020 ↓ 1.5 3 1

Hash Join (cost=1.08..2.16 rows=2 width=8) (actual time=0.018..0.020 rows=3 loops=1)

  • Hash Cond: ((ubicacion.codalmacen)::text = (almacen.codigo)::text)
44. 0.003 0.003 ↑ 1.0 5 1

Seq Scan on ubicacion (cost=0.00..1.05 rows=5 width=40) (actual time=0.003..0.003 rows=5 loops=1)

45. 0.006 0.010 ↓ 1.5 3 1

Hash (cost=1.05..1.05 rows=2 width=32) (actual time=0.010..0.010 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
46. 0.004 0.004 ↓ 1.5 3 1

Seq Scan on almacen (cost=0.00..1.05 rows=2 width=32) (actual time=0.003..0.004 rows=3 loops=1)

  • Filter: (NOT noinventariar)
  • Rows Removed by Filter: 2
47. 58.516 58.516 ↑ 1.0 1 14,629

Index Scan using familia_pkey on familia (cost=0.27..0.29 rows=1 width=23) (actual time=0.004..0.004 rows=1 loops=14,629)

  • Index Cond: ((codigo)::text = (producto.codfamilia)::text)
Planning time : 6.866 ms
Execution time : 1,899.701 ms