We have had several threads about index usage, specifically when PostgreSQL has
the choice of using one or not.
There seems to be a few points of view:
(1) The planner and statistics need to improve, so that erroneously using an
index (or not) happens less frequently or not at all.
(2) Use programmatic hints which allow coders specify which indexes are used
during a query. (ala Oracle)
(3) It is pretty much OK as-is, just use enable_seqscan=false in the query.
My point of view is about this subject is one from personal experience. I had a
database on which PostgreSQL would always (erroneously) choose not to use an
index. Are my experiences typical? Probably not, but are experiences like it
very common? I don't know, but we see a number "Why won't PostgreSQL use my
index" messages to at least conclude that it happens every now and then. In my
experience, when it happens, it is very frustrating.
I think statement (1) is a good idea, but I think it is optimistic to expect
that a statistical analysis of a table will contain enough information for all
possible cases.
Statement (2) would allow the flexibility needed, but as was pointed out, the
hints may become wrong over time as characteristics of the various change.
Statement (3) is not good enough because disabling sequential scans affect
whole queries and sub-queries which would correctly not use an index would be
forced to do so.
My personal preference is that some more specific mechanism than enable_seqscan
be provided for the DBA to assure an index is used. Working on the statistics
and the planner is fine, but I suspect there will always be a strong argument
for manual override in the exceptional cases where it will be needed.
What do you all think? What would be a good plan of attack?