Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is the time complexity of hash index in PostgreSQL?

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 ?

like image 585
user3329081 Avatar asked Nov 01 '22 09:11

user3329081


1 Answers

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.

like image 89
Frank Heikens Avatar answered Nov 15 '22 05:11

Frank Heikens