Subject: Is index deduplication active on an index

From: Craig Milhiser <[email-masked]>
Date: Fri, 23 Aug 2024 15:24:50 -0400

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.

Then I can reindex if needed those indexes created prior to v13.

I looked in pg_index but I did not find a field. Perhaps it is in a flag in
indoption but I did not see that in the definition in
src/include/catalog/pg_index.h. I see the allequalimage flag to protect the
conditions described in the documentation. But I do not know if that flag
is reused to signify this index has been evaluated for deduplication due to
a reindex and it is safe. A comment in _bt_allequalimage in
src/backend/access/nbtree/nbtutils.c says the flag is stored in the index
meta page. Is there a way to find index deduplication is active for an
index from a postgresql client?

Thanks

---

From: Mahesh Shetty <[email-masked]>
Date: Sat, 24 Aug 2024 11:46:56 +0530

Even I’m looking for the same.

On Sat, Aug 24, 2024 at 12:55 AM Craig Milhiser <[email-masked]> wrote:

> I looked in pg_index but I did not find a field. Perhaps it is in a flag
> in indoption but I did not see that in the definition in

---

From: Peter Geoghegan <[email-masked]>
Date: Sun, 25 Aug 2024 20:01:32 -0400

On Fri, Aug 23, 2024 at 3:25 PM Craig Milhiser <[email-masked]> 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

---

From: Mahesh Shetty <[email-masked]>
Date: Mon, 26 Aug 2024 08:12:40 +0530

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

---

From: Peter Geoghegan <[email-masked]>
Date: Sun, 25 Aug 2024 23:09:26 -0400

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

No.

-- 
Peter Geoghegan