Tom recently said, when talking about allowing the user (in this case me)
from passing a hash table size to "create index:"
"but that doesn't mean I want to make the user deal with it."
I started thinking about this and, maybe I'm old fashioned, but I would
like the ability to deal with it. So much software these days does things
in an automatic fashion, and too often you are left saying "stop, no do it
that way, damn!" tossing hands up in frustration. Come on, be honest, we
all fight this.
I think the holy grail of completly automatic tuning/functionality is a
lofty goal and a good one to seek, but the harsh reality is that there are
many times when the statistics aren't sufficient and the very broad
audience to which PostgreSQL is targeted clouds various specific use
cases.
I have been on the end of these problems numerous times in the almost 10
years of using PostgreSQL. While I still believe that PostgreSQL is, by
far, one of the best and most usable databases out there, there are times
I just get frustrated.
Being able to assign "hints" to queries may be able to allow DBAs to tune
queries in tables who's characteristics are misrepresented by the
statistics in ANALYZE.
Being able to pass a hash table size to a hash CREATE INDEX statement,
may make hash table faster.
Whould a "hinting" syntax help this out? I don't know, but I do know that
just about every non-trivial project for which I have used PostgreSQL, I
have run into issues where I've had to manually alter statistics source
code, or enable/disable scan types to work around situations where PG just
didn't understand the nature of the problem.
Also, isn't "SET enable_seqscan=FALSE" just another more clumsy way of
issuing a hint to the planner?
CREATE INDEX mytablehash ON mytable USING hash /* +HASH_SIZE(10000000) */
SELECT * from table1, table2 where table1.realm = table2.realm and
table1.name = 'foo' /* +USE_INDEX(tabel1.realm) +USE_HASH(table1.realm,
table2.realm) */
