El 13 de mayo de 2010 12:41, Alvaro Herrera <alvhe...@alvh.no-ip.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"
