PostgreSQL's Hash Indexes Are Now Cool
rhaas.blogspot.com
PostgreSQL's Hash Indexes Are Now Cool
1–10 of 39 posts
Re: PostgreSQL's Hash Indexes Are Now Cool
#2Wasn't sure what a hash index was vs. btree
Short version - hash indexes are faster in PG11, but they only apply to "where = foobar" queries, giving a 0(1) time. Btree indexes have O(logn)
But hash indexes can't be applied to range clauses, like "where SO post:
Re: PostgreSQL's Hash Indexes Are Now Cool
#3Wasn't sure what a hash index was vs. btree Short version - hash indexes are faster in PG11, but they only apply to "where = foobar" queries, giving a 0(1) time. Btree indexes have O(logn) But hash indexes can't be applied to range clauses, like "where SO post: https://stackoverflow.com/a/398921
> We can see here that the hash index performs better than the btree index and the performance difference is in the range of 10 to 22%. In some other workloads we have seen a better performance like with hash index on varchar columns and even in the community, it has been reported that there is performance improvement in the range of 40-60% when hash indexes are used for unique index columns.
the link is from the post http://amitkapila16.blogspot.com/2017/03/hash-indexes-are-fa...
Re: PostgreSQL's Hash Indexes Are Now Cool
#4Are there any plans for allowing hash indexes in uniqueness constraints such as the ones created for primary keys? It seems like a good fit for an index that is specialized for equality checks.
Re: PostgreSQL's Hash Indexes Are Now Cool
#5Are there any plans for allowing hash indexes in uniqueness constraints such as the ones created for primary keys? It seems like a good fit for an index that is specialized for equality checks.
Maybe it would be easier just to remove "A foreign key must reference columns that either are a primary key or form a unique constraint" restriction, stating that any unique index is enough.
Re: PostgreSQL's Hash Indexes Are Now Cool
#6> A report from a tester who goes by "AP" in July tipped us off to the need for a few further tweaks. AP found that trying to insert 2 billion rows into a newly-created hash index was causing an error.
This is what I call trying things "at Indian scale" :D
Re: PostgreSQL's Hash Indexes Are Now Cool
#7Are there any plans for allowing hash indexes in uniqueness constraints such as the ones created for primary keys? It seems like a good fit for an index that is specialized for equality checks.
Maybe it would be easier just to remove "A foreign key must reference columns that either are a primary key or form a unique constraint" restriction, stating that any unique index is enough.
> Maybe it would be easier just to remove "A foreign key must reference columns that either are a primary key or form a unique constraint" restriction, stating that any unique index is enough.
The problem is that only btree indexes support uniqueness atm. That's the relevant unsupported features, not the ability to have constraints (which essentially just requires uniqueness support of the underlying index):
postgres[27716][1]# SELECT amname, pg_indexam_has_property(oid, 'can_unique') FROM pg_am;
┌────────┬─────────────────────────┐
│ amname │ pg_indexam_has_property │
├────────┼─────────────────────────┤
│ btree │ t │
│ hash │ f │
│ gist │ f │
│ gin │ f │
│ spgist │ f │
│ brin │ f │
└────────┴─────────────────────────┘
(6 rows)
Re: PostgreSQL's Hash Indexes Are Now Cool
#8Earlier quoted context omitted.
Maybe it would be easier just to remove "A foreign key must reference columns that either are a primary key or form a unique constraint" restriction, stating that any unique index is enough.
> Maybe it would be easier just to remove "A foreign key must reference columns that either are a primary key or form a unique constraint" restriction, stating that any unique index is enough. The problem is that only btree indexes support uniqueness atm. That's the relevant unsupported features, not the ability to have constraints (which essentially just requires uniqueness support of the underlying index): postgres…
Is the hash guaranteed to be unique though? There is always a possibility of a hash collision
Re: PostgreSQL's Hash Indexes Are Now Cool
#9Any good articles that go into detail on when to use hash indexes in PG 10?
Re: PostgreSQL's Hash Indexes Are Now Cool
#10Earlier quoted context omitted.
> Maybe it would be easier just to remove "A foreign key must reference columns that either are a primary key or form a unique constraint" restriction, stating that any unique index is enough. The problem is that only btree indexes support uniqueness atm. That's the relevant unsupported features, not the ability to have constraints (which essentially just requires uniqueness support of the underlying index): postgres…
Is the hash guaranteed to be unique though? There is always a possibility of a hash collision
> Is the hash guaranteed to be unique though? There is always a possibility of a hash collision
I mean my point is that hashindexes do not support uniqueness right now. But hash collisions wouldn't be a problem there. Does mainly require some tricky concurrency aware code (consider cases lik ewhere one transaction just deleted a conflicting row but is still in progress, and a new value like that is inserted, etc).