Re: Consutla SQL

From: Rodriguez Fernando <rodriguez(at)ort(dot)edu(dot)uy>
To: daniel(dot)ferrer(at)ctd(dot)com(dot)ar
Cc: pgsql-es-ayuda(at)postgresql(dot)org
Subject: Re: Consutla SQL
Date: 2008-02-29 14:24:03
Message-ID: 47C81583.70501@ort.edu.uy
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-es-ayuda

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

In response to

Responses

Browse pgsql-es-ayuda by date

  From Date Subject
Next Message juan jaimes 2008-02-29 14:48:40 obtener contactos
Previous Message Alvaro Herrera 2008-02-29 13:53:33 Re: Error borrando datos de tabla en 8.3