Hi,
While working on the BRIN SK_SEARCHARRAY patch I noticed a silly bug in
handling clauses on multi-column BRIN indexes, introduced in PG13.
Consider a simple table with two columns (a,b) and a multi-columns BRIN
index on them:
create table t (a int, b int);
insert into t
select
mod(i,10000) + 100 * random(),
mod(i,10000) + 100 * random()
from generate_series(1,1000000) s(i);
create index on t using brin(a int4_minmax_ops, b int4_minmax_ops)
with (pages_per_range=1);
Let's run a query with condition on "a":
select * from t where a = 500;
QUERY PLAN
-----------------------------------------------------------------
Bitmap Heap Scan on t (actual rows=97 loops=1)
Recheck Cond: (a = 500)
Rows Removed by Index Recheck: 53189
Heap Blocks: lossy=236
-> Bitmap Index Scan on t_a_b_idx (actual rows=2360 loops=1)
Index Cond: (a = 500)
Planning Time: 0.075 ms
Execution Time: 8.263 ms
(8 rows)
Now let's add another condition on b:
select * from t where a = 500 and b < 800;
QUERY PLAN
-----------------------------------------------------------------
Bitmap Heap Scan on t (actual rows=97 loops=1)
Recheck Cond: ((a = 500) AND (b < 800))
Rows Removed by Index Recheck: 101101
Heap Blocks: lossy=448
-> Bitmap Index Scan on t_a_b_idx (actual rows=4480 loops=1)
Index Cond: ((a = 500) AND (b < 800))
Planning Time: 0.085 ms
Execution Time: 14.989 ms
(8 rows)
Well, that's wrong. With one condition we accessed 236 pages, and with
additional condition - which should reduce the number of heap pages - we
accessed 448 pages.
The problem is in bringetbitmap(), which failed to combine the results
from consistent function correctly (and also does not abort early).
Here's a patch for that, I'll push it shortly after a bit more testing.
regard
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
