From: Tomas Vondra <tomas.vondra@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Add empty BRIN ranges during CREATE INDEX
Date: Fri, 08 Dec 2023 16:15:56 +0000
Message-ID: <E1rBdW8-009DdC-Hb@gemulon.postgresql.org> (raw)
Add empty BRIN ranges during CREATE INDEX
When building BRIN indexes, the brinbuildCallback only advances to the
next page range when seeing a tuple that doesn't belong to the current
one. This means that the index may end up missing ranges at the end of
the table, if those pages do not contain any indexable tuples.
We tend not to have completely empty pages at the end of a relation, but
this also applies to partial indexes, where the tuples may simply not
match the index predicate. This results in inefficient scans using the
affected BRIN index - without the summaries, the page ranges have to be
read and processed, which consumes I/O and possibly also CPU time.
The existing code already added empty ranges for earlier parts of the
table, this commit makes sure we add them for the ranges at the end of
the table too.
Patch by Matthias van de Meent, with review/improvements by me.
Author: Matthias van de Meent
Reviewed-by: Tomas Vondra
Discussion: https://postgr.es/m/CAEze2WiMsPZg%3DxkvSF_jt4%3D69k6K7gz5B8V2wY3gCGZ%2B1BzCbQ%40mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/dae761a87edae444d11a411f711f1d679bed5941
Modified Files
--------------
contrib/pageinspect/expected/brin.out | 18 +++++
contrib/pageinspect/sql/brin.sql | 17 +++++
src/backend/access/brin/brin.c | 121 ++++++++++++++++++++++++++++++++--
3 files changed, 152 insertions(+), 4 deletions(-)
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-committers@postgresql.org
Cc: tomas.vondra@postgresql.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Add empty BRIN ranges during CREATE INDEX
In-Reply-To: <E1rBdW8-009DdC-Hb@gemulon.postgresql.org>
* 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