Re: Index usage and wrong cost analisys - Mailing list pgsql-general

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)
(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=9248loops=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@postgresql.org
>
> --
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html

--
Martijn van Oosterhout   <kleptog@svana.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