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 [EMAIL PROTECTED] > > -- > Pedro Miguel G. Alves [EMAIL PROTECTED] > 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 <[EMAIL PROTECTED]> 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
pgp00000.pgp
Description: PGP signature
