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

The following bug has been logged on the website:

Bug reference:      18437
Logged by:          ji xiaohang
Email address:      1165125080@qq.com
PostgreSQL version: 12.6
Operating system:   centos 7
Description:

I have a table sch.tb1 with the following structure:

dste_pg_db=# \d sch.tb1
                            Table "sch.tb1"
      Column       |              Type              | Collation | Nullable |
Default 
-------------------+--------------------------------+-----------+----------+---------
 id                | bigint                         |           | not null |

 col1              | character varying(256)         |           | not null |

 col2              | character varying(2)           |           |          |

 col3              | bigint                         |           |          |

 col4              | character varying(32)          |           |          |

 col5              | character varying(255)         |           | not null |

 col6              | character varying(255)         |           | not null |

 col7              | character varying(2)           |           |          |

 col8              | character varying(2)           |           |          |

 col9              | character varying(4000)        |           |          |

 col1              | integer                        |           |          |

 col1              | character varying(2)           |           |          |

 col1              | character varying(36)          |           | not null |

 col1              | timestamp(0) without time zone |           | not null |

 col1              | character varying(36)          |           | not null |

 col1              | timestamp(0) without time zone |           | not null |

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)

dste_pg_db=# select count(col3) from dste_smt.dste_role_t;
 count  
--------
 126702
(1 row)

dste_pg_db=# select count(col4) from dste_smt.dste_role_t;
 count  
--------
 126847
(1 row)


Compare the primary key columns of the full scan and index scan. 
It is found that the primary key column of the index scan has duplicate
values.

Then, the same two values can be found by index only scan.
select count(*) from sch.tb1 where id ='Duplicate ID';

But when I query all the rows for that value, I only find one row of data.
select * from sch.tb1 where id ='Duplicate ID';

Then run the index only scan again to check the primary key value. It turns
out to be one row.
select count(*) from sch.tb1 where id ='Duplicate ID.';

I suspect that the index is corrupted. I used the amcheck extension to check
it, but it didn't find the problem.
I don't know what could be causing this problem right now.

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