Change the index access method in PostgreSQL?

Question

I have a bunch of PostgreSQL (v14 or 15) indexes over a few dozen tables that use B-Trees for their access method. We want to change them to hashes as we don't do inequality operations on them, and hash is about 30% faster on average for equality operations from the data I've seen.

I see in the PostgreSQL docs that indexes can be altered and reindexed, but there doesn't seem to be a method for changing the access method. Is that possible and I'm just missing on how to perform it? Or do I need to drop and recreate the indexes with the appropriate indexing method?

Thanks.

No, not possible, you have to create a new index and drop the old one. Performance depends on your queries, not just the type of index. A claim about 30% performance improvements is a little premature. — Frank Heikens
– Frank Heikens, Commented Dec 30, 2024 at 19:46
That's unfortunate, but kinda figured. Most of our queries are recursive table joins for graph operations or basic selects on unordered data, so I can't imagine the performance would be anything but better. — T. N. Williams
– T. N. Williams, Commented Dec 30, 2024 at 21:10
Your imagination and reality, are two different things. Check the query plans first, to avoid surprises. For example you disable the option for index-only scans, no more multi column indexes, no min-max aggregates and no ordering. I haven't used a single hash index the last 20 years, there was never a use case. Again, check your query plans before moving on. — Frank Heikens
– Frank Heikens, Commented Dec 30, 2024 at 21:19
Did a test on a small table with 6.2 million records and replaced the btree index by a hash index: The btree index was 125MB in size, the hash index is 167MB in size. And no significant change in performance, both were equally fast on a fast machine. — Frank Heikens
– Frank Heikens, Commented Dec 30, 2024 at 22:08
I didn't realize that hash indexes couldn't be used for multi-column indexes - that wasn't mentioned in the access method documentation (but was the multi-column index documentation 🙃). We primarily use multi-column indexes, so that throws a spanner in that idea. Thank you for bringing that to my attention. 😀 — T. N. Williams
– T. N. Williams, Commented Dec 30, 2024 at 22:53

Nikhil · Accepted Answer · 2024-12-31 06:40:27Z

1

Drop the Btree index using:

DROP INDEX indexname;

Create the hash index using :

CREATE INDEX column_name_hash_idx ON tablename USING HASH (columnname);

The acccess method will pick up the hash index retrival only when '=' is used because hash indexes are optimised for equality comparisons .

answered Dec 31, 2024 at 6:40

Nikhil

3,9785 gold badges20 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Change the index access method in PostgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related