I guess the creating hash index is O(n), while use it to access data also get O(n), so,it's better than b-tree index ?
1 Answer
From the manual:
Note: Testing has shown PostgreSQL's hash indexes to perform no better than B-tree indexes, and the index size and build time for hash indexes is much worse. Furthermore, hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with REINDEX after a database crash. For these reasons, hash index use is presently discouraged.
Later versions of the manual don't mention the performance issues, only focus on problems with replication.
It looks like it doesn't make sense to use a hash index.
5 Comments
user3329081
While in my application, there is no wal-logged or rebuilt problem. I care more about the complexity. So, could you suggest is my theoretical analysis correct ? @Frank Heikens
Frank Heikens
You have the same problem, you use the database and it's indexes. These hash indexes can't be trusted after a database crash (power outage or something like that). And why use some index that isn't any better then a btree index? You don't win anything but you do loose reliability. Doesn't make sense to me.
user3329081
well, I mainly focus on it's performance analysis as this is a research project, reliability is not the first priority for me ... and, I don't have need to consider crash ...
Frank Heikens
If you're worried about performance, stay away from hash indexes: "Testing has shown PostgreSQL's hash indexes to perform no better than B-tree indexes, and the index size and build time for hash indexes is much worse."
Amin Shojaei
As you mentioned, that note is deleted in new version's document. I am wondered if the performance issue still exists or not? @FrankHeikens