I haven't weighed in on this because 1) I'm not a postgresql developer,
and am firmly of the opinion that they who are doing the work get to
decide how the work gets done (especially when you aren't paying them
for the work), and 2) I don't have any experience as a developer with
hints, and thus don't know any of the pluses or minuses. I do, however,
know my fellow developers. As general rules:
1) If you give developers a feature, they will use it. The implicit
assumption seems to be that if you're given a feature, you've been given
it for a good reason, use it whenever possible. Therefor, any hints
feature *will* be used widely an in "inappropriate" circumstances.
Protestations that this wasn't what the feature was meant for will fall
on deaf ears.
2) Taking away a feature is painfull. Of course the developers will
*say* that they're doing it in a portable way that'll be easy to change
in the future, but we lie like cheap rugs. This is is often just a case
of stupidity and/or ignorance, but even the best developers can get
caught- 99 out of 100 uses of the feature are portable and easy to
update, it's #100 that's a true pain, and #100 was an accident, or a
kludge to get the app out the door under shipping schedule, etc. Taking
away, or breaking, a feature then just becomes a strong disincentive to
upgrade.
3) Developers are often astonishingly bad at predicting what is or is
not good for performance. A good example of this for databases is the
assumption that index scans are always faster than sequential scans.
The plan the programmer thinks they want is often not the plan the
programmer really wants. Especially considering generally the program
has so many other things they're having to deal with (the "it's hard to
remember you're out to drain the swamp when you're up to your ass in
alligators" problem) that we generally don't have the spare brainpower
left over for query optimization. Thus the strong tendancy to want to
adopt simple, rough and ready, mostly kinda true rules (like "index
scans are always faster than sequential scans") about what is or is not
good for performance.
Or, in shorter forms:
1) If you make it convient to use, expect it to be used a lot. If it
shouldn't be used a lot, don't make it convient.
2) Breaking features means that people won't upgrade.
3) Programmers are idiots- design accordingly.
Brian
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
