Re: Index usage

First of all tkx for the answer, Dennis.

The vacuum analyze is run on daily basis, so that cannot be the point.

Is there any way to force the planner to use the index?

Bellow is the explain analyze of the querys. Indeed, the second range has
more rows (9105 / 21503), but is this SO big that the planner cannot
handle?

This is running in a dedicated machine with 512Mb ram. Is there any
configuration parameter so that I can increase the "index to seq turn point"? :)

Thanks

OK __________________________________

explain ANALYZE select count(1) from requisicaoanalise where
(ra_datacolh::date >= '2003-4-01'::date and ra_datacolh::date <
'2003-5-1'::date) and isactive=0;

Aggregate (cost=10660.84..10660.84 rows=1 width=0) (actual
time=172.41..172.41 rows=1 loops=1)
-> Index Scan using requisicaoanalise_datacolh on requisicaoanalise (cost=0.00..10654.06 rows=2711 width=0) (actual time=0.13..145.50 rows=9105 loops=1)
Index Cond: ((ra_datacolh >= '2003-04-01'::date) AND (ra_datacolh
< '2003-05-01'::date))
Filter: (isactive = 0)
Total runtime: 172.62 msec
(5 rows)

NOK __________________________________

explain ANALYZE select count(1) from requisicaoanalise where (ra_datacolh::date >= '2003-6-01'::date and ra_datacolh::date < '2003-7-1'::date) and isactive=0;

Aggregate (cost=31019.00..31019.00 rows=1 width=0) (actual time=43252.40..43252.40 rows=1 loops=1)
-> Seq Scan on requisicaoanalise (cost=0.00..30965.24 rows=21503 width=0) (actual time=8.43..43224.01 rows=9248 loops=1)
Filter: ((ra_datacolh >= '2003-06-01'::date) AND (ra_datacolh < '2003-07-01'::date) AND (isactive = 0))
Total runtime: 43252.57 msec
(4 rows)

On Tue, Aug 26, 2003 at 08:37:08AM +0200, Dennis Björklund wrote:
> On Mon, 25 Aug 2003, Pedro Alves wrote:
>
> > 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?
>
> Run "vacuum analyze". The planner seems to think that one of the queries
> returns 313 rows while the other returns 2388 rows.
>
> To me that looks like the statistics need to be updated using vacuum
> analyze.
>
> Also, explain analyze gives a little more information and is better to
> run then just explain.
>

> --
> /Dennis

--
Pedro Miguel G. Alves pmalves(at)think(dot)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