public inbox for [email protected]  

help / color / mirror / Atom feed

From: Peter Geoghegan <[email protected]>
To: Craig Milhiser <[email protected]>
Cc: [email protected]
Subject: Re: Is index deduplication active on an index
Date: Sun, 25 Aug 2024 20:01:32 -0400
Message-ID: <CAH2-WzmxSGDuKH7bPMXS2dRxK2eCOwZgVew6Bu5r9hN=HNaLrA@mail.gmail.com> (raw)
In-Reply-To: <CA+wnhO2gfnajw+3sFWC70obWw8xV+qKAmPwmERukM93C4dFVEw@mail.gmail.com>
References: <CA+wnhO2gfnajw+3sFWC70obWw8xV+qKAmPwmERukM93C4dFVEw@mail.gmail.com>

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

view thread (5+ messages)  latest in thread

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], [email protected]
  Subject: Re: Is index deduplication active on an index
  In-Reply-To: <CAH2-WzmxSGDuKH7bPMXS2dRxK2eCOwZgVew6Bu5r9hN=HNaLrA@mail.gmail.com>

* 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