RE: EXPLAIN ANALYZE of BRIN bitmap index scan with disjunction

[Chris Wilson <chris.wilson@xxxxxxxxxxxxxxxxx>]

Hi Simon,

 

I deliberately included r in the index, to demonstrate the issue that I’m seeing. I know that there is very little locality in this particular, dummy, arbitrary test case. I can try to produce a test case that has some locality, but I expect it to show exactly the same results, i.e. that the BRIN index performs much worse when we try to query on this column as well.

 

Thanks, Chris.

 

From: Simon Riggs <simon@xxxxxxxxxxxxxxx>
Sent: 20 June 2019 16:57
To: Chris Wilson <chris.wilson@xxxxxxxxxxxxxxxxx>
Cc: pgsql-performance@xxxxxxxxxxxxxx
Subject: Re: EXPLAIN ANALYZE of BRIN bitmap index scan with disjunction

 

On Thu, 20 Jun 2019 at 16:13, Chris Wilson <chris.wilson@xxxxxxxxxxxxxxxxx> wrote:

Dear Postgres performance experts,

 

I noticed that when I added a BRIN index to a very large table, attempting to make a particular query faster, it became much slower instead. While trying to understand this, I noticed that the actual number of rows in the EXPLAIN ANALYZE output was much higher than I expected. I was able to produce a repeatable test case for this. I’m not sure if this is actually a bug, or simply that the “number of rows” means something different than I expected.

 

This reproducible test case is not especially slow, because I wanted to make it easy and fast to run and understand. Right now I’d just like to understand why it behaves this way.

 

The SQL is to create the test case is:

 

drop table brin_test;

create table brin_test AS SELECT generate_series as id, generate_series % 100 as r from generate_series(1,100000);

create index idx_brin_test_brin on brin_test using brin (id, r) with (pages_per_range = 32);

 

You've created the index on (id,r) rather than just (id)

 

vacuum analyze brin_test;

 

And here are two queries to compare:

 

explain analyze select * from brin_test where id >= 90000;

explain analyze select * from brin_test where id >= 90000 and r in (1,3);

 

With the following results:

 

testing=# explain analyze select * from brin_test where id >= 90000;

                                                           QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------

Bitmap Heap Scan on brin_test  (cost=8.55..630.13 rows=10146 width=8) (actual time=0.474..1.796 rows=10001 loops=1)

   Recheck Cond: (id >= 90000)

   Rows Removed by Index Recheck: 3215

   Heap Blocks: lossy=59

   ->  Bitmap Index Scan on idx_brin_test_brin  (cost=0.00..6.02 rows=14286 width=0) (actual time=0.026..0.026 rows=640 loops=1)

         Index Cond: (id >= 90000)

Planning Time: 0.155 ms

Execution Time: 2.133 ms

(8 rows)

 

testing=# explain analyze select * from brin_test where id >= 90000 and r in (1,3);

                                                           QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------

Bitmap Heap Scan on brin_test  (cost=6.06..556.21 rows=219 width=8) (actual time=6.101..23.927 rows=200 loops=1)

   Recheck Cond: ((id >= 90000) AND (r = ANY ('{1,3}'::integer[])))

   Rows Removed by Index Recheck: 13016

   Heap Blocks: lossy=59

   ->  Bitmap Index Scan on idx_brin_test_brin  (cost=0.00..6.01 rows=7143 width=0) (actual time=0.038..0.038 rows=1280 loops=1)

         Index Cond: ((id >= 90000) AND (r = ANY ('{1,3}'::integer[])))

Planning Time: 0.071 ms

Execution Time: 23.954 ms

(8 rows)

 

Note that introducing a disjunction (set of possible values) into the query doubles the number of actual rows returned, and increases the number removed by the index recheck.

 

Strange, yes.

 

It looks to me as though perhaps the BRIN index does not completely support queries with a set of possible values, and executes the query multiple times (try adding more values of R to see what I mean).

 

That doesn't appear to be happening.

 

The execution time also increases massively.

 

Could anyone help me to understand what’s going on here, and whether there’s a bug or limitation of BRIN indexes? If it’s a limitation, then the query planner does not seem to account for it, and chooses this plan even when it’s a bad one (much worse than removing result rows using a filter).

 

 The second column changes the way the index is defined. It appears there is very little locality for the r column, so try removing it.

 

--

Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Solutions for the Enterprise