Re: consulta se demora mucho mas que antes

Javier Aquino H. wrote:

>
> Tons usa lo siguiente:
>
> WHERE h323disconnecttime between
> '2006-03-29' and '2006-03-30'

Gracias Javier, tambien funciona asi, y es mucho mas simple, ahora me
sucede algo curioso que no se si sera un problema o no , la primera vez
que ejecuto la consulta se tarda aproximadamente 2.5 segundos, si vuelvo
a ejecutar la misma consulta nuevamente se tarda 0.3 segundos
aproximadamente ej:

radius=# explain analyze
radius-# select 'quemados',sum(acctsessiontime)/60 as minutos,
sum(roundedsessiontime)/60 as redondeados
radius-# from stopacct a inner join pines b on (a.username = b.pin)
radius-# where h323disconnecttime between '2006-03-01' and '2006-03-02'
radius-# and h323callorigin = 'originate'
radius-# and idproducto in (11,40,41);

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=53626.93..53626.95 rows=1 width=16) (actual
time=1941.089..1941.091 rows=1 loops=1)
-> Nested Loop (cost=0.00..53602.25 rows=4936 width=16) (actual
time=45.371..1923.064 rows=4264 loops=1)
-> Index Scan using stopacct_h323disconnecttime on stopacct a
(cost=0.00..14504.79 rows=6481 width=31) (actual time=45.341..347.760
rows=6554 loops=1)
Index Cond: ((h323disconnecttime >= '2006-03-01
00:00:00-06'::timestamp with time zone) AND (h323disconnecttime <=
'2006-03-02 00:00:00-06'::timestamp with time zone))
Filter: ((h323callorigin)::text = 'originate'::text)
-> Index Scan using pines_pkey on pines b (cost=0.00..6.02
rows=1 width=13) (actual time=0.233..0.235 rows=1 loops=6554)
Index Cond: (("outer".username)::text = (b.pin)::text)
Filter: ((idproducto = 11) OR (idproducto = 40) OR
(idproducto = 41))
Total runtime: 1941.227 ms
(9 rows)

radius=# explain analyze
radius-# select 'quemados',sum(acctsessiontime)/60 as minutos,
sum(roundedsessiontime)/60 as redondeados
radius-# from stopacct a inner join pines b on (a.username = b.pin)
radius-# where h323disconnecttime between '2006-03-01' and '2006-03-02'
radius-# and h323callorigin = 'originate'
radius-# and idproducto in (11,40,41);

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=53626.93..53626.95 rows=1 width=16) (actual
time=159.829..159.830 rows=1 loops=1)
-> Nested Loop (cost=0.00..53602.25 rows=4936 width=16) (actual
time=0.057..142.512 rows=4264 loops=1)
-> Index Scan using stopacct_h323disconnecttime on stopacct a
(cost=0.00..14504.79 rows=6481 width=31) (actual time=0.033..35.283
rows=6554 loops=1)
Index Cond: ((h323disconnecttime >= '2006-03-01
00:00:00-06'::timestamp with time zone) AND (h323disconnecttime <=
'2006-03-02 00:00:00-06'::timestamp with time zone))
Filter: ((h323callorigin)::text = 'originate'::text)
-> Index Scan using pines_pkey on pines b (cost=0.00..6.02
rows=1 width=13) (actual time=0.009..0.011 rows=1 loops=6554)
Index Cond: (("outer".username)::text = (b.pin)::text)
Filter: ((idproducto = 11) OR (idproducto = 40) OR
(idproducto = 41))
Total runtime: 159.967 ms
(9 rows)

si cambio el nuevamente el intervalo de fechas se tarda otra vez ~3
secs, es esto normal o no?, pareciera que psql guarda cache o algo.
atte
---
Miguel