Consulta complejas

Estimados colegas

Tengo una consulta compleja para sacar la existencia por depósitos, tengo
una tabla que se llama existencia en la cual cargo los productos en cada
deposito eso implica que un productos puede estar en varios depósitos, y a
través de esta consulta obtengo el stock real, pero la consulta tarda mucho
porque en ventas tengo como 8 millones de registro, básicamente tengo 4
columna que me interesa, stock inicial, cant comprada, cant salida, cant
entrada y cant vendida y el

saldo = Stock inicial + cant comprada + cant entrada – cant salida – cant
vendida

Tambien tengo un campo “saldo” en la tabla existencia que actualizo por
trigger pero no confio tanto, me gusta más recalcular todo a la hora de
sacar la existencia.

V_productos es una vista porque ay es donde tengo realmente mi producto
unitario y presentación porque un producto unitario puede tener varias
presentaciones y cada presentación se maneja como uno independiente. El
campo estado y disp_stock se usa por unitario y por presentación

Mi Consulta ¿ay alguna forma de optimizar la consulta?

Agradezco la atención y a continuación pongo mi sql

SELECT e.idsucursales, e.iddepositos, e.codigo, e.codigopre,

p.descripciones, p.barra, p.codigo_ref, p.presentacion,

e.fh_inventario, e.cantinventario,

SUM(cd.cantidad) as cant_comprada,

SUM(CASE WHEN ptrd.tipo=2 OR ptrd.tipo=4 OR ptrd.tipo=6 THEN
ptrd.cantidad ELSE 0 END) as cant_entrada,

SUM(CASE WHEN ptrd.tipo=1 OR ptrd.tipo=3 OR ptrd.tipo=5 THEN
ptrd.cantidad ELSE 0 END) as cant_salida,

SUM(fd.cantidad) AS cant_vendida

FROM existencias e

INNER JOIN v_productos p ON e.codigo=p.codigo
AND TRIM(e.codigopre)=TRIM(p.codigopre)

LEFT JOIN (compras cd INNER JOIN compra c ON
cd.idcompras=c.idcompras)

ON e.codigo=cd.codigo AND
TRIM(e.codigopre)=TRIM(cd.codigopre) AND

e.idsucursales=c.idsucursales
AND e.iddepositos=c.iddepositos AND c.fecha_recepcion >=
e.fh_inventario::date

LEFT JOIN (prod_transfd ptrd INNER JOIN
prod_transf ptr ON ptrd.idtransferencia=ptr.idtransferencia)

ON e.codigo=ptrd.codigo AND
TRIM(e.codigopre)=TRIM(ptrd.codigopre)

AND
e.idsucursales=ptrd.idsucursales AND e.iddepositos=ptrd.iddepositos

LEFT JOIN (facturas fd INNER JOIN factura f
ON fd.sucursal=f.sucursal AND fd.dpto=f.dpto AND fd.factura=f.factura)

ON e.codigo=fd.codigo AND
TRIM(e.codigopre)=TRIM(fd.codigopre)

AND
e.idsucursales=fd.idsucursales AND e.iddepositos=fd.iddepositos AND
f.fecha_emision >= e.fh_inventario::date

WHERE p.estado=1 AND p.disp_stock=1 AND e.idsucursales=1 AND
e.iddepositos=2

GROUP BY e.idsucursales, e.iddepositos, e.codigo,
e.codigopre, e.fh_inventario, e.cantinventario,

p.descripciones, p.barra, p.codigo_ref,
p.presentacion

ORDER BY e.idsucursales, e.iddepositos, e.codigo,
e.codigopre

El explain me retorna esto pero me es muy complicado analizar.

"GroupAggregate (cost=1505247.70..1505249.58 rows=26 width=394)"

" -> Sort (cost=1505247.70..1505247.76 rows=26 width=394)"

" 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))"

" -> Hash Left Join (cost=1468176.71..1505247.09 rows=26
width=394)"

" 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)"

" -> Merge Left Join (cost=1026.27..1026.42 rows=3
width=387)"

" Merge Cond: (((e.codigo)::text = (cd.codigo)::text) AND
((btrim((e.codigopre)::text)) = (btrim((cd.codigopre)::text))))"

" Join Filter: ((e.idsucursales = c.idsucursales) AND
(e.iddepositos = c.iddepositos) AND (c.fecha_recepcion >=
(e.fh_inventario)::date))"

" -> Sort (cost=919.55..919.56 rows=3 width=380)"

" Sort Key: e.codigo, (btrim((e.codigopre)::text))"

" -> Hash Left Join (cost=871.35..919.53 rows=3
width=380)"

" 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 Join (cost=705.52..751.12 rows=3
width=366)"

" 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)"

" Sort Key: pb.iddivisiones,
p.codigobase, ((btrim((p.codigo)::text))::character(20))"

" -> HashAggregate
(cost=568.70..574.58 rows=588 width=1449)"

" -> Append
(cost=23.63..461.39 rows=588 width=1449)"

" -> Hash Join
(cost=23.63..192.26 rows=326 width=1365)"

" Hash Cond:
(p.iddepositos = d.iddepositos)"

" -> Hash Join
(cost=22.34..184.86 rows=326 width=1337)"

" Hash
Cond: (p.idmarcas = m.idmarcas)"

" ->
Hash Join (cost=21.16..179.20 rows=326 width=1229)"

"
Hash Cond: (p.idlineasp = l.idlineasp)"

"
-> Hash Join (cost=15.74..169.29 rows=326 width=1183)"

"
Hash Cond: (pb.iddivisiones = dv.iddivisiones)"

"
-> Hash Join (cost=14.67..163.74 rows=326 width=1137)"

"
Hash Cond: (p.idfamiliasp = fm.idfamiliasp)"

"
-> Hash Join (cost=10.47..155.05 rows=329 width=1091)"

"
Hash Cond: ((p.codigobase)::bpchar = pb.codigobase)"

"
-> Hash Join (cost=2.54..142.58 rows=334 width=1041)"

"
Hash Cond: (p.idrubros = r.idrubros)"

"
-> Hash Join (cost=1.14..136.52 rows=352 width=933)"

"
Hash Cond: (p.idsecciones = s.idsecciones)"

"
-> Seq Scan on productos p (cost=0.00..129.66 rows=587 width=825)"

"
Filter: ((estado = 1) AND (disp_stock = 1))"

"
-> Hash (cost=1.06..1.06 rows=6 width=112)"

"
-> Seq Scan on secciones s (cost=0.00..1.06 rows=6 width=112)"

"
-> Hash (cost=1.18..1.18 rows=18 width=112)"

"
-> Seq Scan on rubros r (cost=0.00..1.18 rows=18 width=112)"

"
-> Hash (cost=5.19..5.19 rows=219 width=71)"

"
-> Seq Scan on productosb pb (cost=0.00..5.19 rows=219 width=71)"

"
-> Hash (cost=2.98..2.98 rows=98 width=50)"

"
-> Seq Scan on familiasp fm (cost=0.00..2.98 rows=98 width=50)"

"
-> Hash (cost=1.03..1.03 rows=3 width=50)"

"
-> Seq Scan on divisiones dv (cost=0.00..1.03 rows=3 width=50)"

"
-> Hash (cost=3.52..3.52 rows=152 width=50)"

"
-> Seq Scan on lineasp l (cost=0.00..3.52 rows=152 width=50)"

" ->
Hash (cost=1.08..1.08 rows=8 width=112)"

"
-> Seq Scan on marcas m (cost=0.00..1.08 rows=8 width=112)"

" -> Hash
(cost=1.13..1.13 rows=13 width=32)"

" -> Seq
Scan on depositos d (cost=0.00..1.13 rows=13 width=32)"

" -> Hash Join
(cost=168.10..263.25 rows=262 width=1554)"

" Hash Cond:
(pb.iddivisiones = dv.iddivisiones)"

" -> Hash Join
(cost=167.03..253.34 rows=262 width=1508)"

" Hash
Cond: (pp.iddepositos = d.iddepositos)"

" ->
Hash Join (cost=165.74..248.44 rows=262 width=1480)"

"
Hash Cond: (p.idlineasp = l.idlineasp)"

"
-> Hash Join (cost=160.32..239.42 rows=262 width=1434)"

"
Hash Cond: (p.idmarcas = m.idmarcas)"

"
-> Hash Join (cost=159.14..234.63 rows=262 width=1326)"

"
Hash Cond: (pp.idformaspre = f.idformaspre)"

"
-> Hash Join (cost=157.96..229.85 rows=262 width=1248)"

"
Hash Cond: (p.idfamiliasp = fm.idfamiliasp)"

"
-> Hash Join (cost=153.75..222.03 rows=265 width=1202)"

"
Hash Cond: ((p.codigobase)::bpchar = pb.codigobase)"

"
-> Hash Join (cost=145.82..210.46 rows=268 width=1152)"

"
Hash Cond: (p.idrubros = r.idrubros)"

"
-> Hash Join (cost=144.42..205.32 rows=283 width=1044)"

"
Hash Cond: ((pp.codigo)::text = (p.codigo)::text)"

"
-> Seq Scan on productosp pp (cost=0.00..56.30 rows=471 width=469)"

"
Filter: ((estado = 1) AND (disp_stock = 1))"

"
-> Hash (cost=136.08..136.08 rows=667 width=575)"

"
-> Hash Join (cost=1.14..136.08 rows=667 width=575)"

"
Hash Cond: (p.idsecciones = s.idsecciones)"

"
-> Seq Scan on productos p (cost=0.00..124.11 rows=1111 width=467)"

"
-> Hash (cost=1.06..1.06 rows=6 width=112)"

"
-> Seq Scan on secciones s (cost=0.00..1.06 rows=6 width=112)"

"
-> Hash (cost=1.18..1.18 rows=18 width=112)"

"
-> Seq Scan on rubros r (cost=0.00..1.18 rows=18 width=112)"

"
-> Hash (cost=5.19..5.19 rows=219 width=71)"

"
-> Seq Scan on productosb pb (cost=0.00..5.19 rows=219 width=71)"

"
-> Hash (cost=2.98..2.98 rows=98 width=50)"

"
-> Seq Scan on familiasp fm (cost=0.00..2.98 rows=98 width=50)"

"
-> Hash (cost=1.08..1.08 rows=8 width=82)"

"
-> Seq Scan on formaspre f (cost=0.00..1.08 rows=8 width=82)"

"
-> Hash (cost=1.08..1.08 rows=8 width=112)"

"
-> Seq Scan on marcas m (cost=0.00..1.08 rows=8 width=112)"

"
-> Hash (cost=3.52..3.52 rows=152 width=50)"

"
-> Seq Scan on lineasp l (cost=0.00..3.52 rows=152 width=50)"

" ->
Hash (cost=1.13..1.13 rows=13 width=32)"

"
-> Seq Scan on depositos d (cost=0.00..1.13 rows=13 width=32)"

" -> Hash
(cost=1.03..1.03 rows=3 width=50)"

" -> Seq
Scan on divisiones dv (cost=0.00..1.03 rows=3 width=50)"

" -> Hash (cost=96.57..96.57 rows=488
width=46)"

" -> Bitmap Heap Scan on
existencias e (cost=29.25..96.57 rows=488 width=46)"

" Recheck Cond:
((idsucursales = 1) AND (iddepositos = 2))"

" -> Bitmap Index Scan on
pk_existencias (cost=0.00..29.13 rows=488 width=0)"

" Index Cond:
((idsucursales = 1) AND (iddepositos = 2))"

" -> Hash (cost=139.98..139.98 rows=1292
width=49)"

" -> Hash Join (cost=11.67..139.98
rows=1292 width=49)"

" Hash Cond:
(ptrd.idtransferencia = ptr.idtransferencia)"

" -> Seq Scan on prod_transfd
ptrd (cost=0.00..110.55 rows=1292 width=53)"

" Filter: ((idsucursales =
1) AND (iddepositos = 2))"

" -> Hash (cost=8.52..8.52
rows=252 width=4)"

" -> Seq Scan on
prod_transf ptr (cost=0.00..8.52 rows=252 width=4)"

" -> Sort (cost=106.72..106.74 rows=8 width=46)"

" Sort Key: cd.codigo,
(btrim((cd.codigopre)::text))"

" -> Nested Loop (cost=0.00..106.60 rows=8
width=46)"

" -> Seq Scan on compra c (cost=0.00..66.31
rows=4 width=16)"

" Filter: ((idsucursales = 1) AND
(iddepositos = 2))"

" -> Index Scan using pk_compras on compras
cd (cost=0.00..10.05 rows=2 width=38)"

" Index Cond: (cd.idcompras =
c.idcompras)"

" -> Hash (cost=1370482.88..1370482.88 rows=3357778
width=46)"

" -> Merge Join (cost=1277606.94..1370482.88
rows=3357778 width=46)"

" Merge Cond: ((f.factura = fd.factura) AND
(f.sucursal = fd.sucursal) AND (f.dpto = fd.dpto))"

" -> Sort (cost=253794.74..257641.52 rows=1538709
width=16)"

" Sort Key: f.factura, f.sucursal, f.dpto"

" -> Seq Scan on factura f
(cost=0.00..69368.09 rows=1538709 width=16)"

" -> Materialize (cost=1023810.67..1042526.07
rows=3743079 width=54)"

" -> Sort (cost=1023810.67..1033168.37
rows=3743079 width=54)"

" Sort Key: fd.factura, fd.sucursal,
fd.dpto"

" -> Seq Scan on facturas fd
(cost=0.00..487206.16 rows=3743079 width=54)"

" Filter: ((idsucursales = 1) AND
(iddepositos = 2))"