>> 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.
>> dste_pg_db=# select count(col4) from dste_smt.dste_role_t;
>> count
>> --------
>> 126847
>> (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).
After testing, I found that the problem was with the table's vm visibility mapping file.
When I replace the problem table vm file with an all-0 vm file that uses an empty table.
$ hexdump 115327046_vm
0000000 0e3f 0000 0110 9400 0000 0000 0018 2000
0000010 2000 2004 0000 0000 0001 0000 0000 0000
0000020 0000 0000 0000 0000 0000 0000 0000 0000
*
0002000
I found that the index only scan result became the same as the seq scan result.
dste_pg_db=> explain analyze select count(col4) from sch1.tb1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=2604.43..2604.44 rows=1 width=8) (actual time=98.969..98.970 rows=1 loops=1)
-> Index only using dste_col_i_2 on sch1.tb1 (cost=0.42..2291.20 rows=125292 width=2) (actual time=0.137..82.381 rows=125680 loops=1)
Heap Fetches: 126847
Planning time: 1.050 ms
Execution time: 99.057 ms
(6 rows)
dste_pg_db=> select count(col4) from sch1.tb1;
select count(id) from dste_smt.dste_role_t;
count
--------
125680
(1 row)
The problem should be in the vm file, is there any possible reason for this？
------------------ 原始邮件 ------------------
发件人: "Laurenz Albe"<laurenz.albe@cybertec.at>;
发送时间: 2024年4月15日(星期一) 晚上10:18
收件人: "1165125080"<1165125080@qq.com>; "pgsql-bugs"<pgsql-bugs@lists.postgresql.org>;
主题: Re: BUG #18437: The index scan result is more than the full scan result, and the primary key index has duplicate val
/* 邮件内部图片支持调起预览。 */img[image-inside-content='1'] {cursor: pointer;}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.qmbox style, .qmbox script, .qmbox head, .qmbox link, .qmbox meta {display: none !important;}