Re: Multicolumn hash indexes

On Tue, Sep 26, 2017 at 11:32:52PM -0400, Tom Lane wrote:
> 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.

To stretch this analogy too far, what other things could be built out
of the bread this bakes? I'm guessing that at least non-hash
multicolumn indexes would benefit. Expressional indexes, maybe?

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate