La consulta es esta:
SELECT a."FECHAPROC" AS fechaproc,
a."LOCALIDAD" AS localidad,
a."URBANIZA" AS urbaniza,
a."CALLE" AS calle,
a."CLICODFAC" AS clicodfac,
a."NOMBRE" AS nombre,
a."NROMUNI" AS nromuni,
a."MANZCEN" AS manzcen,
a."LOTECEN" AS lotecen,
a."TS" AS ts,
a."EC" AS ec,
a."ECA" AS eca,
a."ECD" AS ecd,
a."TARIFA" AS tarifa,
a."CMEDI" AS cmedi,
a."CICLO" AS ciclo,
b.id,
b.clvmzna,
b.lote,
r.ciclo_comercial AS ciclo_com_gis,
5000 + rd.trazo_id AS carga,
rd.numero AS orden_carga,
row_number() OVER () AS orden_impresion,
count(d.gid) AS gid,
string_agg((to_char(d.horaini::interval, 'HH24:MI'::text) || '-'::text)
|| to_char(d.horafin::interval, 'HH24:MI'::text), ', '::text ORDER BY
d.zona) AS hopdes,
string_agg((d.fechaini::text || '-'::text) || d.fechafin::text, ',
'::text ORDER BY d.zona) AS fechas_inicio_fin,
sum(d.tiempo) AS horas_servicio_horas,
sum(d.tiempo_num) AS horcnt,
CASE
WHEN length(b.id::text) > 0 THEN '1-Con Horario'::character
varying
ELSE '2-Sin Horario'::character varying
END AS tienehorario
FROM unidos a
LEFT JOIN (cat_lote b
LEFT JOIN mag_zonas d ON st_contains(d.the_geom, b.geo_punto) AND
d.vigente = 1
LEFT JOIN (cat_lote e
LEFT JOIN rut_detalle rd ON rd.gid = e.gid
JOIN rut_trazo r ON r.trazo_id = rd.trazo_id) ON b.gid = e.gid AND
r.vigente = 1) ON a."CLICODFAC"::text = b.id::text
WHERE date_part('year'::text, a."FECHAPROC") >= 2015::double precision
AND a."EC" = 1
GROUP BY a."FECHAPROC", a."LOCALIDAD", a."URBANIZA", a."CALLE",
a."CLICODFAC", a."NOMBRE", a."NROMUNI", a."MANZCEN", a."LOTECEN", a."TS",
a."EC", a."ECA", a."ECD", a."TARIFA", a."CMEDI", a."CICLO", b.id,
b.clvmzna, b.lote, r.ciclo_comercial, rd.trazo_id, rd.numero
ORDER BY r.ciclo_comercial, rd.trazo_id, rd.numero
En el explain analyze obtengo esto
"WindowAgg (cost=9740706.30..9794981.40 rows=374311 width=230)
(actual time=52168.480..65089.359 rows=1122034 loops=1)"
" -> GroupAggregate (cost=9740706.30..9787495.18 rows=374311
width=230) (actual time=52168.457..62914.819 rows=1122034 loops=1)"
" Group Key: r.ciclo_comercial, rd.trazo_id, rd.numero,
a."FECHAPROC", a."LOCALIDAD", a."URBANIZA", a."CALLE", a."CLICODFAC",
a."NOMBRE", a."NROMUNI", a."MANZCEN", a."LOTECEN", a."TS", a."EC",
a."ECA", a."ECD", a."TARIFA", a."CMEDI", a."CICLO", b.id, (...)"
" -> Sort (cost=9740706.30..9741642.08 rows=374311 width=230)
(actual time=52168.158..53177.792 rows=1122060 loops=1)"
" Sort Key: r.ciclo_comercial, rd.trazo_id, rd.numero,
a."FECHAPROC", a."LOCALIDAD", a."URBANIZA", a."CALLE", a."CLICODFAC",
a."NOMBRE", a."NROMUNI", a."MANZCEN", a."LOTECEN", a."TS", a."EC",
a."ECA", a."ECD", a."TARIFA", a."CMEDI", a."CICLO", (...)"
" *Sort Method: external merge Disk: 266152kB"*
" -> Hash Right Join (cost=150284.12..9706056.56
rows=374311 width=230) (actual time=3495.335..21677.962 rows=1122060
loops=1)"
" Hash Cond: ((b.id)::text = (a."CLICODFAC")::text)"
" -> Merge Left Join (cost=6233.57..9513169.71
rows=269017 width=99) (actual time=78.767..16777.771 rows=269602
loops=1)"
" Merge Cond: (b.gid = e.gid)"
" -> Nested Loop Left Join
(cost=0.42..9494129.99 rows=269017 width=91) (actual
time=1.008..16366.263 rows=269602 loops=1)"
" Join Filter: ((d.the_geom &&
b.geo_punto) AND _st_contains(d.the_geom, b.geo_punto))"
" Rows Removed by Join Filter: 35379942"
" -> Index Scan using cat_lote_pkey on
cat_lote b (cost=0.42..83873.24 rows=269017 width=59) (actual
time=0.016..406.860 rows=269590 loops=1)"
" -> Materialize (cost=0.00..42.42
rows=132 width=902) (actual time=0.000..0.005 rows=132 loops=269590)"
" -> Seq Scan on mag_zonas d
(cost=0.00..41.76 rows=132 width=902) (actual time=0.064..0.667
rows=132 loops=1)"
" Filter: (vigente = 1)"
" Rows Removed by Filter: 9"
" -> Materialize (cost=6233.15..17834.78
rows=42592 width=16) (actual time=77.755..286.031 rows=101567
loops=1)"
" -> Merge Join
(cost=6233.15..17728.30 rows=42592 width=16) (actual
time=77.751..263.574 rows=101565 loops=1)"
" Merge Cond: (rd.gid = e.gid)"
" -> Sort
(cost=6123.95..6230.43 rows=42592 width=16) (actual
time=77.341..91.721 rows=101565 loops=1)"
" Sort Key: rd.gid"
" Sort Method: quicksort
Memory: 7833kB"
" -> Hash Join
(cost=76.55..2848.99 rows=42592 width=16) (actual time=0.992..44.744
rows=101565 loops=1)"
" Hash Cond:
(rd.trazo_id = r.trazo_id)"
" -> Seq Scan on
rut_detalle rd (cost=0.00..1965.65 rows=101565 width=12) (actual
time=0.030..18.360 rows=101565 loops=1)"
" -> Hash
(cost=76.39..76.39 rows=13 width=8) (actual time=0.934..0.934 rows=13
loops=1)"
" Buckets: 1024
Batches: 1 Memory Usage: 1kB"
" -> Seq Scan
on rut_trazo r (cost=0.00..76.39 rows=13 width=8) (actual
time=0.023..0.926 rows=13 loops=1)"
" Filter:
(vigente = 1)"
" Rows
Removed by Filter: 18"
" -> Index Only Scan using
cat_lote_pkey on cat_lote e (cost=0.42..10403.68 rows=269017 width=4)
(actual time=0.236..100.399 rows=269590 loops=1)"
" Heap Fetches: 17732"
" -> Hash (cost=139371.66..139371.66 rows=374311
width=131) (actual time=3415.655..3415.655 rows=1122035 loops=1)"
" Buckets: 65536 Batches: 1 Memory Usage: 185529kB"
" -> Seq Scan on unidos a
(cost=0.00..139371.66 rows=374311 width=131) (actual
time=0.045..2683.709 rows=1122035 loops=1)"
" Filter: (("EC" = 1) AND
(date_part('year'::text, ("FECHAPROC")::timestamp without time zone)
>= 2015::double precision))"
" Rows Removed by Filter: 47815"
"Planning time: 13.085 ms"
"Execution time: 65367.274 ms"
Estoy corriendo sobre postgres 9.4 en windows server 2008
mis parametros son los de la instalacion por defecto solo he tocado estos
dos:
shared_buffers = 512MB # min 128kB
work_mem = 384MB # min 64kB
Solo modifique el work_mem a raiz de que vi esta linea en el explain
analyze
*Sort Method: external merge Disk: 266152kB"*
lo subi a 384MB pero obtuve lo mismo me podrian sugerir que se podria hacer
para mejorar.
Creo que el problema esta aqui
"WindowAgg (cost=9740706.30..9794981.40 rows=374311 width=230)
(actual time=52168.480..65089.359 rows=1122034 loops=1)"
" -> GroupAggregate (cost=9740706.30..9787495.18 rows=374311
width=230) (actual time=52168.457..62914.819 rows=1122034 loops=1)"
donde el cost esta muy diferente del rows pero no tengo una idea de
como poder arreglarlo
--
José Mercedes Venegas Acevedo
cel claro 940180540
mails: jvenegasp...@gmail.com
