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

consulta se demora mucho mas que antes

From: Miguel <mmiranda(at)123(dot)com(dot)sv>
To: pgsql-es-ayuda(at)postgresql(dot)org
Subject: consulta se demora mucho mas que antes
Date: 2006-03-30 16:31:29
Message-ID: 442C07E1.70006@123.com.sv (view raw or flat)
Thread:
Lists: pgsql-es-ayuda
saludos listeros, tengo un problema que por alguna razon a partir de 
ayer una consulta se demoramuhchisimo mas de lo que demoraba antes, no 
se ha cambiado nada, excepto que es es una tabla donde se guarda el 
trafico diario, por lo que aunmenta como 15000 filas diarias, antes se 
tardaba como 6 segundos y ahora como 2.5 minutos, esta es la tabla

radius=# \d stopacct;
                                  Table "public.stopacct"
       Column        |            Type             |               Modifiers
---------------------+-----------------------------+----------------------------------------
 radacctid           | bigint                      | not null
 h323connecttime     | timestamp with time zone    | not null
 h323disconnecttime  | timestamp with time zone    | not null
 username            | character varying(32)       |
 nasipaddress        | inet                        | not null
 acctsessiontime     | bigint                      |
 calledstationid     | character varying(50)       |
 callingstationid    | character varying(50)       |
 acctdelaytime       | smallint                    |
 cisconasport        | character varying(16)       |
 h323callorigin      | character varying(10)       | not null
 h323calltype        | character varying(64)       | not null default 
''::character varying
 h323disconnectcause | character varying(2)        |
 h323confid          | character varying(35)       | not null
 accttarifa          | double precision            |
 acctstoptime        | timestamp without time zone | default now()
 roundedsessiontime  | bigint                      |
Indexes:
    "stopacct_pkey" PRIMARY KEY, btree (radacctid, h323calltype)
    "stopacct_calledstationid" btree (calledstationid)
    "stopacct_callingstationid" btree (callingstationid)
    "stopacct_h323disconnectcause" btree (h323disconnectcause)
    "stopacct_h323disconnecttime" btree (h323disconnecttime)
    "stopacct_username" btree (username)

y esta la consulta, por desgracia no tengo el resultado del explain 
analyze cuando se tardaba segundos:

radius=# explain
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 h323callorigin = 'originate'
radius-# and h323disconnecttime::date = '2006-03-29'
radius-# and idproducto in  (11,40,41)
radius-# ;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=394204.20..394204.21 rows=1 width=16)
   ->  Nested Loop  (cost=0.00..394197.32 rows=1374 width=16)
         ->  Seq Scan on stopacct a  (cost=0.00..383479.14 rows=1777 
width=32)
               Filter: (((h323callorigin)::text = 'originate'::text) AND 
((h323disconnecttime)::date = '2006-03-29'::date))
         ->  Index Scan using pines_pkey on pines b  (cost=0.00..6.02 
rows=1 width=13)
               Index Cond: (("outer".username)::text = (b.pin)::text)
               Filter: ((idproducto = 11) OR (idproducto = 40) OR 
(idproducto = 41))


me parece sospechoso el costo del Seq San:383479.14, de las clausulas 
del whete solameente h323callorigin no tiene indice, ya que solamente 
puede tener dos valores (originate o answer), es necesario un indice en 
estos casos?.

agradeceria cualquier comentario
gracias



 




Responses

pgsql-es-ayuda by date

Next:From: Mario GonzalezDate: 2006-03-30 16:55:01
Subject: Re: bajar/levantar motor Postgresql
Previous:From: Mario GonzalezDate: 2006-03-30 16:13:44
Subject: Re: bajar/levantar motor Postgresql

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