Re: consulta se demora mucho mas que antes

Alvaro Herrera wrote:

>Miguel escribió:
>
>
>>Alvaro Herrera wrote:
>>
>>
>>
>>>Me da la impresion de que el cast ese a date no ayuda mucho. Que pasa
>>>si usas algo como
>>>
>>>
>>>
>>Al cambiar el cast la respuesta es casi instantanea, pero no arroja
>>resultados :-(
>>
>>
>
>Obvio, porque te equivocaste en la condicion. Mira bien la segunda
>parte del BETWEEN. Lo que yo puse:
>
>
>
>>>WHERE h323disconnecttime between
>>> '2006-03-29'::timestamptz and '2006-03-30'::timestamptz
>>>and h323callorigin = 'originate'
>>>
>>>
>
>Lo que tu pusiste:
>
>
>
>>radius-# where h323disconnecttime between '2006-03-29'::timestamptz and
>>'2006-03-29'::timestamptz
>>
>>
>
>
>
ops, tienes razon, con respecto a mi comentario de que tambien funciona con
WHERE h323disconnecttime between
'2006-03-29 00:00:00' and '2006-03-29 23:59:59'

es cuestion de gustos cual where utilizar, ambos tienen el mismo costo:

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 between '2006-03-29 00:00:00' and
'2006-03-29 23:59:59'
radius-# and idproducto in (11,40,41);

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=87789.77..87789.78 rows=1 width=16) (actual
time=137.285..137.287 rows=1 loops=1)
-> Nested Loop (cost=0.00..87749.36 rows=8081 width=16) (actual
time=0.047..122.822 rows=3568 loops=1)
-> Index Scan using stopacct_h323disconnecttime on stopacct a
(cost=0.00..23743.18 rows=10610 width=31) (actual time=0.025..33.852
rows=5624 loops=1)
Index Cond: ((h323disconnecttime >= '2006-03-29
00:00:00-06'::timestamp with time zone) AND (h323disconnecttime <=
'2006-03-29 23:59:59-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.010 rows=1 loops=5624)
Index Cond: (("outer".username)::text = (b.pin)::text)
Filter: ((idproducto = 11) OR (idproducto = 40) OR
(idproducto = 41))
Total runtime: 137.421 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-29'::timestamptz and
'2006-03-30'::timestamptz
radius-# and h323callorigin = 'originate'
and idproducto in (11,40,41)radius-# and idproducto in (11,40,41);

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=87789.77..87789.78 rows=1 width=16) (actual
time=137.319..137.321 rows=1 loops=1)
-> Nested Loop (cost=0.00..87749.36 rows=8081 width=16) (actual
time=0.047..122.903 rows=3568 loops=1)
-> Index Scan using stopacct_h323disconnecttime on stopacct a
(cost=0.00..23743.18 rows=10610 width=31) (actual time=0.025..33.743
rows=5624 loops=1)
Index Cond: ((h323disconnecttime >= '2006-03-29
00:00:00-06'::timestamp with time zone) AND (h323disconnecttime <=
'2006-03-30 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.010 rows=1 loops=5624)
Index Cond: (("outer".username)::text = (b.pin)::text)
Filter: ((idproducto = 11) OR (idproducto = 40) OR
(idproducto = 41))
Total runtime: 137.451 ms
(9 rows)