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

explain analize

From: Guillermo Villanueva <guillermovil(at)gmail(dot)com>
To: pgsql-es-ayuda <pgsql-es-ayuda(at)postgresql(dot)org>
Subject: explain analize
Date: 2011-07-19 01:27:18
Message-ID: CANm+PCAEBLdj8AHW2xCnFKfMsYRtB+yH-b0YgZvdUC_dtDCp3A@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-es-ayuda
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)

pgsql-es-ayuda by date

Next:From: Jaime CasanovaDate: 2011-07-19 07:50:31
Subject: Re: Consulta de PostgreSQL
Previous:From: Álvaro Hernández TortosaDate: 2011-07-18 23:44:01
Subject: Re: PostgreSQL y SAN

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