Re: BRIN indexes vs. SK_SEARCHARRAY (and preprocessing scan keys) - Mailing list pgsql-hackers

From Tomas Vondra
Subject Re: BRIN indexes vs. SK_SEARCHARRAY (and preprocessing scan keys)
Date
Msg-id 57020b2e-d9c9-9bc7-4892-b36d9bb07563@enterprisedb.com
Whole thread Raw
In response to Re: BRIN indexes vs. SK_SEARCHARRAY (and preprocessing scan keys)  (Tomas Vondra <tomas.vondra@enterprisedb.com>)
Responses Re: BRIN indexes vs. SK_SEARCHARRAY (and preprocessing scan keys)
List pgsql-hackers
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
Attachment

pgsql-hackers by date:

Previous
From: Michael Paquier
Date:
Subject: Re: Missing free_var() at end of accum_sum_final()?
Next
From: Amit Kapila
Date:
Subject: Re: Change xl_hash_vacuum_one_page.ntuples from int to uint16