Apart from using the pageinspect extension is there any other way ?
On Mon, 26 Aug 2024 at 05:32, Peter Geoghegan <pg@bowt.ie> wrote:
On Fri, Aug 23, 2024 at 3:25 PM Craig Milhiser <craig@milhiser.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:
