Horton

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

Tomas Vondra <tomas.vondra@enterprisedb.com>

View thread View on postgr.es
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2023-02-17T02:50:54Z
Lists: pgsql-hackers

Attachments

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

Commits

  1. Improve BRIN minmax-multi opclass test coverage

  2. Introduce bloom_filter_size for BRIN bloom opclass

  3. Minor cleanups in the BRIN code

  4. Support GiST index support functions that want to cache data across calls.