ng to BRIN.
The situation gets even worse when B-tree index is subjected to
identical tuples which often happens when you have an avalanche of
timestamps that are within less than 1ms of each other (frequent TS
rounding resolution).
--
Arcadiy Ivanov
arca...@gmail.com | @arcivanov | https://ivano
benchmark.
But apart from TPC-E and having to perform to it, is there any practical
real world usefulness in trying to have a B-tree index on TS-based data
just to have a PK on it, as opposed to having a BRIN on a TS field and
calling it a day?
--
Arcadiy Ivanov
arca...@gmail.com | @arcivanov
4) | 1531281600
(9190,3) | 1531281600
(9190,4) | 1531281600
(10307,4) | 1531281600
...
(10349,3) | 1531281600
(10371,3) | 1531281600
...
(10392,2) | 1531281600
(10445,4) | 1531281600
(10446,1) | 1531281600
(10446,2) | 1531281600
(11580,3) | 1531281600
...
(11586,2) | 1531281600
(11595,3) | 1531281600
...
(11598,2) | 1531281600
(11600,1) | 1531281600
...
(11601,2) | 1531281600
(11609,1) | 1531281600
...
(11621,2) | 1531281600
(11621,3) | 1531281600
(11664,3) | 1531281600
...
(11684,3) | 1531281600
(11726,2) | 1531281600
...
(11770,4) | 1531281600
(12891,2) | 1531281600
(14057,1) | 1531281600
...
(14058,2) | 1531281600
(14064,3) | 1531281600
...
(14071,3) | 1531281600
(1000 rows)
The issue seems to be with both clustering and BRIN stats.
Fillfactor on the table is not a factor, can be 100 can be 75, I'm able
to reproduce and fail to reproduce with both.
Clustering on `date_ord_idx` can be done with ANALYZE and without, in
some cases PG picks scan and sort, sometimes it uses index regardless of
stats.
Sometimes the table is properly clustered, sometimes it isn't
afterwards. Sometimes if you cluster first on the unanalyzed
`date_ord_idx`, clustering doesn't fix the problem, but then analyzing
and clustering on `date_ord_idx` again does fix the issue. Sometimes
neither helps.
Full vacuum analyze may help, or may not help, depending on the phase of
the moon.
When BRIN is actually fixed, the explain has a few hundred rows
eliminated by the recheck, not >1M, and queries are 50 - 70 ms long for
date range.
If anybody can give a suggestion on where in the code to start looking,
I'll be extra-grateful.
--
Arcadiy Ivanov
arca...@gmail.com | @arcivanov | https://ivanov.biz
https://github.com/arcivanov
186753
Planning time: 0.232 ms
Execution time: 4823.412 ms
(24 rows)
schema0=# \d schema0_lab.data_table
Table "schema0_lab.data_table"
Column | Type | Collation | Nullable
| Default
+--
