Explain de store procedure

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?

Gracias!

Alejandro

2010/5/13 Alejandro D. Burne <alejandro(dot)dburne(at)gmail(dot)com>:
> 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.
>

chequea los tipos de datos.
si estas usando 8.4 puedes usar auto explain indicandole que muestre
el plan de ejecucion de las sentencias dentro de las funciones con el
parametro auto_explain.log_nested_statements

--
Jaime Casanova www.2ndQuadrant.com
Soporte y capacitación de PostgreSQL

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
--

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"

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

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

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

> Dale una leída a esto a ver si te ayuda, y me cuentas:
>
> http://alvherre.livejournal.com/4324.html

A qué te refieres con "normalmente es posible solucionar estos problemas
usando EXECUTE para evitar el uso de un plan preparado"?

que TODAS las sentencias en una función pl/pgSQL deberían ser ejecutadas
usando SQL DINÁMICO (cadenas) para evitar utilizar planes guardados?

Atentamente,

RAUL DUQUE
Bogotá, Colombia

Buenas tardes listeros,

en cual de las tablas del catálogo puedo encontrar las relaciones entre las tablas de mi base de datos, tipo de relación que tienen y cardinalidades, Saludos,

Arcel

----- Mensaje original -----
De: "Raúl Andrés Duque Murillo " < ra_duque @ yahoo . com . mx >
Para: " Alvaro Herrera" < alvherre @ alvh .no-ip. org >, "Alejandro D. Burne " < alejandro . dburne @ gmail . com >
CC : " POSTGRES " <pgsql-es-ayuda@ postgresql . org >
Enviados: Jueves, 13 de Mayo 2010 15:55:16 (GMT-0500) Auto-Detected
Asunto: Re : [pgsql-es-ayuda] Explain de store procedure

> Dale una leída a esto a ver si te ayuda, y me cuentas:
>
> http :// alvherre . livejournal . com /4324. html

A qué te refieres con "normalmente es posible solucionar estos problemas
usando EXECUTE para evitar el uso de un plan preparado"?

que TODAS las sentencias en una función pl / pgSQL deberían ser ejecutadas
usando SQL DINÁMICO (cadenas) para evitar utilizar planes guardados?

Atentamente,

RAUL DUQUE
Bogotá , Colombia

-
Enviado a la lista de correo pgsql-es-ayuda (pgsql-es-ayuda@ postgresql . org )
Para cambiar tu suscripci �n:
http ://www. postgresql . org /mailpref/pgsql-es-ayuda

Excerpts from Raúl Andrés Duque Murillo's message of jue may 13 15:55:16 -0400 2010:
> > Dale una le\xc3\xadda a esto a ver si te ayuda, y me cuentas:
> >
> > http://alvherre.livejournal.com/4324.html
>
> A qu\xc3\xa9 te refieres con "normalmente es posible solucionar estos problemas
> usando EXECUTE para evitar el uso de un plan preparado"?
>
> que TODAS las sentencias en una funci\xc3\xb3n pl/pgSQL deber\xc3\xadan ser ejecutadas
> usando SQL DIN\xc3\x81MICO (cadenas) para evitar utilizar planes guardados?

Efectivamente
--

>> que TODAS las sentencias en una funci\xc3\xb3n pl/pgSQL deber\xc3\xadan
>> ser ejecutadas
>> usando SQL DIN\xc3\x81MICO (cadenas) para evitar utilizar planes
>> guardados?
>
> Efectivamente

Ninguna otra alternativa?

(almenos yo) trato al máximo evitar el uso de sql dinámico ya que complican
mucho la depuración, lectura y mantenimiento de la funciones.

Los planes de ejecucción se guardan por server (creo) ... al reiniciar el
servidor, no estaría obligando a que se vuelvan a calcular y el problema
desaparece?

A lo que voy es que es un problema temporal, porque por la afirmación de
arriba nos haría pensar que debemos migrar todas nuestra funciones pl/pgsql
para hacer uso de sql dinámico cosa que me parece horrible.

Atentamente,

RAUL DUQUE
Bogotá, Colombia

Excerpts from Raúl Andrés Duque Murillo's message of jue may 13 17:13:30 -0400 2010:

> Ninguna otra alternativa?

No todos los planes tienen los problemas que plantea Alejandro. Si tu
función no tiene este problema, no es necesario hacer nada.

> (almenos yo) trato al m\xc3\xa1ximo evitar el uso de sql din\xc3\xa1mico ya que complican
> mucho la depuraci\xc3\xb3n, lectura y mantenimiento de la funciones.

Sí, por supuesto.

> Los planes de ejecucci\xc3\xb3n se guardan por server (creo) ... al reiniciar el
> servidor, no estar\xc3\xada obligando a que se vuelvan a calcular y el problema
> desaparece?

Si te parece conveniente reiniciar el servidor cada vez que necesites
que se recalcule el plan, adelante.

--

> No todos los planes tienen los problemas que plantea Alejandro. Si tu
> función no tiene este problema, no es necesario hacer nada.

Gracias Alvaro. Que condiciones deben darse para que se presente este
problema?

El 13 de mayo de 2010 19:59, Raúl Andrés Duque Murillo <
ra_duque(at)yahoo(dot)com(dot)mx> escribió:

> No todos los planes tienen los problemas que plantea Alejandro. Si tu
>> función no tiene este problema, no es necesario hacer nada.
>>
>
> Gracias Alvaro. Que condiciones deben darse para que se presente este
> problema?
>

Me parece que es una situación de casuística más que una regla, en esa bd
hay unos 160 SPs y es la primera vez que ocurre con alguno de ellos. Es más
dentro de ese SP hay varias consultas y solo la que expuse es la que tiene
el inconveniente; creo que la única manera de detectarlos es analizando los
logs periódicamente y analizando aquellas consultas lentas. También en las
quejas de los usuarios ;-)
Porque dependiendo de los parámetros que se le envíen el plan puede ser
eficiente en determinados casos y terrible en otros para el mismo SP.

Saludos, Alejandro

Excerpts from Alejandro D. Burne's message of jue may 13 20:26:10 -0400 2010:
> El 13 de mayo de 2010 19:59, Raúl Andrés Duque Murillo <
> ra_duque(at)yahoo(dot)com(dot)mx> escribió:
>
> > No todos los planes tienen los problemas que plantea Alejandro. Si tu
> >> función no tiene este problema, no es necesario hacer nada.
> >>
> >
> > Gracias Alvaro. Que condiciones deben darse para que se presente este
> > problema?
>
> Me parece que es una situación de casuística más que una regla, en esa bd
> hay unos 160 SPs y es la primera vez que ocurre con alguno de ellos. Es más
> dentro de ese SP hay varias consultas y solo la que expuse es la que tiene
> el inconveniente; creo que la única manera de detectarlos es analizando los
> logs periódicamente y analizando aquellas consultas lentas. También en las
> quejas de los usuarios ;-)
> Porque dependiendo de los parámetros que se le envíen el plan puede ser
> eficiente en determinados casos y terrible en otros para el mismo SP.

Justamente. El problema se presenta cuando uno de los posibles valores
de un parámetro causa un plan diferente que el resto de los posibles
valores; por ej. que uno de los valores más comunes ocupe un % de la
tabla mucho mayor que el resto (si ese parámetro pudiera usarse como
condición de índice, la existencia de ese valor tan común haría que se
usara un recorrido secuencial de la tabla en todas las ejecuciones de la
función, haciéndola mucho más lenta cuando se usan los valores para los
cuales se podría usar un índice).
--