Re: Index usage and wrong cost analisys

Look through the docs. By altering the values of random_page_cost,
effect_cache_size and cpu_tuple_cost you can make the estimates approximate
real life better.

On Tue, Aug 26, 2003 at 12:32:23PM +0100, Pedro Alves wrote:
>
>
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato