Skip site navigation (1) Skip section navigation (2)

Consutla SQL

From: "Daniel Ferrer" <daniel(dot)ferrer(at)ctd(dot)com(dot)ar>
To: <pgsql-es-ayuda(at)postgresql(dot)org>
Subject: Consutla SQL
Date: 2008-02-29 13:34:43
Message-ID: 20080229133446.B1BD02E0098@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-es-ayuda
 
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.
 


In response to

Responses

pgsql-es-ayuda by date

Next:From: Alvaro HerreraDate: 2008-02-29 13:53:33
Subject: Re: Error borrando datos de tabla en 8.3
Previous:From: Conrado BlasettiDate: 2008-02-29 12:32:30
Subject: Resultado apaisado

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group