public inbox for [email protected]  

help / color / mirror / Atom feed

From: Paul Foerster <[email protected]>
To: Pgsql-General List <[email protected]>
Subject: Updating to PostgreSQL 17.5
Date: Sun, 11 May 2025 20:08:14 +0200
Message-ID: <[email protected]> (raw)

Hi,

the release notes for PostgreSQL 17.5 (https://www.postgresql.org/docs/17/release-17-5.html) state:

"Also, if you have any BRIN bloom indexes, it may be advisable to reindex them after updating."

I don't know what exactly that means. So I read about BRIN and BLOOM indexes and learned how to create them using the "USING BRIN..." or "USING BLOOM..." clause. But there is no such thing as "USING BRIN BLOOM" or other variation. After quite some research, I only found that there may be a BRIN and a BLOOM index on the same table, created by two individual CREATE INDEX commands. As I understand, those are the ones referred to in the release notes.

Also, the \di+ command wasn't much of a help in this case. So I came up with the following query which also creates the necessary reindex commands. Please let me know if my query below hits the wanted indexes or not.

I'd be really cool if release notes contained the necessary queries to find the objects in question.

Thanks in advance,
Paul


with
    brin_indexes as (
        select
            e.nspname as schema_name,
            c.relname as table_name,
            b.relname as index_name
        from
            pg_catalog.pg_index as a
            join pg_catalog.pg_class as b on b.oid = a.indexrelid
            join pg_catalog.pg_class as c on c.oid = a.indrelid
            join pg_catalog.pg_am as d on b.relam = d.oid
            join pg_catalog.pg_namespace as e on e.oid = c.relnamespace
        where
            d.amname = 'brin'
    ),
    bloom_indexes as (
        select
            e.nspname as schema_name,
            c.relname as table_name,
            b.relname as index_name
        from
            pg_catalog.pg_index as a
            join pg_catalog.pg_class as b on b.oid = a.indexrelid
            join pg_catalog.pg_class as c on c.oid = a.indrelid
            join pg_catalog.pg_am as d on b.relam = d.oid
            join pg_catalog.pg_namespace as e on e.oid = c.relnamespace
        where
            d.amname = 'bloom'
    )
select
    concat ('reindex index "', schema_name, '"."', index_name, '"; -- brin index') as reindex_cmd
from
    brin_indexes
where
    schema_name in (select schema_name from bloom_indexes) and
    table_name in (select table_name from bloom_indexes)
union all
select
    concat ('reindex index "', schema_name, '"."', index_name, '"; -- bloom index') as reindex_cmd
from
    bloom_indexes
where
    schema_name in (select schema_name from brin_indexes) and
    table_name in (select table_name from brin_indexes)
order by
    reindex_cmd;

reply

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: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Updating to PostgreSQL 17.5
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox