Re: Multicolumn hash indexes

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Multicolumn hash indexes
Date: 2017-09-26 23:21:43
Message-ID: CA+TgmoZnzqZ35c7+ZaqhwTU8HKeKwCpRpgNJEHbRxO2t=7=S_g@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Tue, Sep 26, 2017 at 5:41 PM, Tomasz Ostrowski
<tometzky+pg(at)ato(dot)waw(dot)pl> wrote:
> 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?

I was just thinking the same thing today. I think it is a great idea,
but I don't have time to do the work myself at the moment. I'd be
happy to help review, though.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2017-09-26 23:28:27 Re: User-perspective knowledge about wait events
Previous Message Tom Lane 2017-09-26 23:18:41 Re: Multicolumn hash indexes