Re: Explain de store procedure

El 13 de mayo de 2010 12:41, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>escribió:

> Excerpts from Alejandro D. Burne's message of jue may 13 09:53:58 -0400
> 2010:
> > Tengo un store procedure que dentro tiene una consulta, esa consulta al
> > momento de correrla dentro del SP me demora unos 36 segundos, ahora bien;
> si
> > ejecuto la misma (reemplazando los parametros por los mismos que le paso
> al
> > SP) me demora unos 36 ms.
> > El problema es que no puedo hacer un explain para el SP, alguna idea para
> > poder debuguear esto?
>
> Dale una leída a esto a ver si te ayuda, y me cuentas:
>
> http://alvherre.livejournal.com/4324.html
> --
>

Bueno, he seguido las indicaciones de Alvaro y dura como comenta en el
artículo casi mil veces mas la misma consulta.
Algun tip para estas situaciones? Gracias

PREPARE una_consulta(bpchar) AS SELECT SUM(Det.Cantidad)
FROM auth_prestaciones prest INNER JOIN auth_prestaciones_det det ON
prest.codauthprest=det.codauthprest
WHERE prest.codconv=6 AND prest.codplan IN (7,8) AND
SUBSTRING(prest.codafi FROM 1 FOR 13)=SUBSTRING('000000000220000' FROM 1
FOR 13) AND prest.prest_anulada=false AND
Det.CodigoNN=$1 AND
EXTRACT('YEAR' FROM Prest.Fec_Aut)=EXTRACT('YEAR' FROM CURRENT_DATE) AND
EXTRACT('MONTH' FROM Prest.Fec_Aut)=EXTRACT('MONTH' FROM CURRENT_DATE);

explain analyze EXECUTE una_consulta('420101');

Aggregate (cost=73534.91..73534.92 rows=1 width=2) (actual
time=42595.838..42595.838 rows=1 loops=1)
-> Nested Loop (cost=0.00..73534.90 rows=1 width=2) (actual
time=48.149..42595.828 rows=1 loops=1)
-> Index Scan using "IxAuth_PresDet_NN" on auth_prestaciones_det
det (cost=0.00..18770.85 rows=6616 width=14) (actual time=0.054..3733.577
rows=2852982 loops=1)
Index Cond: (codigonn = $1)
-> Index Scan using auth_prest_pkey on auth_prestaciones prest
(cost=0.00..8.27 rows=1 width=12) (actual time=0.012..0.012 rows=0
loops=2852982)
Index Cond: (prest.codauthprest = det.codauthprest)
Filter: ((NOT prest.prest_anulada) AND (prest.codplan = ANY
('{7,8}'::integer[])) AND (prest.codconv = 6) AND
("substring"((prest.codafi)::text, 1, 13) = '0000000002200'::text) AND
(date_part('YEAR'::text, (prest.fec_aut)::timestamp without time zone) =
date_part('YEAR'::text, (('now'::text)::date)::timestamp without time zone))
AND (date_part('MONTH'::text, (prest.fec_aut)::timestamp without time zone)
= date_part('MONTH'::text, (('now'::text)::date)::timestamp without time
zone)))
Total runtime: 42595.909 ms"

explain analyze SELECT SUM(Det.Cantidad)
FROM auth_prestaciones prest INNER JOIN auth_prestaciones_det det ON
prest.codauthprest=det.codauthprest
WHERE prest.codconv=6 AND prest.codplan IN (7,8) AND
SUBSTRING(prest.codafi FROM 1 FOR 13)=SUBSTRING('000000000220000' FROM 1
FOR 13) AND prest.prest_anulada=false AND
Det.CodigoNN='420101' AND
EXTRACT('YEAR' FROM Prest.Fec_Aut)=EXTRACT('YEAR' FROM CURRENT_DATE) AND
EXTRACT('MONTH' FROM Prest.Fec_Aut)=EXTRACT('MONTH' FROM CURRENT_DATE);

Aggregate (cost=75899.26..75899.27 rows=1 width=2) (actual
time=49.240..49.241 rows=1 loops=1)
-> Nested Loop (cost=0.00..75899.26 rows=1 width=2) (actual
time=1.633..49.218 rows=1 loops=1)
-> Index Scan using "IxAuth_Prest_Afi" on auth_prestaciones prest
(cost=0.00..75889.99 rows=1 width=12) (actual time=1.611..49.193 rows=1
loops=1)
Index Cond: (codconv = 6)
Filter: ((NOT prest_anulada) AND (codplan = ANY
('{7,8}'::integer[])) AND ("substring"((codafi)::text, 1, 13) =
'0000000002200'::text) AND (date_part('YEAR'::text, (fec_aut)::timestamp
without time zone) = date_part('YEAR'::text,
(('now'::text)::date)::timestamp without time zone)) AND
(date_part('MONTH'::text, (fec_aut)::timestamp without time zone) =
date_part('MONTH'::text, (('now'::text)::date)::timestamp without time
zone)))
-> Index Scan using "IxAuth_PresDet_Prest" on auth_prestaciones_det
det (cost=0.00..9.25 rows=2 width=14) (actual time=0.020..0.021 rows=1
loops=1)
Index Cond: (det.codauthprest = prest.codauthprest)
Filter: (det.codigonn = '420101'::bpchar)
Total runtime: 49.296 ms"