Re: Multicolumn hash indexes

On 09/26/2017 08:11 PM, Robert Haas wrote:
> 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.
>

This would require that the applications that are using the database
knows about the index structure in order to pass down the right columns.

I would say that in most cases that applications doesn't know about
index structures. So, multiple indexes would have to be created (col1),
(col1, col2), (col1, col3), ... which isn't ideal.

Maybe an initial proof-of-concept could store the hash of the first
column (col1) plus the hash of all columns (col1, col2, col3) in the
index, and see what requirements / design decisions would appear from that.

Best regards,
Jesper