Re: Consulta complejas

Disculpa ayer sin darme cuenta respondi directamente a un correo personal

El EXPLAIN ANALYSE me saca esto que es de mi servidor

"GroupAggregate (cost=1357401.68..1357402.84 rows=16 width=394) (actual
time=88399.505..89086.558 rows=123 loops=1)"
" -> Sort (cost=1357401.68..1357401.72 rows=16 width=394) (actual
time=88399.477..88565.019 rows=225748 loops=1)"
" Sort Key: e.idsucursales, e.iddepositos, e.codigo, e.codigopre,
e.fh_inventario, e.cantinventario, p.descripciones, p.barra, p.codigo_ref,
(''::character varying(30))"
" Sort Method: external merge Disk: 48880kB"
" -> Hash Left Join (cost=1329344.23..1357401.36 rows=16
width=394) (actual time=22555.475..80682.289 rows=225748 loops=1)"
" Hash Cond: ((e.idsucursales = fd.idsucursales) AND
(e.iddepositos = fd.iddepositos) AND ((e.codigo)::text = (fd.codigo)::text)
AND (btrim((e.codigopre)::text) = btrim((fd.codigopre)::text)))"
" Join Filter: (f.fecha_emision >= (e.fh_inventario)::date)"
" -> Hash Left Join (cost=1029.59..1071.21 rows=2 width=387)
(actual time=40.444..42.895 rows=657 loops=1)"
" Hash Cond: ((e.idsucursales = ptrd.idsucursales) AND
(e.iddepositos = ptrd.iddepositos) AND ((e.codigo)::text =
(ptrd.codigo)::text) AND (btrim((e.codigopre)::text) =
btrim((ptrd.codigopre)::text)))"
" -> Hash Left Join (cost=884.00..924.64 rows=2
width=373) (actual time=38.429..39.742 rows=123 loops=1)"
" Hash Cond: ((e.idsucursales = c.idsucursales)
AND (e.iddepositos = c.iddepositos) AND ((e.codigo)::text =
(cd.codigo)::text) AND (btrim((e.codigopre)::text) =
btrim((cd.codigopre)::text)))"
" Join Filter: (c.fecha_recepcion >=
(e.fh_inventario)::date)"
" -> Hash Join (cost=675.59..715.30 rows=2
width=366) (actual time=36.065..37.240 rows=123 loops=1)"
" Hash Cond: (((p.codigo)::text =
(e.codigo)::text) AND (btrim(((''::character varying(5)))::text) =
btrim((e.codigopre)::text)))"
" -> Sort (cost=601.63..603.10 rows=588
width=1449) (actual time=35.765..36.079 rows=1227 loops=1)"
" Sort Key: pb.iddivisiones,
p.codigobase, ((btrim((p.codigo)::text))::character(20))"
" Sort Method: quicksort Memory:
1698kB"
" -> HashAggregate
(cost=568.70..574.58 rows=588 width=1449) (actual time=24.708..26.233
rows=1227 loops=1)"
" -> Append
(cost=23.63..461.39 rows=588 width=1449) (actual time=0.445..16.636
rows=1227 loops=1)"
" -> Hash Join
(cost=23.63..192.26 rows=326 width=1365) (actual time=0.444..7.255
rows=765 loops=1)"
" Hash Cond:
(p.iddepositos = d.iddepositos)"
" -> Hash Join
(cost=22.34..184.86 rows=326 width=1337) (actual time=0.415..6.050
rows=765 loops=1)"
" Hash Cond:
(p.idmarcas = m.idmarcas)"
" -> Hash
Join (cost=21.16..179.20 rows=326 width=1229) (actual time=0.396..5.424
rows=765 loops=1)"
" Hash
Cond: (p.idlineasp = l.idlineasp)"
" ->
Hash Join (cost=15.74..169.29 rows=326 width=1183) (actual
time=0.298..4.704 rows=765 loops=1)"
"
Hash Cond: (pb.iddivisiones = dv.iddivisiones)"
"
-> Hash Join (cost=14.67..163.74 rows=326 width=1137) (actual
time=0.283..4.080 rows=765 loops=1)"
"
Hash Cond: (p.idfamiliasp = fm.idfamiliasp)"
"
-> Hash Join (cost=10.47..155.05 rows=329 width=1091) (actual
time=0.212..3.392 rows=765 loops=1)"
"
Hash Cond: ((p.codigobase)::bpchar = pb.codigobase)"
"
-> Hash Join (cost=2.54..142.58 rows=334 width=1041) (actual
time=0.053..2.507 rows=765 loops=1)"
"
Hash Cond: (p.idrubros = r.idrubros)"
"
-> Hash Join (cost=1.14..136.52 rows=352 width=933)
(actual time=0.029..1.898 rows=765 loops=1)"
"
Hash Cond: (p.idsecciones = s.idsecciones)"
"
-> Seq Scan on productos p (cost=0.00..129.66
rows=587 width=825) (actual time=0.008..1.073 rows=765 loops=1)"
"
Filter: ((estado = 1) AND (disp_stock = 1))"
"
-> Hash (cost=1.06..1.06 rows=6 width=112) (actual
time=0.010..0.010 rows=10 loops=1)"
"
Buckets: 1024 Batches: 1 Memory Usage: 1kB"
"
-> Seq Scan on secciones s (cost=0.00..1.06
rows=6 width=112) (actual time=0.003..0.005 rows=10 loops=1)"
"
-> Hash (cost=1.18..1.18 rows=18 width=112) (actual
time=0.014..0.014 rows=20 loops=1)"
"
Buckets: 1024 Batches: 1 Memory Usage: 2kB"
"
-> Seq Scan on rubros r (cost=0.00..1.18 rows=18
width=112) (actual time=0.002..0.007 rows=20 loops=1)"
"
-> Hash (cost=5.19..5.19 rows=219 width=71) (actual
time=0.149..0.149 rows=222 loops=1)"
"
Buckets: 1024 Batches: 1 Memory Usage: 23kB"
"
-> Seq Scan on productosb pb (cost=0.00..5.19 rows=219
width=71) (actual time=0.004..0.064 rows=222 loops=1)"
"
-> Hash (cost=2.98..2.98 rows=98 width=50) (actual time=0.061..0.061
rows=100 loops=1)"
"
Buckets: 1024 Batches: 1 Memory Usage: 9kB"
"
-> Seq Scan on familiasp fm (cost=0.00..2.98 rows=98 width=50)
(actual time=0.004..0.028 rows=100 loops=1)"
"
-> Hash (cost=1.03..1.03 rows=3 width=50) (actual time=0.005..0.005
rows=4 loops=1)"
"
Buckets: 1024 Batches: 1 Memory Usage: 1kB"
"
-> Seq Scan on divisiones dv (cost=0.00..1.03 rows=3 width=50)
(actual time=0.002..0.003 rows=4 loops=1)"
" ->
Hash (cost=3.52..3.52 rows=152 width=50) (actual time=0.088..0.088
rows=152 loops=1)"
"
Buckets: 1024 Batches: 1 Memory Usage: 13kB"
"
-> Seq Scan on lineasp l (cost=0.00..3.52 rows=152 width=50) (actual
time=0.004..0.040 rows=152 loops=1)"
" -> Hash
(cost=1.08..1.08 rows=8 width=112) (actual time=0.008..0.008 rows=8
loops=1)"
"
Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" ->
Seq Scan on marcas m (cost=0.00..1.08 rows=8 width=112) (actual
time=0.003..0.005 rows=8 loops=1)"
" -> Hash
(cost=1.13..1.13 rows=13 width=32) (actual time=0.016..0.016 rows=18
loops=1)"
" Buckets:
1024 Batches: 1 Memory Usage: 2kB"
" -> Seq Scan
on depositos d (cost=0.00..1.13 rows=13 width=32) (actual
time=0.005..0.009 rows=18 loops=1)"
" -> Hash Join
(cost=168.10..263.25 rows=262 width=1554) (actual time=3.819..8.991
rows=462 loops=1)"
" Hash Cond:
(pb.iddivisiones = dv.iddivisiones)"
" -> Hash Join
(cost=167.03..253.34 rows=262 width=1508) (actual time=3.785..7.655
rows=462 loops=1)"
" Hash Cond:
(pp.iddepositos = d.iddepositos)"
" -> Hash
Join (cost=165.74..248.44 rows=262 width=1480) (actual time=3.761..7.261
rows=462 loops=1)"
" Hash
Cond: (p.idlineasp = l.idlineasp)"
" ->
Hash Join (cost=160.32..239.42 rows=262 width=1434) (actual
time=3.662..6.781 rows=462 loops=1)"
"
Hash Cond: (p.idmarcas = m.idmarcas)"
"
-> Hash Join (cost=159.14..234.63 rows=262 width=1326) (actual
time=3.644..6.399 rows=462 loops=1)"
"
Hash Cond: (pp.idformaspre = f.idformaspre)"
"
-> Hash Join (cost=157.96..229.85 rows=262 width=1248) (actual
time=3.624..6.013 rows=462 loops=1)"
"
Hash Cond: (p.idfamiliasp = fm.idfamiliasp)"
"
-> Hash Join (cost=153.75..222.03 rows=265 width=1202) (actual
time=3.554..5.569 rows=462 loops=1)"
"
Hash Cond: ((p.codigobase)::bpchar = pb.codigobase)"
"
-> Hash Join (cost=145.82..210.46 rows=268 width=1152)
(actual time=3.233..4.780 rows=462 loops=1)"
"
Hash Cond: (p.idrubros = r.idrubros)"
"
-> Hash Join (cost=144.42..205.32 rows=283
width=1044) (actual time=3.204..4.358 rows=462 loops=1)"
"
Hash Cond: ((pp.codigo)::text =
(p.codigo)::text)"
"
-> Seq Scan on productosp pp
(cost=0.00..56.30 rows=471 width=469) (actual time=0.007..0.528 rows=462
loops=1)"
"
Filter: ((estado = 1) AND (disp_stock =
1))"
"
-> Hash (cost=136.08..136.08 rows=667
width=575) (actual time=3.183..3.183 rows=1112 loops=1)"
"
Buckets: 1024 Batches: 1 Memory Usage:
333kB"
"
-> Hash Join (cost=1.14..136.08
rows=667 width=575) (actual time=0.024..1.772 rows=1112 loops=1)"
"
Hash Cond: (p.idsecciones =
s.idsecciones)"
"
-> Seq Scan on productos p
(cost=0.00..124.11 rows=1111 width=467) (actual time=0.003..0.349
rows=1112 loops=1)"
"
-> Hash (cost=1.06..1.06 rows=6
width=112) (actual time=0.009..0.009 rows=10 loops=1)"
"
Buckets: 1024 Batches: 1
Memory Usage: 1kB"
"
-> Seq Scan on secciones s
(cost=0.00..1.06 rows=6 width=112) (actual time=0.002..0.004 rows=10
loops=1)"
"
-> Hash (cost=1.18..1.18 rows=18 width=112)
(actual time=0.016..0.016 rows=20 loops=1)"
"
Buckets: 1024 Batches: 1 Memory Usage: 2kB"
"
-> Seq Scan on rubros r (cost=0.00..1.18
rows=18 width=112) (actual time=0.003..0.008 rows=20 loops=1)"
"
-> Hash (cost=5.19..5.19 rows=219 width=71) (actual
time=0.309..0.309 rows=222 loops=1)"
"
Buckets: 1024 Batches: 1 Memory Usage: 23kB"
"
-> Seq Scan on productosb pb (cost=0.00..5.19
rows=219 width=71) (actual time=0.003..0.064 rows=222 loops=1)"
"
-> Hash (cost=2.98..2.98 rows=98 width=50) (actual
time=0.060..0.060 rows=100 loops=1)"
"
Buckets: 1024 Batches: 1 Memory Usage: 9kB"
"
-> Seq Scan on familiasp fm (cost=0.00..2.98 rows=98
width=50) (actual time=0.003..0.026 rows=100 loops=1)"
"
-> Hash (cost=1.08..1.08 rows=8 width=82) (actual time=0.009..0.009
rows=8 loops=1)"
"
Buckets: 1024 Batches: 1 Memory Usage: 1kB"
"
-> Seq Scan on formaspre f (cost=0.00..1.08 rows=8 width=82)
(actual time=0.003..0.005 rows=8 loops=1)"
"
-> Hash (cost=1.08..1.08 rows=8 width=112) (actual time=0.008..0.008
rows=8 loops=1)"
"
Buckets: 1024 Batches: 1 Memory Usage: 1kB"
"
-> Seq Scan on marcas m (cost=0.00..1.08 rows=8 width=112) (actual
time=0.002..0.004 rows=8 loops=1)"
" ->
Hash (cost=3.52..3.52 rows=152 width=50) (actual time=0.088..0.088
rows=152 loops=1)"
"
Buckets: 1024 Batches: 1 Memory Usage: 13kB"
"
-> Seq Scan on lineasp l (cost=0.00..3.52 rows=152 width=50) (actual
time=0.004..0.039 rows=152 loops=1)"
" -> Hash
(cost=1.13..1.13 rows=13 width=32) (actual time=0.014..0.014 rows=18
loops=1)"
"
Buckets: 1024 Batches: 1 Memory Usage: 2kB"
" ->
Seq Scan on depositos d (cost=0.00..1.13 rows=13 width=32) (actual
time=0.003..0.007 rows=18 loops=1)"
" -> Hash
(cost=1.03..1.03 rows=3 width=50) (actual time=0.006..0.006 rows=4
loops=1)"
" Buckets:
1024 Batches: 1 Memory Usage: 1kB"
" -> Seq Scan
on divisiones dv (cost=0.00..1.03 rows=3 width=50) (actual
time=0.002..0.003 rows=4 loops=1)"
" -> Hash (cost=71.84..71.84 rows=142
width=46) (actual time=0.224..0.224 rows=125 loops=1)"
" Buckets: 1024 Batches: 1 Memory
Usage: 11kB"
" -> Bitmap Heap Scan on existencias
e (cost=9.71..71.84 rows=142 width=46) (actual time=0.044..0.128 rows=125
loops=1)"
" Recheck Cond: ((idsucursales =
1) AND (iddepositos = 1))"
" -> Bitmap Index Scan on
pk_existencias (cost=0.00..9.67 rows=142 width=0) (actual
time=0.035..0.035 rows=126 loops=1)"
" Index Cond:
((idsucursales = 1) AND (iddepositos = 1))"
" -> Hash (cost=196.19..196.19 rows=611
width=46) (actual time=2.343..2.343 rows=437 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage:
35kB"
" -> Hash Join (cost=70.06..196.19
rows=611 width=46) (actual time=1.243..1.998 rows=437 loops=1)"
" Hash Cond: (cd.idcompras =
c.idcompras)"
" -> Seq Scan on compras cd
(cost=0.00..110.47 rows=2547 width=38) (actual time=0.009..0.625 rows=2564
loops=1)"
" -> Hash (cost=66.31..66.31
rows=300 width=16) (actual time=0.613..0.613 rows=435 loops=1)"
" Buckets: 1024 Batches: 1
Memory Usage: 21kB"
" -> Seq Scan on compra c
(cost=0.00..66.31 rows=300 width=16) (actual time=0.007..0.479 rows=435
loops=1)"
" Filter: ((idsucursales =
1) AND (iddepositos = 1))"
" -> Hash (cost=132.93..132.93 rows=633 width=49)
(actual time=2.000..2.000 rows=622 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 52kB"
" -> Hash Join (cost=13.62..132.93 rows=633
width=49) (actual time=0.173..1.510 rows=622 loops=1)"
" Hash Cond: (ptrd.idtransferencia =
ptr.idtransferencia)"
" -> Seq Scan on prod_transfd ptrd
(cost=0.00..110.61 rows=633 width=53) (actual time=0.023..1.016 rows=622
loops=1)"
" Filter: ((idsucursales = 1) AND
(iddepositos = 1))"
" -> Hash (cost=9.94..9.94 rows=294
width=4) (actual time=0.139..0.139 rows=244 loops=1)"
" Buckets: 1024 Batches: 1 Memory
Usage: 9kB"
" -> Seq Scan on prod_transf ptr
(cost=0.00..9.94 rows=294 width=4) (actual time=0.005..0.073 rows=244
loops=1)"
" -> Hash (cost=1249934.84..1249934.84 rows=2722540
width=46) (actual time=22512.774..22512.774 rows=3013145 loops=1)"
" Buckets: 65536 Batches: 256 (originally 8) Memory
Usage: 42648kB"
" -> Merge Join (cost=1171706.88..1249934.84
rows=2722540 width=46) (actual time=13165.345..19788.234 rows=3013145
loops=1)"
" Merge Cond: ((f.factura = fd.factura) AND
(f.sucursal = fd.sucursal) AND (f.dpto = fd.dpto))"
" -> Sort (cost=254001.36..257851.13
rows=1539906 width=16) (actual time=2432.206..3013.906 rows=1531487
loops=1)"
" Sort Key: f.factura, f.sucursal, f.dpto"
" Sort Method: external merge Disk:
38912kB"
" -> Seq Scan on factura f
(cost=0.00..69422.06 rows=1539906 width=16) (actual time=0.051..649.125
rows=1532097 loops=1)"
" -> Materialize (cost=917703.98..932878.72
rows=3034948 width=54) (actual time=10733.121..14303.299 rows=3013145
loops=1)"
" -> Sort (cost=917703.98..925291.35
rows=3034948 width=54) (actual time=10733.109..13163.308 rows=3013145
loops=1)"
" Sort Key: fd.factura, fd.sucursal,
fd.dpto"
" Sort Method: external merge Disk:
194264kB"
" -> Seq Scan on facturas fd
(cost=0.00..487206.16 rows=3034948 width=54) (actual time=0.292..5495.427
rows=3013145 loops=1)"
" Filter: ((idsucursales = 1)
AND (iddepositos = 1))"
"Total runtime: 89114.732 ms"