Re: Index Scans become Seq Scans after VACUUM ANALYSE

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(at)apartia(dot)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(at)postgresql(dot)org
>

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