Buenas noches, si alguno de uds tiene tiempito, se anima a explicarme la
salida del explain analyze y si es posible tomar como ejemplo la sentencia
select que estoy tratando de optimizar? Desde ya muchas gracias!!
*La sentencia es:*
EXPLAIN ANALYZE SELECT
coalesce(historicotemp.afiapellido, '') || ', ' ||
coalesce(historicotemp.afinombre,'') nombre,
historicotemp.clavebeneficiario,
historicotemp.afidni,
to_char(historicotemp.afifechanac,'dd-mm-yyyy')
FROM
nacer.historicotemp
WHERE
cuieefectorasignado='A20002' AND
periodo= '2011-04-01'::timestamp - '1 month'::interval AND
activo = 'S' AND
historicotemp.afitipocategoria in (3, 4) AND
date_part('year',age(periodo,afifechanac)) = 0
ORDER BY
historicotemp.afiapellido ASC,
historicotemp.afinombre ASC;
*y la salida es:*
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=175739.38..175739.38 rows=1 width=53) (actual
time=2410.441..2410.441 rows=1 loops=1)
Sort Key: afiapellido, afinombre
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on historicotemp (cost=24467.24..175739.37 rows=1
width=53) (actual time=2376.441..2410.431 rows=1 loops=1)
Recheck Cond: ((periodo = '2011-03-01 00:00:00'::timestamp without
time zone) AND (activo = 'S'::bpchar))
Filter: ((afitipocategoria = ANY ('{3,4}'::integer[])) AND
((cuieefectorasignado)::text = 'A20002'::text) AND (date_part('year'::text,
age(periodo, afifechanac)) = 0::double precision))
-> BitmapAnd (cost=24467.24..24467.24 rows=64470 width=0) (actual
time=2155.042..2155.042 rows=0 loops=1)
-> Bitmap Index Scan on "historicoTemp_idx_periodo"
(cost=0.00..5102.60 rows=249850 width=0) (actual time=43.302..43.302
rows=251781 loops=1)
Index Cond: (periodo = '2011-03-01 00:00:00'::timestamp
without time zone)
-> Bitmap Index Scan on historicotemp_idx_activo
(cost=0.00..19364.40 rows=947156 width=0) (actual time=2109.535..2109.535
rows=1068593 loops=1)
Index Cond: (activo = 'S'::bpchar)
Total runtime: 2410.531 ms
(12 rows)
(END)
