Re: Consutla SQL

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