Re: BUG #18437: The index scan result is more than the full scan result, and the primary key index has duplicate val

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