Re: Explain de store procedure

El 13 de mayo de 2010 14:51, Silvio Quadri <silvioq(at)gmail(dot)com> escribió:

> 2010/5/13 Alejandro D. Burne <alejandro(dot)dburne(at)gmail(dot)com>:
> > 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"
>
>
> El tema es que en el primer caso, no sabe a priori el motor que código
> de prestación vas a usar ... por lo cual, no siempre podrá aplicar el
> mismo plan ...
> Ya que el 420101 debe ser el código más común (no conozco tus datos,
> pero puede llegar hasta el 30% de las prácticas), quizás el índice
> óptimo sea el código de afiliado ...
> ¿No te conviene reescribir la consulta para que tome ese índice en vez
> del código de prestación (sin usar los substrings)?
> La otra opción es tratar de escribir la misma consulta con un
> subquery, de tal forma que te agarre sí o sí ese índice.
> Silvio
>

Con tus dichos, ahora me surge una duda, el plan de ejecución lo arma al
momento de crear el SP y almacena ese plan en ese momento, luego nunca mas
lo recalcula?

Gracias x las respuestas, Alejandro