From: Justin Pryzby <pryzby@telsasoft.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Chris Wilson <chris.wilson@cantabcapital.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: EXPLAIN ANALYZE of BRIN bitmap index scan with disjunction
Date: Thu, 20 Jun 2019 11:30:44 -0500
Message-ID: <20190620163044.GU16019@telsasoft.com> (raw)
In-Reply-To: <CANP8+j++Vhbwh1V0hxrA_jeMzxuzzFh833-ere-iuob0KDE=Wg@mail.gmail.com>
References: <BCCA73C2165E8947A2E786EC482564DE013E3562DA@CCPMAILDAG03.cantab.local>
<CANP8+jKwFR2=Jjb30XQxVZaDo4knP4XbmS26sZVZ8yW9iaFV+Q@mail.gmail.com>
<BCCA73C2165E8947A2E786EC482564DE013E35647C@CCPMAILDAG03.cantab.local>
<CANP8+j++Vhbwh1V0hxrA_jeMzxuzzFh833-ere-iuob0KDE=Wg@mail.gmail.com>
On Thu, Jun 20, 2019 at 05:18:33PM +0100, Simon Riggs wrote:
> On Thu, 20 Jun 2019 at 17:01, Chris Wilson <chris.wilson@cantabcapital.com>
> wrote:
>
>
> > 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.
> >
>
> I'm suggesting that adding the second column to the index is the source of
> your problem, not adding the column to the query.
But it *is* odd that the index returns more rows with a strictly tighter
conditions, right ?
Note, it's not an issue of rowcount estimate being confused by redundant
conditions, but real rowcount, and it returns more rows even when the
conditions are duplicative. Compare:
postgres=# explain analyze select * from brin_test where id >= 90000 and r in (1);
...
-> Bitmap Index Scan on brin_test_id_r_idx (cost=0.00..12.03 rows=28125 width=0) (actual time=0.136..0.137 rows=37120 loops=1)
Index Cond: ((id >= 90000) AND (r = 1))
postgres=# explain analyze select * from brin_test where id >= 90000 and r in (1,1);
...
-> Bitmap Index Scan on brin_test_id_r_idx (cost=0.00..12.03 rows=28125 width=0) (actual time=0.263..0.263 rows=74240 loops=1)
Index Cond: ((id >= 90000) AND (r = ANY ('{1,1}'::integer[])))
postgres=# explain analyze select * from brin_test where id >= 90000 and r in (1,1,1);
...
-> Bitmap Index Scan on brin_test_id_r_idx (cost=0.00..12.03 rows=28125 width=0) (actual time=0.387..0.387 rows=111360 loops=1)
Index Cond: ((id >= 90000) AND (r = ANY ('{1,1,1}'::integer[])))
Note, the docs say:
https://www.postgresql.org/docs/devel/indexes-multicolumn.html
|A multicolumn BRIN index can be used with query conditions that involve any
|subset of the index's columns. Like GIN and unlike B-tree or GiST, index search
|effectiveness is the same regardless of which index column(s) the query
|conditions use. The only reason to have multiple BRIN indexes instead of one
|multicolumn BRIN index on a single table is to have a different pages_per_range
|storage parameter.
Justin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, simon@2ndquadrant.com, chris.wilson@cantabcapital.com
Subject: Re: EXPLAIN ANALYZE of BRIN bitmap index scan with disjunction
In-Reply-To: <20190620163044.GU16019@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox