References: 
 <MEYP282MB16694E0A76DD574FE7A31BC8B6D3A@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM>
 <1025275.1697121002@sss.pgh.pa.us>
User-agent: mu4e 1.4.15; emacs 28.1
From: Japin Li <japinli@hotmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Could not use index because of indcheckxmin is true
In-reply-to: <1025275.1697121002@sss.pgh.pa.us>
Date: Thu, 12 Oct 2023 23:31:26 +0800
Message-ID: 
 <MEYP282MB166969CC0F50558A8E4AE486B6D3A@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM>
Content-Type: text/plain
MIME-Version: 1.0
X-MS-Exchange-MessageSentRepresentingType: 1
X-MS-PublicTrafficType: Email
X-MS-TrafficTypeDiagnostic: MEYP282MB1669:EE_|ME3P282MB3604:EE_
X-MS-Office365-Filtering-Correlation-Id: 4dce6850-f11f-47c5-ea1e-08dbcb384f5a
X-Microsoft-Antispam: BCL:0;
X-Microsoft-Antispam-Message-Info: 
	dYxF6Cp3w/Bd8ZMPhKmNG7gHqNnJzZ5pJi98O2o5SiTPtn0nO4R/+mwu4zYtxHVeXVEOCHKVKTLTmyvsDxlKaf7SPdgRqXk6f7DQUiHpAc0NPSPsU4tfjsa6hicpS4kyx1LLjJEMsFpCNj748b0p3sYQUpJicgpFtZ88cp56L5PWg4mCeL3x9B7qq6vUhYfiKTqIqcHAkurTcVCcs05lU0DIAKm1nyNSQcikne3dRRcB2P8Q5JsDuqnBCfDsZPt05Yfet+o6AP0BV8Bj1sDUymLjsDCqqSf7lCKOoybSqfrEI6lI4kDVoSVdo5NZFHFSvwjn+BBBEmXGnxv/eqEe5jqtVlTcD2xxWYRMlv+Bx9EI72LnC3kHVdA17PnMUrk9/cxIoCAhSV0S8MMsMr+kBLAbF3bJ29clhjaxwjQwuJpUJXzo/5YQ5v3J4Yzm79NTQDOXrwj54KdNwqfBIu2KQRZDwIOh7e31xJEDBA1qeFhjERULB8UX+qGFsvx9yQRCwlL3bLah6PK6Jgdl4o1JMXPX1BekblBJNsDiBCBkzXMUnd5BzH67yHpGEDTmUPoY
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?us-ascii?Q?y8J1M+cqpOLot+05gsTRZe9iTkwWBOJt9qGs4YkEtbp9X2OHXKMKchYvgyCA?=
 =?us-ascii?Q?HCizqYP8DtnEJQfgouV9FX2gN6oSJeScQxM9suVrtjc2CzNVfqtokNzAkwJh?=
 =?us-ascii?Q?3VZumzt6BIisx4mJXmiChfMEFk7pMXMIMTKBpsjA3xvR33Q5L+XiSegS56CJ?=
 =?us-ascii?Q?YpQmcEPnnEAXDBsMzbYNn5NxF6RZPvKM/0aFo8XiBfILxGRiwan1KOBH/RYj?=
 =?us-ascii?Q?DnAq9fOkqmckE4J6BqYprKT+QgPkgeDsf78Zji7Z47hco6EYxkaZXBrjHVy/?=
 =?us-ascii?Q?IduoQKdkqEIWFVz7Km3WLjH4PaPPHcOzUsifzB4fIYUZtowlbvNvxdTqo+7V?=
 =?us-ascii?Q?wZMI44m8d6D8WmfGHi0YdMFgnfw93H8nZkh/QeMY/gFwWSX83u+QQWxtWUoA?=
 =?us-ascii?Q?GwMJ7TzO7gixmcqcRAk19ain0tgc0vX/ghpdyOiRQHQgksDWJOMNNP65t9T+?=
 =?us-ascii?Q?m8CQFLswlvlN+p3m3RZeqpMDqWyP4Vspqzi2PDJVuU5kwDpJkWTqc/ofgDii?=
 =?us-ascii?Q?ZNezcGUY29Pk18/2EE+XMnzIu6Grap8WVoLNyNNmgrUvF3fmuBdfCusuCuNJ?=
 =?us-ascii?Q?fz5fzIXCWkGoaCWbCzrCie8sODTSbV7/4U1tsTnpp8+CF7BMnLavxuRPwKpn?=
 =?us-ascii?Q?JJDqMAlHke5GDBWM5wx6FUIttqfQ0PJtL8kc6ujVAYQo6UcL5FcDnNx4hIFX?=
 =?us-ascii?Q?K8w8OoZsL7beW75sAQ6InWUl9ZtuBlL6M6bOdopk5j92irf9KIgqKaTiAmne?=
 =?us-ascii?Q?sToidvkuQatVQyKBTPq7jKIGEx+ldXOptJSYHk8s8sqDbfhv5SaVV3MEBT6E?=
 =?us-ascii?Q?MfQO60SRMI521Fl56+F03OIE4n2wHVXIWZ1ZgUYosvpEpRnW77iPkrNa+F/i?=
 =?us-ascii?Q?r6f72nlr7FAXV/OgauInEsxMQwYnSOod10N/5EUlck3T3dhb2BWOeggdxXNQ?=
 =?us-ascii?Q?NS/aOsf5a8AkYiFhkzSsZlXhf2lcupEesXM6Sgw/3BQBY7+/2UNjkck+yPcv?=
 =?us-ascii?Q?h97cWtmA/B/4foodzJAEoL9is2aSk0+kp8WA7WPlriIiDC0TN/s0UkOfXXX5?=
 =?us-ascii?Q?v5BCzpg0LcsCmI4/kikwE4l8TsuthcZHfBwWIuOa+5tx/Kr7Rykf0PBtHMX/?=
 =?us-ascii?Q?bjdnGo2cp1IXgrqEA1DqZonP6NOOZLwqgGVon/p7SAMG3Rn2X9DPOapNtrJs?=
 =?us-ascii?Q?t20Ae7RPQmvHkLpM8KzQLD9Bu05mthUg6+n2Ww=3D=3D?=
X-OriginatorOrg: sct-15-20-4755-11-msonline-outlook-746f3.templateTenant
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 4dce6850-f11f-47c5-ea1e-08dbcb384f5a
X-MS-Exchange-CrossTenant-AuthSource: MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 12 Oct 2023 15:31:32.3519
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa
X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 
	00000000-0000-0000-0000-000000000000
X-MS-Exchange-Transport-CrossTenantHeadersStamped: ME3P282MB3604
List-Id: <pgsql-sql.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-sql@lists.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-sql>
Archived-At: 
 <https://www.postgresql.org/message-id/MEYP282MB166969CC0F50558A8E4AE486B6D3A%40MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM>
Precedence: bulk


On Thu, 12 Oct 2023 at 22:30, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Japin Li <japinli@hotmail.com> writes:
>> When I create an index using CREATE INDEX (without concurrently), I get
>> an index which indcheckxmin is true.  According to the docs[1], it cannot
>> be used with indcheckxmin is ture, how can I fix this?
>
> There is nothing to fix; you just wait a little bit.
>
>>> indcheckxmin bool
>>> If true, queries must not use the index until the xmin of this pg_index
>>> row is below their TransactionXmin event horizon, because the table may
>>> contain broken HOT chains with incompatible rows that they can see
>
> Note the "until" clause.  Once transactions that ran concurrently
> with the CREATE INDEX are done, the index is available for use.
> There is no need for anyone to clear the indcheckxmin flag, and
> AFAIR there is no code that does so.
>

Thanks for your explanation. After wait a moment, the index can be used.

-- 
Regrads,
Japin Li