public inbox for [email protected]help / color / mirror / Atom feed
Is index deduplication active on an index 5+ messages / 3 participants [nested] [flat]
* Is index deduplication active on an index @ 2024-08-23 19:24 Craig Milhiser <[email protected]> 2024-08-24 06:16 ` Re: Is index deduplication active on an index Mahesh Shetty <[email protected]> 2024-08-26 00:01 ` Re: Is index deduplication active on an index Peter Geoghegan <[email protected]> 0 siblings, 2 replies; 5+ messages in thread From: Craig Milhiser @ 2024-08-23 19:24 UTC (permalink / raw) To: [email protected] 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Is index deduplication active on an index 2024-08-23 19:24 Is index deduplication active on an index Craig Milhiser <[email protected]> @ 2024-08-24 06:16 ` Mahesh Shetty <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Mahesh Shetty @ 2024-08-24 06:16 UTC (permalink / raw) To: Craig Milhiser <[email protected]>; +Cc: [email protected] Even I’m looking for the same. On Sat, Aug 24, 2024 at 12:55 AM 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. > > 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 > > ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Is index deduplication active on an index 2024-08-23 19:24 Is index deduplication active on an index Craig Milhiser <[email protected]> @ 2024-08-26 00:01 ` Peter Geoghegan <[email protected]> 2024-08-26 02:42 ` Re: Is index deduplication active on an index Mahesh Shetty <[email protected]> 1 sibling, 1 reply; 5+ messages in thread From: Peter Geoghegan @ 2024-08-26 00:01 UTC (permalink / raw) To: Craig Milhiser <[email protected]>; +Cc: [email protected] 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Is index deduplication active on an index 2024-08-23 19:24 Is index deduplication active on an index Craig Milhiser <[email protected]> 2024-08-26 00:01 ` Re: Is index deduplication active on an index Peter Geoghegan <[email protected]> @ 2024-08-26 02:42 ` Mahesh Shetty <[email protected]> 2024-08-26 03:09 ` Re: Is index deduplication active on an index Peter Geoghegan <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Mahesh Shetty @ 2024-08-26 02:42 UTC (permalink / raw) To: Peter Geoghegan <[email protected]>; +Cc: Craig Milhiser <[email protected]>; [email protected] 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 > > > ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Is index deduplication active on an index 2024-08-23 19:24 Is index deduplication active on an index Craig Milhiser <[email protected]> 2024-08-26 00:01 ` Re: Is index deduplication active on an index Peter Geoghegan <[email protected]> 2024-08-26 02:42 ` Re: Is index deduplication active on an index Mahesh Shetty <[email protected]> @ 2024-08-26 03:09 ` Peter Geoghegan <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Peter Geoghegan @ 2024-08-26 03:09 UTC (permalink / raw) To: Mahesh Shetty <[email protected]>; +Cc: Craig Milhiser <[email protected]>; [email protected] On Sun, Aug 25, 2024 at 10:42 PM Mahesh Shetty <[email protected]> wrote: > Apart from using the pageinspect extension is there any other way ? No. -- Peter Geoghegan ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-08-26 03:09 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-08-23 19:24 Is index deduplication active on an index Craig Milhiser <[email protected]> 2024-08-24 06:16 ` Mahesh Shetty <[email protected]> 2024-08-26 00:01 ` Peter Geoghegan <[email protected]> 2024-08-26 02:42 ` Mahesh Shetty <[email protected]> 2024-08-26 03:09 ` Peter Geoghegan <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox