Re: Index Scans become Seq Scans after VACUUM ANALYSE - Mailing list pgsql-hackers

Let me add people's expections of the optimizer and the "it isn't using
the index" questions are getting very old.  I have beefed up the FAQ
item on this a month ago, but that hasn't reduced the number of
questions.  I almost want to require people to read a specific FAQ item
4.8 before we will reply to anything.

Maybe that FAQ item needs more info.  Tom can't be running around trying
to check all these optimizer reports when >90% are just people not
understanding the basics of optimization or query performance.

Maybe we need an optimizer FAQ that will answer the basic questions for
people.

---------------------------------------------------------------------------

Tom Lane wrote:
> Louis-David Mitterrand <vindex@apartia.org> writes:
> > While trying to optimise a query I found that running VACUUM ANALYSE
> > changed all the Index Scans to Seq Scans and that the only way to revert
> > to Index Scans was the add "enable_seqscan = 0" in postgresql.conf.
> >> 
> >> EXPLAIN ANALYZE output would be more interesting than just EXPLAIN.
> >> Also, what does the pg_stats view show for these tables?
> 
> > Thanks, pg_stats output is rather big so I attached it in a separate
> > file. Here are the EXPLAIN ANALYZE ouputs:
> 
> Tell you the truth, I'm having a real hard time getting excited over
> a bug report that says the planner chose a plan taking 10.90 seconds
> in preference to one taking 7.96 seconds.
> 
> Any time the planner's estimates are within a factor of 2 of reality,
> I figure it's done very well.  The inherent unknowns are so large that
> that really amounts to divination.  We can't expect to choose a perfect
> plan every time --- if we can avoid choosing a truly stupid plan (say,
> one that takes a couple orders of magnitude more time than the best
> possible plan) then we ought to be happy.
> 
> But having said that, it would be interesting to see if adjusting some
> of the planner cost parameters would yield better results in your
> situation.  The coarsest of these is random_page_cost, which is
> presently 4.0 by default.  Although I have done some moderately
> extensive measurements to get that figure, other folks have reported
> that lower numbers like 3.0 or even less seem to suit their platforms
> better.  In general a lower random_page_cost will favor indexscans...
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026