Re: Is index deduplication active on an index

Apart from using the pageinspect extension is there any other way ?

On Mon, 26 Aug 2024 at 05:32, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

> On Fri, Aug 23, 2024 at 3:25 PM Craig Milhiser <craig(at)milhiser(dot)com> wrote:
> > Is there a way I can determine if index deduplication is active on the
> indexes?
> >
> > I inherited a database that has been upgraded from v12 to 13 to 16. I
> checked the upgrade scripts used and there was not a reindex run during the
> upgrades. Someone may have run a reindex over the years.
>
> You can do this using contrib/pageinspect, which has a function that
> can read the index metapage for you. For example, the following query
> shows the 10 largest indexes that cannot use deduplication:
>
> create extension if not exists pageinspect;
> SELECT
> c.relname,
> c.relpages
> FROM pg_index i
> JOIN pg_opclass op ON i.indclass[0] = op.oid
> JOIN pg_class c ON i.indexrelid = c.oid
> JOIN pg_am am ON op.opcmethod = am.oid
> JOIN pg_namespace n ON c.relnamespace = n.oid
> WHERE am.amname = 'btree'
> AND NOT (select allequalimage AS supports_deduplication FROM
> bt_metap(n.nspname || '.' || c.relname))
> AND c.relkind = 'i' AND i.indisready AND i.indisvalid
> ORDER BY c.relpages DESC LIMIT 10;
>
> Note, however, that this will show you indexes that don't use
> deduplication regardless of the underlying reason. It could just be a
> matter of running REINDEX to get deduplication working, but it might
> also be due to certain implementation level restrictions that you
> can't do anything about. For example, indexes on numeric columns don't
> support deduplication.
>
> The data types (opclasses, actually) that don't support deduplication
> are listed towards the end of this section of the docs:
>
>
> https://www.postgresql.org/docs/current/btree-implementation.html#BTREE-DEDUPLICATION
>
> --
> Peter Geoghegan
>
>
>