RE: Consutla SQL

["Daniel Ferrer" <daniel(dot)ferrer(at)ctd(dot)com(dot)ar>]

-----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.