On Tue, Oct 29, 2013 at 02:53:37PM +0000, Leonardo Francalanci wrote:
> > Before getting too excited about some new academic index type, it's worth
> > noting the sad state in which hash indexes have languished for years.
> > Nobody's bothered to add WAL support, let alone do any other real work
> > on them. The non-btree index types that have been getting love are the
> > ones that offer the ability to index queries that btree can't. I think
> > a new index type whose only benefit is the claim to be faster in a narrow
> > use-case is likely to end up like hash, not getting used enough to be
> > properly maintained.
> > regards, tom lane
>
> Aren't hash indexes in a poor state because they are not faster than btree in every condition?
>
Hi Leonardo,
If there was ONE perfect index, better in every condition, postgres would be
using it. As in everything else, each type has its strengths and weaknesses.
The hash index allows equality searches for very large key lengths using a
relatively very small index size. As has been mentioned before, we still do
not have WAL logging for hash indexes. But even so, for I/O bound systems
hash indexes are twice as fast for searches than the btree equivalent.
Regards,
Ken