Re: Index Scans become Seq Scans after VACUUM ANALYSE

Bruce Momjian wrote:
>
> 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.

I think you are missing a huge point, people are confused by the operation of
PostgreSQL. You admit that there are a lot of questions about this topic. This
means that something is happening which is non-intuitive. Bruce, you are an
expert in PostgreSQL, but most people who use it are not. The unexpected
behavior is just that, unexpected, or a surprise.

Business people, accountants, and engineers do not like surprises. PostgreSQL's
behavior on index usage is totally confusing. If I can paraphase correctly,
PostgreSQL wants to have a good reason to use an index. Most people expect a
database to have an undeniable reason NOT to use an index. I would also say, if
a DBA created an index, there is a strong indication that there is a need for
one! (DBA knowledge vs statistics)

That is the difference, in another post Tom said he could not get excited about
10.9 second execution time over a 7.96 execution time. Damn!!! I would. That is
wrong.

I have bitched about the index stuff for a while, and always have bumped up
against this problem. If I can sway anyone's opinion, I would say, unless
(using Tom's words) a "factor of 2" planner difference against, I would use an
index. Rather than needing clear evidence to use an index, I would say you need
clear evidence not too.