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

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: 1165125080(at)qq(dot)com
Subject: BUG #18437: The index scan result is more than the full scan result, and the primary key index has duplicate val
Date: 2024-04-15 13:25:28
Message-ID: 18437-ad0dab8112f45764@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 18437
Logged by: ji xiaohang
Email address: 1165125080(at)qq(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Laurenz Albe 2024-04-15 14:18:04 Re: BUG #18437: The index scan result is more than the full scan result, and the primary key index has duplicate val
Previous Message Richard Guo 2024-04-15 09:09:19 Re: BUG #18429: Inconsistent results on similar queries with join lateral