Re: Index usage and wrong cost analisys

One more thing I just noticed. Right after making a vacuum analyze on
the table, I saw the following:

Seq scan:

- Cost: 10484
- Timing: 624ms

Index scan (with enable_seqscan = false):

- Cost: 10628
- Timing: 41ms

In production state the query goes up to a minute long (I ran this in a
test database) and it takes less than a second using indexes. What can be
causing this?

Is it safe to turn enable_seqscan = false in production environment?

Thanks

On Tue, Aug 26, 2003 at 09:59:35AM +0100, Pedro Alves wrote:
>
> 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.
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

--
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