From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: John Naylor <john.naylor@2ndquadrant.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: WIP: BRIN multi-range indexes
Date: Wed, 9 Sep 2020 15:40:41 -0300
Message-ID: <20200909184041.GA3829@alvherre.pgsql> (raw)
In-Reply-To: <CACPNZCsgbqh++zat3qvEbHHVXhdcM-0BDeWjFvkM9eTyKvZesw@mail.gmail.com>
On 2020-Sep-09, John Naylor wrote:
> create index on t using brin (a);
> CREATE INDEX
> Time: 1631.452 ms (00:01.631)
> create index on t using brin (a int8_minmax_multi_ops);
> CREATE INDEX
> Time: 6521.026 ms (00:06.521)
It seems strange that the multi-minmax index takes so much longer to
build. I wonder if there's some obvious part of the algorithm that can
be improved?
> The second thing is, with parallel seq scan, the query is faster than
> a BRIN bitmap scan, with this pathological data distribution, but the
> planner won't choose it unless forced to:
>
> set enable_bitmapscan = 'off';
> explain analyze select * from t
> where a between 1923300::int and 1923600::int;
This is probably explained by the fact that you likely have the whole
table in shared buffers, or at least in OS cache. I'm not sure if the
costing should necessarily account for this.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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-hackers@postgresql.org
Cc: alvherre@2ndquadrant.com, john.naylor@2ndquadrant.com, tomas.vondra@2ndquadrant.com
Subject: Re: WIP: BRIN multi-range indexes
In-Reply-To: <20200909184041.GA3829@alvherre.pgsql>
* 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