Re: Index Scans become Seq Scans after VACUUM ANALYSE

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