RE: Consutla SQL

-----Mensaje original-----
De: Rodriguez Fernando [mailto:rodriguez(at)ort(dot)edu(dot)uy]
Enviado el: Viernes, 29 de Febrero de 2008 02:11 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:
> -----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

Si lo tenia puesto de esa manera pero cree esta function para facilitar la
sintaxis en las consultas.

De todas maneras antes de implementarlo le realizamos algnuos test y
practicamente era el mismo costo, o sea la diferencia es muy minima.

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.