explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zeD

Settings
# exclusive inclusive rows x rows loops node
1. 4.107 2,083.012 ↑ 1.4 14,629 1

Unique (cost=64,939.69..65,559.13 rows=20,648 width=245) (actual time=2,078.482..2,083.012 rows=14,629 loops=1)

2. 93.543 2,078.905 ↑ 1.4 14,629 1

Sort (cost=64,939.69..64,991.31 rows=20,648 width=245) (actual time=2,078.480..2,078.905 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: 3,516kB
3. 4.611 1,985.362 ↑ 1.4 14,629 1

Hash Left Join (cost=39,584.35..61,058.88 rows=20,648 width=245) (actual time=1,746.962..1,985.362 rows=14,629 loops=1)

  • Hash Cond: ((producto.codfamilia)::text = (familia.codigo)::text)
4. 141.437 1,980.597 ↑ 1.4 14,629 1

Merge Join (cost=39,563.25..60,753.87 rows=20,648 width=226) (actual time=1,746.779..1,980.597 rows=14,629 loops=1)

  • Merge Cond: ((producto_1.codigo)::text = (producto.codigo)::text)
5. 52.191 88.473 ↑ 1.0 310,087 1

Unique (cost=0.42..16,952.50 rows=310,176 width=10) (actual time=0.021..88.473 rows=310,087 loops=1)

6. 36.282 36.282 ↑ 1.0 310,087 1

Index Only Scan using producto_pkey on producto producto_1 (cost=0.42..16,177.06 rows=310,176 width=10) (actual time=0.012..36.282 rows=310,087 loops=1)

  • Heap Fetches: 117
7. 3.318 1,750.687 ↑ 1.4 14,629 1

Materialize (cost=39,562.83..39,666.07 rows=20,648 width=226) (actual time=1,746.735..1,750.687 rows=14,629 loops=1)

8. 75.102 1,747.369 ↑ 1.4 14,629 1

Sort (cost=39,562.83..39,614.45 rows=20,648 width=226) (actual time=1,746.725..1,747.369 rows=14,629 loops=1)

  • Sort Key: producto.codigo
  • Sort Method: quicksort Memory: 2,969kB
9. 25.068 1,672.267 ↑ 1.4 14,629 1

Hash Right Join (cost=34,125.92..35,822.02 rows=20,648 width=226) (actual time=1,628.514..1,672.267 rows=14,629 loops=1)

  • Hash Cond: ((tstock.codproducto)::text = (producto.codigo)::text)
10. 5.700 67.712 ↓ 2.4 70,090 1

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

11. 36.971 62.012 ↓ 2.4 70,090 1

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

  • Group Key: stock.codproducto
12. 19.056 25.041 ↓ 2.5 73,944 1

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

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

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

14. 0.006 0.036 ↓ 1.5 3 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.014 0.030 ↓ 1.5 3 1

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

  • Hash Cond: ((ubicacion.codalmacen)::text = (almacen.codigo)::text)
16. 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)

17. 0.007 0.013 ↓ 1.5 3 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
18. 0.006 0.006 ↓ 1.5 3 1

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

  • Filter: (NOT noinventariar)
  • Rows Removed by Filter: 2
19. 7.741 1,579.487 ↑ 1.4 14,629 1

Hash (cost=30,882.37..30,882.37 rows=20,648 width=218) (actual time=1,579.487..1,579.487 rows=14,629 loops=1)

  • Buckets: 16,384 Batches: 2 Memory Usage: 1,038kB
20. 20.170 1,571.746 ↑ 1.4 14,629 1

Hash Right Join (cost=29,587.81..30,882.37 rows=20,648 width=218) (actual time=1,552.142..1,571.746 rows=14,629 loops=1)

  • Hash Cond: ((productoempresa.codproducto)::text = (producto.codigo)::text)
  • Filter: CASE WHEN (productoempresa.cod_empresa IS NULL) THEN true WHEN ((productoempresa.cod_empresa)::text = 'Fersay'::text) THEN true ELSE false END
21. 0.004 0.004 ↑ 880.0 1 1

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

22. 14.782 1,551.572 ↑ 2.8 14,629 1

Hash (cost=27,820.61..27,820.61 rows=41,296 width=218) (actual time=1,551.572..1,551.572 rows=14,629 loops=1)

  • Buckets: 16,384 Batches: 4 Memory Usage: 577kB
23. 1,284.927 1,536.790 ↑ 2.8 14,629 1

Hash Left Join (cost=9.53..27,820.61 rows=41,296 width=218) (actual time=0.098..1,536.790 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 (...)
  • Rows Removed by Filter: 295,557
24. 251.859 251.859 ↓ 1.0 310,186 1

Seq Scan on producto (cost=0.00..26,647.76 rows=310,176 width=154) (actual time=0.005..251.859 rows=310,186 loops=1)

  • Filter: (NOT baja)
25. 0.000 0.004 ↓ 0.0 0 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
26. 0.001 0.004 ↓ 0.0 0 1

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

  • Recheck Cond: ((codcliente)::text = '0010'::text)
27. 0.003 0.003 ↓ 0.0 0 1

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

  • Index Cond: ((codcliente)::text = '0010'::text)
28. 0.091 0.154 ↑ 1.0 360 1

Hash (cost=16.60..16.60 rows=360 width=23) (actual time=0.154..0.154 rows=360 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 28kB
29. 0.063 0.063 ↑ 1.0 360 1

Seq Scan on familia (cost=0.00..16.60 rows=360 width=23) (actual time=0.005..0.063 rows=360 loops=1)

Planning time : 18.680 ms
Execution time : 2,085.681 ms