Re: Como leeer el explain analyze

2008/12/30 Edwin Quijada <listas_quijada(at)hotmail(dot)com>:
>
> Hola!
> Me estoy eneredando con una consulta que esta muy lenta y estoy tratando de entender el asunto del explain analyze para ver como mejorar el performance pero la verdad entiendo poco la informacion que ahi ofrece.
> No existe alguna informacion de como se podria interpretar de una mejor forma esta informacion de forma tal que podamos analizar cualquier consulta?
>
>
> Ejemplo:
> QUERY PLAN
> Limit (cost=14910.15..14910.15 rows=1 width=20) (actual time=0.093..0.093 rows=0 loops=1)
> InitPlan
> -> Seq Scan on tsys_leads_lists l2 (cost=0.00..178.34 rows=1 width=0) (actual time=0.038..0.038 rows=1 loops=1)
> Filter: ((fsys_attempts> 0) AND (fsys_loaddatetime = '2008-12-22 12:08:09'::timestamp without time zone))
> -> Sort (cost=14731.80..14733.92 rows=847 width=20) (actual time=0.091..0.091 rows=0 loops=1)
> Sort Key: l.fsys_lastdial_datetime, l.fsys_id_record
> Sort Method: quicksort Memory: 17kB
> -> Result (cost=0.00..14727.57 rows=847 width=20) (actual time=0.041..0.041 rows=0 loops=1)
> One-Time Filter: (NOT $2)
> -> Nested Loop (cost=0.00..14727.57 rows=847 width=20) (never executed)
> -> Seq Scan on tsys_leads_original o (cost=0.00..9238.67 rows=945 width=4) (never executed)
> Filter: ((fsys_id_project = 102) AND (aall_general.asys_geography_get_timezone1_datetime(fsys_phone1)>= ((((((aall_general.asys_geography_get_timezone1_datetime(fsys_phone1))::date)::character varying)::text || ' '::text) || '09:00:00 AM'::text))::timestamp without time zone) AND (aall_general.asys_geography_get_timezone1_datetime(fsys_phone1) <= ((((((aall_general.asys_geography_get_timezone1_datetime(fsys_phone1))::date)::character varying)::text || ' '::text) || '09:00:00 PM'::text))::timestamp without time zone))
> -> Index Scan using tsys_leads_lists_idx on tsys_leads_lists l (cost=0.00..5.80 rows=1 width=28) (never executed)
> Index Cond: (l.fsys_unoid = o.fsys_unoid)
> Filter: ((l.fsys_loaddatetime = '2008-12-22 12:08:09'::timestamp without time zone) AND (l.fsys_lead_activation_mark = 1) AND (l.fsys_attempts = 0) AND ((l.fsys_lastdial_datetime IS NULL) OR (l.fsys_lastdial_datetime < (now())::date)) AND ((l.fsys_active = 1) OR ((l.fsys_active = 0) AND (l.fsys_lastget_datetime < (now())::date))) AND ((l.fsys_code IS NULL) OR ((l.fsys_code)::text = ''::text) OR (subplan)))
> SubPlan
> -> Seq Scan on tsys_script_dispositions d (cost=0.00..5.09 rows=1 width=0) (never executed)
> Filter: ((($0)::text = (fsys_code)::text) AND (fsys_done = 0))
> Total runtime: 0.200 ms
>
>

A la lista ...

Una forma sencilla es interpretar el gráfico que tira el pgadmin con
el botón "Explain".
Ahí se muestra claramente con flechas anchas cuando se estima que el
acceso a una tabla es costoso.
Silvio

--
Silvio Quadri