From: Craig Milhiser <craig@milhiser.com>
Date: Fri, 23 Aug 2024 15:24:50 -0400
Message-ID: 
 <CA+wnhO2gfnajw+3sFWC70obWw8xV+qKAmPwmERukM93C4dFVEw@mail.gmail.com>
Subject: Is index deduplication active on an index
To: pgsql-admin@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000460aee06205ebb9e"
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?utf-8?B?Q3hNT2orVWVjaXFVaXhONU55dmUvSzVwTEdndXYrbnI0RGJ1NHNlcU9oVFlh?=
 =?utf-8?B?bU55TmJaczhwdUlXRzhobHZ6OEEyd1QrQ3ZtTUkybVVXaGFxWEUrVUVxVTRE?=
 =?utf-8?B?SGJwT05oRXRPSWpRSTJVTEhoS2RiNlFVdkJFUnJ2TndiN0hrUTVOK3NSRDBU?=
 =?utf-8?B?SlpqSU55bkQzRldNeHp3MUFpK1BVY2REZGJPVEU1ZmtQNGxXWWdSNXZ1UzY2?=
 =?utf-8?B?bXE5anNCN3hPNVJyVUFuaU5Hb0NDS3VVZVROeUNKbTltVnNZVnlWTWg1RlF3?=
 =?utf-8?B?Wm1na25YUzFXRndWUmhCeGFWNjREejRJNTJ2TmdualhwZHpHNUNYZjMxT285?=
 =?utf-8?B?R291cjZZTWExN1JEOWpNS0Fjc3pqZVNPY0dWWFpFYUZzdTNBUndFTHNCdWYw?=
 =?utf-8?B?Zkd1NGhMVXg3Zm5wNzBtRDdpcmd6TkVIOXBRWU1QcGVuRDYxdG1LbTlWSU5a?=
 =?utf-8?B?ejdiQjhGR2c5b3ZQVG83SDlKazM1QlJsTm9mVzRULzRBVHJFSFJVcHRDMnFh?=
 =?utf-8?B?amtSZHp3SGJlTTZOaHNDandMMmRKRThHaWgra2JVbFhjQXZUamh6N0tqdDRS?=
 =?utf-8?B?OTlxZFhkbEFPYzByd0ZxZGR2TUQ0Q0ozV1BBeERxeHNhWXMyUFpvenRJMWp4?=
 =?utf-8?B?QUxNM0hQdWF5c3NNZFdvN08zUkJQd0FKTWYvWVBDMHVNR1pBQ1pEN3QwVEM5?=
 =?utf-8?B?NlBKOHZWUzdsRE9uVXVZemRxemswMUNHWlk5c3BFVmFWZWhIVmJsSnUwcVlN?=
 =?utf-8?B?enNXZkJzQ21OKytRZndVa285OFN4cDgvYVcwWFdjZzh3M0o4aEgveFREWHZn?=
 =?utf-8?B?b016ZTEreUtjQ2dxd0RlUW1VcjdhRXNheWhGRWlPcWNGSVBTRTU5WCtTVngv?=
 =?utf-8?B?dlNpWXo1Vk1pdzNGWjJhZmxIZitXS0d0T0dwbUZkVEpWMzl1YVBud0hSZFpJ?=
 =?utf-8?B?amYwWkJ2QTEwckN6MEdNcVNYNEFXM3RSdXoyaFhSbkRmOFBJNFp3bENEQnEv?=
 =?utf-8?B?L1hjRGlIRS85VElIWEFDSWN5L3J3dzZBQTlBU1Jwbmk3NjI5Y0N2NWhVOG9W?=
 =?utf-8?B?K2hLcEJ1dFR5L3ZFRVpYTGtlelY1V1VzS1NPNitqOWZJa21FVDR0TU1KWDZU?=
 =?utf-8?B?QTZVZTJIU3JRWEdxT29ZTUh1R0NnUXNwbFBCdTlRZmYveTZCOW9NSjYvQkxH?=
 =?utf-8?B?aWcraGxYTlViL2NxVndJQm1WRHB4bXVpUVNGb1pvRjlGVmlTVlFpODZrdEpB?=
 =?utf-8?B?aUxrUGExTTB1STQ3MjFFaUVVem4zTDY2WUo3NEo2bCt2UmtQd0ZobEE4clF4?=
 =?utf-8?B?T2VpZFdLeUFPYWo0aHdTaUxDbGlZbkxDNm9MdHZHaGJPZGEyN1pYb1FGN2g2?=
 =?utf-8?B?K2JlelF4a0RpUUo2QnpKR0hZM0puSGZDbE5BSTVpVEFxOU9sWE9uQ0ROTnc2?=
 =?utf-8?B?K3M3dkExVHdqN0k5cUJWVHZYb29pblJwcmREUURpVy9rQ2dUaVcyNW5IL2pm?=
 =?utf-8?B?Rm44LzVoUVErYjhJdEdpRmYvT0hpMDlRcXIwOWNmSndta3BuZjJrcEl2dk5U?=
 =?utf-8?B?aWVMU0NSS0w0cTZ6SjdNTTgvNk1sWlJzZEFCWWVxblNRbmJSZnBHbEZjYnhv?=
 =?utf-8?B?M1BuQ3BOVUp3eUErVXBBeGkreHJMWEh1alpxR2VzUXd6S2E0LzRDcVBhanF6?=
 =?utf-8?B?OEFDbzh5U0VRVnpLYmJhSklSbVA3T2dBSmxpN0VmSStOZnFyTjM2Mjl4cklG?=
 =?utf-8?B?V21sNW9ubFE2OFl6cTI4dGVjYzNRVDIvU0VCOUNyNUNKeG95L0haZmhhOXRw?=
 =?utf-8?B?Q05MY2JMTjBOUy93TzFsMDVDRERVaGZLZDQ5MTdUbUJDSFRyT1F2dzMvQlJV?=
 =?utf-8?B?Z2xGYmN2UmU1VEJadkZSNDErNDR1TGVoUXJ4SzUxb1ZuZ05Tb0N2OUdDSFVu?=
 =?utf-8?B?ZDZYY0xJcFpDamNvZWQ2T3RpUlRweDZvL00xU1pyOHJmWmhMRnFtZEkzeEFC?=
 =?utf-8?B?L1Y3UEpobmlKNXRFQjJoSWg5TjJZeFI2OXduTmRyQzVKREF5ZERGYmFmNy8w?=
 =?utf-8?B?WFBHRnRvc2FmRDEzZVFUcXFJVnVYdWNUS0pNWXZkNHdhdElwOU01T2ZxL0R0?=
 =?utf-8?Q?VeMspIj/hNpHulzuLPvEh3uEq?=
X-OriginatorOrg: milhiser.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 658fd366-0866-403b-bcc1-08dcc3a94b89
X-MS-Exchange-CrossTenant-AuthSource: BN9PR11MB5545.namprd11.prod.outlook.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 23 Aug 2024 19:25:06.9183
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 538ddb49-3475-41a4-a685-313f6b534707
X-MS-Exchange-CrossTenant-MailboxType: HOSTED
X-MS-Exchange-CrossTenant-UserPrincipalName: 
 BkWV6opOaWRRUViWXby+q3Rm+Vn575+ZarJDkbKJQ8yHUaDdChcCk2mFVisTuSC0Q/yiraDJINTW7E4ezq3YZw==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BL1PR11MB5253
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BwnhO2gfnajw%2B3sFWC70obWw8xV%2BqKAmPwmERukM93C4dFVEw%40mail.gmail.com>
Precedence: bulk

--000000000000460aee06205ebb9e
Content-Type: text/plain; charset="UTF-8"

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

--000000000000460aee06205ebb9e
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dutf-8"><d=
iv dir=3D"auto"><div><span style=3D"color:rgb(0,0,0);font-family:'-apple-sy=
stem','helveticaneue';font-size:16px;font-style:normal;font-weight:400;lett=
er-spacing:normal;text-indent:0px;text-transform:none;white-space:normal;wo=
rd-spacing:0px;text-decoration:none;display:inline!important;float:none">Is=
 there a way I can determine if index deduplication is active on the indexe=
s?&nbsp;</span></div></div><div dir=3D"auto"><br></div><div dir=3D"auto">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 upgrad=
es. Someone may have run a reindex over the years.<div dir=3D"auto"><br></d=
iv><div dir=3D"auto">Then I can reindex if needed those indexes created pri=
or to v13.&nbsp;<br><div dir=3D"auto"><br></div><div dir=3D"auto">I looked =
in pg_index but I did not find a field. Perhaps it is in a flag in indoptio=
n 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 ind=
ex 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 t=
he flag is stored in the index meta page. Is there a way to find index dedu=
plication is active for an index from a postgresql client?</div><div dir=3D=
"auto"><br></div><div dir=3D"auto">Thanks</div><div dir=3D"auto"><br></div>=
</div>
</div>

--000000000000460aee06205ebb9e--