Potential BRIN Index Corruption

From: Huan Ruan <leohuanruan(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Potential BRIN Index Corruption
Date: 2020-11-26 00:46:08
Message-ID: CAGgcTZuG6bAjkYmo_Dt7tZgFLjnb0K1=CovVQh1=toTAYE_pKA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

1. if anyone else has experienced similar issues
2. 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.

After a reindex of ts_brin_index, both variations of the query correctly
return 1 record.

Thanks
Huan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2020-11-26 02:42:44 Re: Potential BRIN Index Corruption
Previous Message David G. Johnston 2020-11-25 20:48:37 Re: limit of data type character varying