Re: Index usage - Mailing list pgsql-general

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)
(actualtime=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@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