Re: consulta se demora mucho mas que antes

Alvaro Herrera wrote:

>>Invertir el orden no ayudo mucho:
>>
>>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 h323disconnecttime::date = '2006-03-29'::date
>>radius-# and h323callorigin = 'originate'
>>radius-# and idproducto in (11,40,41);
>> QUERY PLAN
>>----------------------------------------------------------------------------------------------------------------------------
>>Aggregate (cost=393646.90..393646.91 rows=1 width=16)
>> -> Nested Loop (cost=0.00..393639.84 rows=1411 width=16)
>> -> Seq Scan on stopacct a (cost=0.00..382461.38 rows=1853
>>width=31)
>> Filter: (((h323disconnecttime)::date =
>>'2006-03-29'::date) AND ((h323callorigin)::text = 'originate'::text))
>> -> 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))
>>(7 rows)
>>
>>
>
>Me da la impresion de que el cast ese a date no ayuda mucho. Que pasa
>si usas algo como
>
>WHERE h323disconnecttime between
> '2006-03-29'::timestamptz and '2006-03-30'::timestamptz
> and h323callorigin = 'originate'
>
>(obviamente cambia timestamptz a timestamp si tu campo no tiene TZ, lo
>cual seria un error)
>
>
>
Al cambiar el cast la respuesta es casi instantanea, pero no arroja
resultados :-(

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-29'::timestamptz and
'2006-03-29'::timestamptz
and h323callorigin = 'originate'
and idproducto in (11,40,41)radius-# and h323callorigin = 'originate'
radius-# and idproducto in (11,40,41)
radius-# ;

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=9.41..9.43 rows=1 width=16) (actual time=0.031..0.032
rows=1 loops=1)
-> Nested Loop (cost=0.00..9.40 rows=1 width=16) (actual
time=0.024..0.024 rows=0 loops=1)
-> Index Scan using stopacct_h323disconnecttime on stopacct a
(cost=0.00..3.37 rows=1 width=31) (actual time=0.021..0.021 rows=0 loops=1)
Index Cond: ((h323disconnecttime >= '2006-03-29
00:00:00-06'::timestamp with time zone) AND (h323disconnecttime <=
'2006-03-29 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) (never executed)
Index Cond: (("outer".username)::text = (b.pin)::text)
Filter: ((idproducto = 11) OR (idproducto = 40) OR
(idproducto = 41))
Total runtime: 0.160 ms
(9 rows)

el campo si es timestampz:
radius=# \d stopacct;
Table "public.stopacct"
Column | Type | Modifiers
---------------------+-----------------------------+----------------------------------------
h323disconnecttime | timestamp with time zone | not null

como si funciona de maravilla es cambiando la condicion por

WHERE h323disconnecttime between
'2006-03-29 00:00:00' and '2006-03-29 23:59:59'

---
Miguel