Re: Consutla SQL

[Rodriguez Fernando <rodriguez(at)ort(dot)edu(dot)uy>]

Daniel Ferrer wrote:
> -----Mensaje original-----
> De: Rodriguez Fernando [mailto:rodriguez(at)ort(dot)edu(dot)uy] 
> Enviado el: Viernes, 29 de Febrero de 2008 12:24 p.m.
> Para: daniel(dot)ferrer(at)ctd(dot)com(dot)ar
> CC: pgsql-es-ayuda(at)postgresql(dot)org
> Asunto: Re: [pgsql-es-ayuda] Consutla SQL
>
> Daniel Ferrer wrote:
>   
> >  
> > Estimada lista:
> >                         Les comento que necesito extraer datos
> >     
> estadísticos
>   
> > y el campo por el cual estoy filtrando es de tipo timestamp.
> >                         La tabla ya cuenta con mas de 500.000 registros
> >     
> por
>   
> > lo cual cada consulta que realizo me da como resultado un larga espera.
> >                         Estos reportes son de tipo comercial por lo cual
> > debo optimizar dicho reportes.
> >  
> >                         Alguna sugerencia ?
> >  
> >  
> > Adjunto la consulta y el explain:
> >  
> >  
> > SELECT 
> >  to_char(sucursal_destino.id_entidad,'0000') || ' - ' ||
> > sucursal_destino.razon_social AS "Sucursal_Destino",
> >  (Select
> > extraer_cliente(cp.cobro,cp.id_cliente_origen,cp.id_cliente_destino)) || '
> >     
> '
>   
> > || cliente.razon_social AS "NombreCliente",
> >  zona.nombre AS "Zona",
> >  te.nombre AS "Tipo",
> >  to_char(cp.fecha_despachada,'YYYY-MM') AS "Periodo", 
> >  
> >  (trim(to_char(cp.nro_sucursal,'0000') || '-' ||
> > trim(to_char(cp.nro_carta_porte,'00000000')))) AS "CartaPorte",
> >  
> >  (SELECT sum(cpp.unidad) FROM carta_porte_producto cpp, productos p WHERE 
> >  (cpp.nro_carta_porte = cp.nro_carta_porte AND cpp.nro_sucursal =
> > cp.nro_sucursal) AND
> >  cpp.id_producto = p.id_producto AND 
> >  p.nombre IN ('pallets','PALLETS','pallets ** SIN
> > CARGO','termos','TERMOS','pallets .')
> >  ) AS "pallets",
> >  
> >  (SELECT sum(cpp.unidad) FROM carta_porte_producto cpp, productos p WHERE 
> >  (cpp.nro_carta_porte = cp.nro_carta_porte AND cpp.nro_sucursal =
> > cp.nro_sucursal) AND
> >  cpp.id_producto = p.id_producto AND 
> >  p.nombre IN ('bultos ** SIN CARGO','bultos','BULTOS','bultos .')
> >  ) AS "bultos",
> >  
> >  (SELECT sum(cpp.unidad) FROM carta_porte_producto cpp, productos p WHERE 
> >  (cpp.nro_carta_porte = cp.nro_carta_porte AND cpp.nro_sucursal =
> > cp.nro_sucursal) AND
> >  cpp.id_producto = p.id_producto AND 
> >  p.nombre IN ('sobres ** SIN CARGO','sobres','SOBRES','sobres .')
> >  ) AS "sobres",
> >  
> >  (SELECT sum(cpp.unidad) FROM carta_porte_producto cpp, productos p WHERE 
> >  (cpp.nro_carta_porte = cp.nro_carta_porte AND cpp.nro_sucursal =
> > cp.nro_sucursal) AND
> >  cpp.id_producto = p.id_producto AND 
> >  p.nombre NOT IN ('pallets','PALLETS','pallets ** SIN
> > CARGO','termos','TERMOS','pallets .',
> >  'bultos ** SIN CARGO','bultos','BULTOS','bultos .','remito','sobres **
> >     
> SIN
>   
> > CARGO','sobres','SOBRES','sobres .')
> >  ) AS "otros",
> >  
> >  (SELECT sum(cpp.peso) FROM carta_porte_producto cpp WHERE
> > cpp.nro_carta_porte = cp.nro_carta_porte AND cpp.nro_sucursal =
> > cp.nro_sucursal) AS "kg",
> >  (SELECT sum(cpp.m3) FROM carta_porte_producto cpp WHERE
> >     
> cpp.nro_carta_porte
>   
> > = cp.nro_carta_porte AND cpp.nro_sucursal = cp.nro_sucursal) AS "m3",
> >  (SELECT sum(cpp.valor_declarado) FROM carta_porte_producto cpp WHERE
> > cpp.nro_carta_porte = cp.nro_carta_porte AND cpp.nro_sucursal =
> > cp.nro_sucursal) AS "vd",
> >  cp.contrarreembolso AS "crr"
> > FROM 
> >  carta_porte "cp",
> >  tipo_envio "te",
> >  detalle_estado_carta_porte "decp", 
> >  entidad "sucursal_destino",
> >  entidad "cliente",
> >  localidades "l",
> >  localidades "li",
> >  zona_sucursal "zona"
> >  
> > WHERE
> >  ---- Condicion ----
> >  cp.fecha_despachada BETWEEN '2007/01/01' AND '2008/02/01' AND
> >  (Select
> > extraer_cliente(cp.cobro,cp.id_cliente_origen,cp.id_cliente_destino)) =
> > '9000' AND
> >  cliente.id_entidad = '9000' AND
> >  ---- Join ----
> >  te.id_tipo_envio = cp.id_tipo_envio AND 
> >  cp.id_detalle_estado = decp.id_detalle_estado AND 
> >  (cp.localidad_destino = li.nombre_localidad AND cp.codigo_postal_destino
> >     
> =
>   
> > li.codigo_postal) AND
> >  li.id_localidad_padre = l.id_localidad AND
> >  l.nro_sucursal = sucursal_destino.id_entidad AND
> >  zona.id_zona_sucursal =  l.id_zona AND
> >  decp.id_estado_carta_porte NOT IN ('20','21','22','23','24','25','26');
> >  
> >  
> >  
> > "Nested Loop  (cost=338.35..35975.54 rows=3 width=444)"
> > "  ->  Index Scan using id_cliente on entidad cliente  (cost=0.00..8.27
> > rows=1 width=23)"
> > "        Index Cond: (id_entidad = 9000::bigint)"
> > "  ->  Hash Join  (cost=338.35..35773.05 rows=3 width=421)"
> > "        Hash Cond: ((cp.id_tipo_envio)::text = (te.id_tipo_envio)::text)"
> > "        ->  Nested Loop  (cost=337.15..35771.80 rows=3 width=308)"
> > "              ->  Nested Loop  (cost=337.15..35746.50 rows=3 width=316)"
> > "                    ->  Hash Join  (cost=337.15..35726.83 rows=3
> > width=293)"
> > "                          Hash Cond: (l.id_zona = zona.id_zona_sucursal)"
> > "                          ->  Nested Loop  (cost=335.22..35724.87 rows=3
> > width=83)"
> > "                                ->  Hash Join  (cost=335.22..35705.59
> > rows=3 width=75)"
> > "                                      Hash Cond:
> > (((cp.localidad_destino)::text = (li.nombre_localidad)::text) AND
> > (cp.codigo_postal_destino = li.codigo_postal))"
> > "                                      ->  Seq Scan on carta_porte cp
> > (cost=0.00..35286.27 rows=1345 width=84)"
> > "                                            Filter: ((fecha_despachada >=
> > '2007-01-01 00:00:00'::timestamp without time zone) AND (fecha_despachada
> >     
> <=
>   
> > '2008-02-01 00:00:00'::timestamp without time zone) AND ((subplan) =
> >     
> 9000))"
>   
> > "                                            SubPlan"
> > "                                              ->  Result
> >     
> (cost=0.00..0.01
>   
> > rows=1 width=0)"
> > "                                      ->  Hash  (cost=284.09..284.09
> > rows=3409 width=27)"
> > "                                            ->  Seq Scan on localidades
> >     
> li
>   
> > (cost=0.00..284.09 rows=3409 width=27)"
> > "                                ->  Index Scan using id_localidad on
> > localidades l  (cost=0.00..6.41 rows=1 width=24)"
> > "                                      Index Cond: (li.id_localidad_padre
> >     
> =
>   
> > l.id_localidad)"
> > "                          ->  Hash  (cost=1.41..1.41 rows=41 width=226)"
> > "                                ->  Seq Scan on zona_sucursal zona
> > (cost=0.00..1.41 rows=41 width=226)"
> > "                    ->  Index Scan using id_cliente on entidad
> > sucursal_destino  (cost=0.00..6.54 rows=1 width=31)"
> > "                          Index Cond: (l.nro_sucursal =
> > sucursal_destino.id_entidad)"
> > "              ->  Index Scan using id_detalle_estado on
> > detalle_estado_carta_porte decp  (cost=0.00..8.42 rows=1 width=8)"
> > "                    Index Cond: (cp.id_detalle_estado =
> > decp.id_detalle_estado)"
> > "                    Filter: (id_estado_carta_porte <> ALL
> > ('{20,21,22,23,24,25,26}'::bigint[]))"
> > "        ->  Hash  (cost=1.09..1.09 rows=9 width=126)"
> > "              ->  Seq Scan on tipo_envio te  (cost=0.00..1.09 rows=9
> > width=126)"
> > "  SubPlan"
> > "    ->  Aggregate  (cost=8.43..8.44 rows=1 width=8)"
> > "          ->  Index Scan using nro_sucursal_nro_carta_porte_idx on
> > carta_porte_producto cpp  (cost=0.00..8.42 rows=1 width=8)"
> > "                Index Cond: ((nro_sucursal = $4) AND (nro_carta_porte =
> > $3))"
> > "    ->  Aggregate  (cost=8.43..8.44 rows=1 width=8)"
> > "          ->  Index Scan using nro_sucursal_nro_carta_porte_idx on
> > carta_porte_producto cpp  (cost=0.00..8.42 rows=1 width=8)"
> > "                Index Cond: ((nro_sucursal = $4) AND (nro_carta_porte =
> > $3))"
> > "    ->  Aggregate  (cost=8.43..8.44 rows=1 width=8)"
> > "          ->  Index Scan using nro_sucursal_nro_carta_porte_idx on
> > carta_porte_producto cpp  (cost=0.00..8.42 rows=1 width=8)"
> > "                Index Cond: ((nro_sucursal = $4) AND (nro_carta_porte =
> > $3))"
> > "    ->  Aggregate  (cost=10.00..10.01 rows=1 width=4)"
> > "          ->  Nested Loop  (cost=0.00..10.00 rows=1 width=4)"
> > "                Join Filter: (cpp.id_producto = p.id_producto)"
> > "                ->  Index Scan using nro_sucursal_nro_carta_porte_idx on
> > carta_porte_producto cpp  (cost=0.00..8.42 rows=1 width=12)"
> > "                      Index Cond: ((nro_sucursal = $4) AND
> >     
> (nro_carta_porte
>   
> > = $3))"
> > "                ->  Seq Scan on productos p  (cost=0.00..1.50 rows=6
> > width=8)"
> > "                      Filter: ((nombre)::text <> ALL
> > (('{pallets,PALLETS,"pallets ** SIN CARGO",termos,TERMOS,"pallets
> >     
> .","bultos
>   
> > ** SIN CARGO",bultos,BULTOS,"bultos .",remito,"sobres ** SIN
> > CARGO",sobres,SOBRES,"sobres ."}'::character varying[])::text[]))"
> > "    ->  Aggregate  (cost=9.76..9.77 rows=1 width=4)"
> > "          ->  Nested Loop  (cost=0.00..9.75 rows=1 width=4)"
> > "                Join Filter: (cpp.id_producto = p.id_producto)"
> > "                ->  Index Scan using nro_sucursal_nro_carta_porte_idx on
> > carta_porte_producto cpp  (cost=0.00..8.42 rows=1 width=12)"
> > "                      Index Cond: ((nro_sucursal = $4) AND
> >     
> (nro_carta_porte
>   
> > = $3))"
> > "                ->  Seq Scan on productos p  (cost=0.00..1.28 rows=4
> > width=8)"
> > "                      Filter: ((nombre)::text = ANY (('{"sobres ** SIN
> > CARGO",sobres,SOBRES,"sobres ."}'::character varying[])::text[]))"
> > "    ->  Aggregate  (cost=9.76..9.77 rows=1 width=4)"
> > "          ->  Nested Loop  (cost=0.00..9.75 rows=1 width=4)"
> > "                Join Filter: (cpp.id_producto = p.id_producto)"
> > "                ->  Index Scan using nro_sucursal_nro_carta_porte_idx on
> > carta_porte_producto cpp  (cost=0.00..8.42 rows=1 width=12)"
> > "                      Index Cond: ((nro_sucursal = $4) AND
> >     
> (nro_carta_porte
>   
> > = $3))"
> > "                ->  Seq Scan on productos p  (cost=0.00..1.28 rows=4
> > width=8)"
> > "                      Filter: ((nombre)::text = ANY (('{"bultos ** SIN
> > CARGO",bultos,BULTOS,"bultos ."}'::character varying[])::text[]))"
> > "    ->  Aggregate  (cost=9.81..9.82 rows=1 width=4)"
> > "          ->  Nested Loop  (cost=0.00..9.81 rows=1 width=4)"
> > "                Join Filter: (cpp.id_producto = p.id_producto)"
> > "                ->  Index Scan using nro_sucursal_nro_carta_porte_idx on
> > carta_porte_producto cpp  (cost=0.00..8.42 rows=1 width=12)"
> > "                      Index Cond: ((nro_sucursal = $4) AND
> >     
> (nro_carta_porte
>   
> > = $3))"
> > "                ->  Seq Scan on productos p  (cost=0.00..1.32 rows=5
> > width=8)"
> > "                      Filter: ((nombre)::text = ANY
> > (('{pallets,PALLETS,"pallets ** SIN CARGO",termos,TERMOS,"pallets
> > ."}'::character varying[])::text[]))"
> > "    ->  Result  (cost=0.00..0.01 rows=1 width=0)"
> >
> > No virus found in this outgoing message.
> > Checked by AVG Free Edition. 
> > Version: 7.5.516 / Virus Database: 269.21.2/1304 - Release Date:
> >     
> 2008-02-29
>   
> > 08:18 a.m.
> >  
> >
> > --
> > TIP 7: no olvides aumentar la configuración del "free space map"
> >   
> >     
> Hola, la funcion extraer_cliente que hace?, tenes forma de reemplazarla 
> por join?
>
> Saludos Fernando
>
> ----------------------------------------------------------------------
>
> Te comento, la function extraer_clientes() los que hace es evaluar dos
> caompos para traer solamente el id cuando se cumple cierta condicion.
> Te copio la Function.
>
> DECLARE
>     cobro ALIAS FOR $1;
>     cliente_origen ALIAS FOR $2;
>     cliente_destino ALIAS FOR $3;
>     resultado INTEGER := 0;
> BEGIN
> 	IF (cobro = 'origen') THEN
> 		resultado := cliente_origen;
> 	ELSE
> 		resultado := cliente_destino;
> 	END IF;
>
> RETURN resultado;
> END
>
>
> De todas maneras creo que la la mayor carga en la consulta se da porque
> tiene que buscar por campo timestamp, es asi ?
>
> ->  Seq Scan on carta_porte cp
>   
> > (cost=0.00..35286.27 rows=1345 width=84)"
> > "                                            Filter: ((fecha_despachada >=
> > '2007-01-01 00:00:00'::timestamp without time zone) AND (fecha_despachada
> >     
> <=
>   
> > '2008-02-01 00:00:00'::timestamp without time zone) AND ((subplan) =
> >     
> 9000))"
>
>
>
>
>
>
>  
>
> No virus found in this outgoing message.
> Checked by AVG Free Edition. 
> Version: 7.5.516 / Virus Database: 269.21.2/1304 - Release Date: 2008-02-29
> 08:18 a.m.
>  
>
> --
> TIP 10: no uses HTML en tu pregunta, seguro que quien responda no podrá leerlo
>
>   
Hola, la idea de una funcion me parece buena pero podrias probar esto:
en el select
       case when cp.cobro='orgigen'  then  cp.id_cliente_origen
               else  cp.id_cliente_destino
       end ||......

en el where

((cp.cobro='orgigen' and cp.id_cliente_origen> '9000') or 
(cp.cobro<>'orgigen' and cp.id_cliente_destino>'9000'))

quizas ahora funcione un poco mas rápido

saludos Fernando