Re: consulta se demora mucho mas que antes

Jaime Casanova wrote:

>que tan seguido ejecutas VACUUM?
>
>
Tengo configurado autovacuum cada 24 horas

>
>
>
>>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 la otra tabla?
>
>
>
yup, aqui esta

radius=# \d pines
Table "public.pines"
Column | Type | Modifiers
------------------+-----------------------+-----------
pais | character varying(2) |
folio | bigint |
pin | character varying(20) | not null
password | character varying(15) |
monto_facial | numeric(6,2) |
monto_actual | numeric(10,4) |
idproducto | integer |
idplan | integer |
fecha_exp_fija | date |
fecha_exp_var | date |
fecha_creacion | date |
fecha_activacion | date |
cod_status | integer |
Indexes:
"pines_pkey" PRIMARY KEY, btree (pin)
"pines_cod_status" btree (cod_status)
"pines_fecha_exp_fija" btree (fecha_exp_fija)
"pines_fecha_exp_var" btree (fecha_exp_var)
"pines_folio" btree (folio)
"pines_idproducto" btree (idproducto)
"pines_monto_actual" btree (monto_actual)
"pines_monto_facial" btree (monto_facial)

radius=#

>>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-# ;
>>
>>
>
>que version de postgres es esta?
>
>
8.1.3, compilado desde fuentes.

>se me ocurre que deberia poder usar el indice en h323disconnecttime,
>intenta haciendo un cast en la fecha ('2006-03-29'::date)
>
>ademas, seria mas util el explain analyze
>
>
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 h323callorigin = 'originate'
radius-# and h323disconnecttime::date = ('2006-03-29'::date)
radius-# and idproducto in (11,40,41);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=393528.42..393528.44 rows=1 width=16) (actual
time=104836.785..104836.787 rows=1 loops=1)
-> Nested Loop (cost=0.00..393521.31 rows=1422 width=16) (actual
time=70750.115..104820.084 rows=3568 loops=1)
-> Seq Scan on stopacct a (cost=0.00..382220.83 rows=1873
width=31) (actual time=70658.687..81319.380 rows=5624 loops=1)
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) (actual time=4.171..4.172 rows=1 loops=5624)
Index Cond: (("outer".username)::text = (b.pin)::text)
Filter: ((idproducto = 11) OR (idproducto = 40) OR
(idproducto = 41))
Total runtime: 104837.033 ms
(8 rows)

:-(

Igual se tardo un mundo... nada que ver con los 2 segundos de antes
---
miguel