Re: BRIN indexes vs. SK_SEARCHARRAY (and preprocessing scan keys)

cfbot identified a couple issues in the pathes:

1) not handling NULLs correctly (or rather at all). There was a FIXME,
so I took this as a sign it's time to finally address that.

2) minmax-multi did not fully adopt the preprocessed values in the
second part of the _consistent function

The patches also add a bunch of regression tests to improve coverage.

While adding those, I ran into an interesting behavior with BRIN bloom
indexes. If you have such index on a bigint column, then this won't use
the index:

SELECT * FROM t WHERE b = 82;

unless you cast the constant to bigint like this:

SELECT * FROM t WHERE b = 82::bigint;

I vaguely remember dealing with this while working on the bloom indexes,
and concluding this is OK. But what's interesting is that with multiple
values in the IN clause it works and this will use the index:

SELECT * FROM t WHERE b IN (82, 83);

That's a bit surprising.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company