RE: EXPLAIN ANALYZE of BRIN bitmap index scan with disjunction - Mailing list pgsql-performance

From Chris Wilson
Subject RE: EXPLAIN ANALYZE of BRIN bitmap index scan with disjunction
Date
Msg-id BCCA73C2165E8947A2E786EC482564DE013E356D91@CCPMAILDAG03.cantab.local
Whole thread Raw
In response to Re: EXPLAIN ANALYZE of BRIN bitmap index scan with disjunction  (Simon Riggs <simon@2ndquadrant.com>)
List pgsql-performance

That makes perfect sense, thanks Simon!

 

Chris.

 

From: Simon Riggs <simon@2ndquadrant.com>
Sent: 21 June 2019 10:17
To: Chris Wilson <chris.wilson@cantabcapital.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: EXPLAIN ANALYZE of BRIN bitmap index scan with disjunction

 

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

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

 

In both cases the index is returning a lossy bitmap of 59 heap blocks. The second query is more restrictive, so the number removed by index recheck is higher. The total of number rows returned plus the number of rows removed by index recheck is the same in both cases.

 

The only weirdness is why the index reports it has returned 640 rows in one query and 1280 in second query. Since a lossy bitmap is returned, that figure can only be an estimate. The estimate differs between queries, but is wrong in both cases. 

 

--

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

pgsql-performance by date:

Previous
From: Simon Riggs
Date:
Subject: Re: EXPLAIN ANALYZE of BRIN bitmap index scan with disjunction
Next
From: Chris Wilson
Date:
Subject: RE: EXPLAIN ANALYZE of BRIN bitmap index scan with disjunction