Hi. I'm having some trouble on the use of indexes.
The querys below are exactly the same but refer to different months.
One case uses indexes, the other doesn't.
Is there anything I can do? Increasing index mem size?
Query 2 hash 9105 entries matching the given conditions
Query 2 hash 9248 entries matching the given conditions
QUERY 1:
explain select ra_datacolh::date, count(distinct ra_utente) from requisicaoanalise where (ra_datacolh::date >=
'2003-4-01'::dateand ra_datacolh::date < '2003-5-1'::date) and isactive=0 group by ra_datacolh;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Aggregate (cost=0.00..12300.68 rows=313 width=8)
-> Group (cost=0.00..12292.86 rows=3130 width=8)
-> Index Scan using requisicaoanalise_datacolh on requisicaoanalise (cost=0.00..12285.03 rows=3130 width=8)
Index Cond: ((ra_datacolh >= '2003-04-01'::date) AND (ra_datacolh < '2003-05-01'::date))
Filter: (isactive = 0)
QUERY 2:
netlab=> explain select ra_datacolh::date, count(distinct ra_utente) from requisicaoanalise where
(ra_datacolh::date>= '2003-6-01'::date and ra_datacolh::date < '2003-7-1'::date) and isactive=0 group by ra_datacolh;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=32452.98..32632.06 rows=2388 width=8)
-> Group (cost=32452.98..32572.36 rows=23877 width=8)
-> Sort (cost=32452.98..32512.67 rows=23877 width=8)
Sort Key: ra_datacolh
-> Seq Scan on requisicaoanalise (cost=0.00..30716.71 rows=23877 width=8)
Filter: ((ra_datacolh >= '2003-06-01'::date) AND (ra_datacolh < '2003-07-01'::date) AND (isactive
=0))
--
Pedro Miguel G. Alves pmalves@think.pt
THINK - Tecnologias de Informação www.think.pt
Tel: +351 21 413 46 00 Av. José Gomes Ferreira
Fax: +351 21 413 46 09 nº 13 1495-139 ALGÉS