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);
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.
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).
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).
