Re: Explain de store procedure

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