Re: Explain de store procedure

El día 13 de mayo de 2010 14:56, Alejandro D. Burne
<alejandro(dot)dburne(at)gmail(dot)com> escribió:
> 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
>

No sé en 8.4. Hasta versiones anteriores, el plan se calcula una vez
por cada sesión.
Silvio

--
Silvio Quadri