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.
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 |