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.
After a reindex of ts_brin_index, both variations of the query correctly return 1 record.
Thanks
Huan
