Re: Bool btree_gin index not chosen on equality contraint, but on greater/lower?

Patric Bechtel <patric(dot)bechtel(at)gmail(dot)com> writes:
> Tom Lane schrieb am 14.02.2016 um 17:51:
>> I think your problem is that the planner won't apply match_boolean_index_clause() or
>> expand_boolean_index_clause(), because it has a hard-wired idea of which index opclasses could
>> possibly benefit from that, cf IsBooleanOpfamily().

> If someone might give me a hint where to look, I'd be grateful.

In principle, instead of using the hard-wired IsBooleanOpfamily test,
we could check "op_in_opfamily(BooleanEqualOperator, opfamily)" to see
whether those transforms would produce something that works with the
index. The reason I didn't do it that way is that those tests are made
for every WHERE clause the planner ever considers, so it seemed like
adding a syscache lookup that is usually going to accomplish nothing
would be pretty annoying overhead. The trick to getting an acceptable
patch here would be to figure out how to arrange things to not cost
much when the optimization doesn't apply.

One idea worth exploring is to redefine IsBooleanOpfamily to inspect
the index's AM OID as well as the opfamily, and code it along the
lines of

((relam) == BTREE_AM_OID ? (opfamily) == BOOL_BTREE_FAM_OID :
(relam) == HASH_AM_OID ? (opfamily) == BOOL_HASH_FAM_OID :
op_in_opfamily(BooleanEqualOperator, opfamily))

so that the extra syscache lookup doesn't have to happen at all for
btree and hash indexes. But I doubt that moves the needle far enough.

Another idea is to try to rearrange match_clause_to_indexcol and
expand_indexqual_conditions so that IsBooleanOpfamily() doesn't get hit
quite so easily. I don't remember at the moment exactly why that's the
first test rather than something further down. Probably at least part of
the reason is an assumption that IsBooleanOpfamily() is cheap. It's
possible that match_boolean_index_clause() is actually cheaper than the
full-fledged version of IsBooleanOpfamily() and so the order of those
tests should be reversed; moving them down to after other tests might also
be appropriate.

Another line of thought is to try to cache the result of the
IsBooleanOpfamily() test in the IndexOptInfo structs so it doesn't
have to get done so many times. But that would be more invasive
and I'm not sure that it helps in simple cases.

regards, tom lane