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