Re: Multicolumn hash indexes

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, Sep 26, 2017 at 7:18 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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.

No, that's merely stating where the goalposts stand, and where they have
stood for the past twenty-odd years. You might imagine they are somewhere
else, but you'd be mistaken.

There is a facility in the planner to require a condition for the first
column of an index before considering an indexscan plan. We could perhaps
extend that to require a condition for each column of the index, though
I'm not sure how much work is involved directly in that. The bigger
picture here though is that it puts a premium on *not* throwing away
"unnecessary" qual conditions, which is directly antithetical to a bunch
of other planner goals.

User: Why won't the planner use my multicolumn hash index?
I have query conditions constraining all the columns!
Us: Well, one of your conditions was discarded because it was
constant-true after constant simplification, or redundant with
a partition qual or CHECK constraint, or implied by an index
predicate, or treated as a join condition instead of a
restriction condition, or absorbed into an equivalence class
and then the planner chose to emit some other equivalence
condition instead, or possibly two or three other things.
User: WAAAAH!

There are also some related problems concerning how to make index entries
for tuples that have some but not all of the indexed columns being NULL.
Maybe that goes away if you're willing to demand strict equality
constraints for all columns, but I'm not completely convinced offhand.
(See the amoptionalkey discussion in the index AM API spec for some
context.)

I agree that doing a half-baked job of this is probably within reach.
I'm uncertain about what it would take to bake it fully.

regards, tom lane