pg.ddx.io  pgsql-general@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Updating to PostgreSQL 17.5
3+ messages / 2 participants
[nested] [flat]

* Updating to PostgreSQL 17.5
@ 2025-05-11 18:08 Paul Foerster <paul.foerster@gmail.com>
  2025-05-11 19:36 ` Re: Updating to PostgreSQL 17.5 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Paul Foerster @ 2025-05-11 18:08 UTC (permalink / raw)
  To: Pgsql-General List <pgsql-general@lists.postgresql.org>

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;




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Updating to PostgreSQL 17.5
  2025-05-11 18:08 Updating to PostgreSQL 17.5 Paul Foerster <paul.foerster@gmail.com>
@ 2025-05-11 19:36 ` Tom Lane <tgl@sss.pgh.pa.us>
  2025-05-11 20:08   ` Re: Updating to PostgreSQL 17.5 Paul Foerster <paul.foerster@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2025-05-11 19:36 UTC (permalink / raw)
  To: Paul Foerster <paul.foerster@gmail.com>; +Cc: Pgsql-General List <pgsql-general@lists.postgresql.org>

Paul Foerster <paul.foerster@gmail.com> writes:
> 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.

Sorry --- it means BRIN indexes that use one of the "xxx_bloom_ops" opclasses.

https://www.postgresql.org/docs/current/brin.html#BRIN-BUILTIN-OPCLASSES

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Updating to PostgreSQL 17.5
  2025-05-11 18:08 Updating to PostgreSQL 17.5 Paul Foerster <paul.foerster@gmail.com>
  2025-05-11 19:36 ` Re: Updating to PostgreSQL 17.5 Tom Lane <tgl@sss.pgh.pa.us>
@ 2025-05-11 20:08   ` Paul Foerster <paul.foerster@gmail.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Paul Foerster @ 2025-05-11 20:08 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pgsql-General List <pgsql-general@lists.postgresql.org>

Hi Tom,

> On 11 May 2025, at 21:36, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> Sorry --- it means BRIN indexes that use one of the "xxx_bloom_ops" opclasses.
> 
> https://www.postgresql.org/docs/current/brin.html#BRIN-BUILTIN-OPCLASSES

Ah, so that'd reduce my monster query to:

select
    concat ('reindex index "', schemaname, '"."', indexname, '";') as reindex_cmd
from
    pg_indexes
where
    indexdef ~ '_bloom_ops';

Thanks very much.

Paul




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2025-05-11 20:08 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-05-11 18:08 Updating to PostgreSQL 17.5 Paul Foerster <paul.foerster@gmail.com>
2025-05-11 19:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-05-11 20:08   ` Paul Foerster <paul.foerster@gmail.com>

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