On 2014-11-01 10:18:03 -0700, Josh Berkus wrote: > On 10/31/2014 03:07 PM, Tom Lane wrote: > > I don't care one way or the other about the money type, but I will defend > > hash indexes, especially seeing that we've already added a pretty > > in-your-face warning as of 9.5: > > > > regression=# create table foo(f1 int); > > CREATE TABLE > > regression=# create index on foo using hash (f1); > > WARNING: hash indexes are not WAL-logged and their use is discouraged > > CREATE INDEX > > Yes, and I'm arguing that is the wrong decision. If hash indexes are > "discouraged", then they shouldn't be in core in the first place.
Last time we discussed it there were people (IIRC Andrew was one of them) commenting that they use hash indexes *precisely* because they're not WAL logged and that they can live with the dangers that creates. I don't think that's sufficient justification for introducing the feature at all. But it's nothing new that removing a feature has to fit quite different criteria than adding one.
So, by that argument we could remove hash indexes once we have unlogged indexes on logged tables. But then there's no need to remove them anymore...
I would object to removing hash indexes as long as the alternative way to index oversized value is to write all my SQL to look like:
select count(*) from foo where substr(x,1,2700)=substr($1,1,2700) and x=$1
Now, if the planner were smart enough to realize that x=$1 implies substr(x,1,2700)=substr($1,1,2700), that might be a different matter. But it is not.
Or, if there were a way to create a view on foo which would do this implication automatically, but again as far as I know there is not a way to do that either.
