Multicolumn hash indexes

From: Tomasz Ostrowski <tometzky+pg(at)ato(dot)waw(dot)pl>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Multicolumn hash indexes
Date: 2017-09-26 21:41:15
Message-ID: 252abaa6-4d71-17bd-bcaf-d0a2b0537bdc@ato.waw.pl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi.

I've noticed that hash indexes can't currently (in PG10) be multicolumn.
Are they technically hard to implement or just nobody took such a feature?

I think multicolumn hash indexes should help pretty significantly with
queries like:
- where username=? and user_post_id=?
- where client_id=? and period=? and invoice_number=?
etc.

I imagine that calculating a multicolumn hash should be pretty
straightforward to implement - after hashing bytes of first column just
keep going and update the hash state with bytes of a second and
subsequent columns. And it should allow for faster (O(1), less IO) and
much smaller (better cached, less IO again) multicolumn indexes. Also in
PG10 hash indexes are WAL-logged and therefore much easier to work with.
What do you think?

--
Tomasz "Tometzky" Ostrowski

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Stephen Frost 2017-09-26 21:45:48 Re: [BUGS] BUG #14825: enum type: unsafe use?
Previous Message Tom Lane 2017-09-26 21:32:15 Re: [HACKERS] BUG #14825: enum type: unsafe use?