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

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #18437: The index scan result is more than the full scan result, and the primary key index has duplicate val
Дата
Msg-id 18437-ad0dab8112f45764@postgresql.org
обсуждение исходный текст
Ответы Re: BUG #18437: The index scan result is more than the full scan result, and the primary key index has duplicate val  (Laurenz Albe <laurenz.albe@cybertec.at>)
Список pgsql-bugs
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.


В списке pgsql-bugs по дате отправления:

Предыдущее
От: Richard Guo
Дата:
Сообщение: Re: BUG #18429: Inconsistent results on similar queries with join lateral
Следующее
От: Laurenz Albe
Дата:
Сообщение: Re: BUG #18437: The index scan result is more than the full scan result, and the primary key index has duplicate val