Skip site navigation (1) Skip section navigation (2)

Re: consulta se demora mucho mas que antes

From: Miguel <mmiranda(at)123(dot)com(dot)sv>
To: "Javier Aquino H(dot)" <JAquino(at)LexusEditores(dot)com>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Jaime Casanova <systemguards(at)gmail(dot)com>, pgsql-es-ayuda(at)postgresql(dot)org
Subject: Re: consulta se demora mucho mas que antes
Date: 2006-03-31 18:48:42
Message-ID: 442D798A.5050503@123.com.sv (view raw or flat)
Thread:
Lists: pgsql-es-ayuda
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



In response to

Responses

pgsql-es-ayuda by date

Next:From: Miguel OrtegaDate: 2006-03-31 18:52:51
Subject: Problemas con la Ñ otra vez....
Previous:From: Pablo BraulioDate: 2006-03-31 18:15:14
Subject: Re: Listar columnas de una tabla.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group