Обсуждение: Potential BRIN Index Corruption
Hi All
We have a table with 623 million records. It appears a BRIN index of this table on a timestamp column is missing some records, as illustrated below in a cut-down version with additional columns and indices omitted.
We cannot work out a reproducible case but we have a copy of the offending database. I was hoping to know
- if anyone else has experienced similar issues
- if anyone can shed some light on what to collect in order to fire a useful bug report
Version
- centos-release-7-7.1908.0.el7.centos.x86_64
- PostgreSQL 10.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
Table DDL
CREATE TABLE large_table_with_623m_records (
date_with_btree_index date,
ts_with_brin_index timestamp without time zone not null
);
CREATE INDEX date_bree_index ON large_table_with_623m_records
USING btree (date_with_btree_index COLLATE pg_catalog."default", date_with_btree_index);
CREATE INDEX ts_brin_index ON large_table_with_623m_records
USING brin (ts_with_brin_index);
Query
SELECT
*
FROM large_table_with_623m_records
WHERE
ts_with_brin_index >= '2018-06-29 12:12:50' AND ts_with_brin_index < '2018-06-29 12:13:00'
AND date_with_btree_index = '2013-05-21'
This query uses Index Scan on date_bree_index and correctly returns 1 record that has ts_with_brin_index = '2018-06-29 12:12:58:081'.
If I remove the last line (AND date_with_btree_index = '2013-05-21'), the query uses Bitmap Index Scan on ts_brin_index and incorrectly returns 0 record.
If I remove the last line (AND date_with_btree_index = '2013-05-21'), the query uses Bitmap Index Scan on ts_brin_index and incorrectly returns 0 record.
After a reindex of ts_brin_index, both variations of the query correctly return 1 record.
Thanks
Huan
On 2020-Nov-26, Huan Ruan wrote: > Hi All > > We cannot work out a reproducible case but we have a copy of the offending > database. I was hoping to know I think the way to debug this would be to see what WAL records have been emitted for the index, using pageinspect to find the problem index tuple. Use 'select ctid rrom large_table_with_623m_records where ...' to pinpoint the unindexed tuple's page number; see when (in LSN) was that tuple written; inspect WAL surroundings looking for updates (or lack thereof) for the BRIN index. Use pageinspect to examine raw brin data.
Thanks Alvaro for pointing me to pageinspect. I will give it a try and report back. It might take a few days. I knew this module but have never tried it before.
HI Alvaro
Unfortunately those records were written a while ago and we no longer keep their WAL logs. Thanks for your help anyway.
Huan
On Fri, 27 Nov 2020 at 08:40, Huan Ruan <leohuanruan@gmail.com> wrote:
Thanks Alvaro for pointing me to pageinspect. I will give it a try and report back. It might take a few days. I knew this module but have never tried it before.
On 12/9/20 12:07 AM, Huan Ruan wrote: > HI Alvaro > > Unfortunately those records were written a while ago and we no longer keep > their WAL logs. Thanks for your help anyway. > Can you estimate when roughly the records were written? E.g. by using a rough estimate of WAL or XIDs generated per day, or something like that. Maybe there was some I/O issue in that time period and a couple writes got lost, or something like that. Are there any other corrupted indexes on the table? regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Hi Tomas
The records have a timestamp column so we do know the time they were written. We didn't find any I/O issues that match that time but unfortunately as it's been a while we are not confident with that finding.
> Are there any other corrupted indexes on the table?
That was one of my first questions too. I don't see any physical errors in pg log so not sure if there are other corruptions. One thing we consider doing is to turn on checksums.
Regards
Huan