Re: Multicolumn hash indexes

On Fri, Sep 29, 2017 at 6:33 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> Now you could also imagine something where we keep a separate set of
> hash buckets for each column and multi-column searches are handled by
> searching each hash table separately and taking the intersection of
> the sets of TIDs found for each column. Not sure that's a good idea,
> but it's sort of like what GIN does.
>

I'd like to note that what GIN does for multicolumn indexes seems quite
artificial thing to me. Logically multicolumn GIN index is the same thing
as multiple singlecolumn GIN indexes. The reason why multicolumn GIN
exists is the fact that in particular case GIN does overlapping of scan
results over multiple attributes more efficient than our executor do.
Particular case is that scans over multiple columns return ordered sets of
TIDs. GIN implements kind of merge join over TIDs while our executor can
only do BitmapAnd in this case.

However, if we imagine that at the moment we develop GIN our executor can
do merge join of TIDs produced by multiple index scans, then we wouldn't
invent multicolumn GIN at all. Or probably we would invent very different
kind of multicolumn GIN.

I mean that multicolumn GIN was a good solution of particular problem from
practical side of view. It allowed to implement very efficient algorithms
with minimal changes in planner/executor infrastructure. However,
multicolumn GIN doesn't look like a good design pattern we would like to
repeat.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company