|
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). Thanks, Chris. |
