From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 1165125080@qq.com
Subject: 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 13:25:28 +0000
Message-ID: <18437-ad0dab8112f45764@postgresql.org> (raw)
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.
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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, 1165125080@qq.com
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: <18437-ad0dab8112f45764@postgresql.org>
* 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