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
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 |