public inbox for [email protected]
help / color / mirror / Atom feedFrom: Mahesh Shetty <[email protected]>
To: Peter Geoghegan <[email protected]>
Cc: Craig Milhiser <[email protected]>
Cc: [email protected]
Subject: Re: Is index deduplication active on an index
Date: Mon, 26 Aug 2024 08:12:40 +0530
Message-ID: <CAJPiW+g+kT=_NfoOfs3-4BpeBx=yA2mJmMV9s2J6BGCRPxQDsw@mail.gmail.com> (raw)
In-Reply-To: <CAH2-WzmxSGDuKH7bPMXS2dRxK2eCOwZgVew6Bu5r9hN=HNaLrA@mail.gmail.com>
References: <CA+wnhO2gfnajw+3sFWC70obWw8xV+qKAmPwmERukM93C4dFVEw@mail.gmail.com>
<CAH2-WzmxSGDuKH7bPMXS2dRxK2eCOwZgVew6Bu5r9hN=HNaLrA@mail.gmail.com>
Apart from using the pageinspect extension is there any other way ?
On Mon, 26 Aug 2024 at 05:32, Peter Geoghegan <[email protected]> wrote:
> 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], [email protected]
Subject: Re: Is index deduplication active on an index
In-Reply-To: <CAJPiW+g+kT=_NfoOfs3-4BpeBx=yA2mJmMV9s2J6BGCRPxQDsw@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