Re: Multicolumn hash indexes

On Tue, Sep 26, 2017 at 7:18 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Tomasz Ostrowski <tometzky+pg(at)ato(dot)waw(dot)pl> writes:
>> 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?
>
> It's not simple, particularly not if you wish that the index would support
> queries specifying conditions for just a subset of the indexed columns
> (an assumption that's buried pretty deeply in the planner, for one thing).
> Then you couldn't compute the hash.

Whoa, that seems like moving the goalposts. Somebody could design a
hash index that was intended to answer such queries, but it's not the
one we've got. I think we should just aim to support equality queries
on all columns. That seems like a fairly straightforward
generalization of what we've already got.

You'd need to adjust a bunch of things, like _hash_datum2hashkey and
_hash_datum2hashkey_type but that doesn't necessarily seem like it
would be super-difficult. One problem is that the metapage currently
stores the OID of the hashproc used for the index, and there's no
place there to store more than one. But since that's only for
forensics anyway, we could store InvalidOid for a multi-column index,
or the value for the first column, or whatever.

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