Thread

Bug in processing conditions on multi-column BRIN indexes

Tomas Vondra <tomas.vondra@enterprisedb.com> — 2023-02-16T02:04:16Z

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