From: Laurenz Albe <laurenz.albe@cybertec.at>
To: 1165125080@qq.com
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18437: The index scan result is more than the full scan result, and the primary key index has duplicate val
Date: Mon, 15 Apr 2024 16:18:04 +0200
Message-ID: <33fd6b8acc5c3a8a34d0a8c6ee360cde4c23a5b1.camel@cybertec.at> (raw)
In-Reply-To: <18437-ad0dab8112f45764@postgresql.org>
References: <18437-ad0dab8112f45764@postgresql.org>
On Mon, 2024-04-15 at 13:25 +0000, PG Bug reporting form wrote:
> PostgreSQL version: 12.6
>
> I have a table sch.tb1 with the following structure:
>
> dste_pg_db=# \d sch.tb1
> ...
> Indexes:
> "tb1_id_pkey" PRIMARY KEY, btree (id)
> "dste_col_i_1" btree (col3)
> "dste_col_i_2" btree (col4)
> "dste_col_i_3" btree (col7)
> "dste_col_i_4" btree (col11)
> "dste_col_i_5" btree (col1)
> "dste_col_i_6" btree (col8)
>
>
> Use seq scan，the number of lines is 125680
>
> set enable_indexscan = off;
> select count(id) from dste_smt.dste_role_t;
> count
> --------
> 125680
> (1 row)
>
> But with index only scan, the number of rows is more than the real number.
>
> All indexes are, including primary keys.
>
> set enable_indexscan = on;
> dste_pg_db=# select count(id) from dste_smt.dste_role_t;
> count
> --------
> 125684
> (1 row)
Yes, that must be data corruption.
You'll have to identify and delete duplicate values, then rebuild the indexes.
That *might* be caused by a PostgreSQL bug, and it might well be a bug that
got fixed since 12.6. Hard to tell. You should have applied the latest
minor release (but that cannot fix the problem now).
Without a way to reproduce this in PostgreSQL 12.18, there is little we can do.
Yours,
Laurenz Albe
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: pgsql-bugs@postgresql.org
Cc: laurenz.albe@cybertec.at, 1165125080@qq.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18437: The index scan result is more than the full scan result, and the primary key index has duplicate val
In-Reply-To: <33fd6b8acc5c3a8a34d0a8c6ee360cde4c23a5b1.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox