improving heuristics on "chunk group filtering" with a switch
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
for a table with
* `col1`: ordered value
* `col2`: almost ordered value (eg. 1...10000, 9500..19500, 19000..29000)
and a query like
```
select * from table where
col1 between x1 and y1 and
col2 between x2 and y2
```
The optimizer only uses `col1` in the "chunk group filtering" as expected from the post
https://www.citusdata.com/blog/2021/09/17/citus-10-2-extension-to-postgres-whats-new/ where says
> As a rule of thumb, lookup queries on columnar tables work as follows:
> * When the column filtered in the WHERE clause is ordered (say, timestamp or auto-incrementing primary key), Chunk Group Filtering is often picked as most of the chunks will be eliminated via Chunk Group Filtering
> * When the column filtered in the WHERE clause is not ordered (say, randomly populated integer/UUID), indexes are often picked as most of the chunks won't be eliminated via Chunk Group Filtering
however in the described table not using col2 in the "chunk group filter" leads to decompressing too many unecesary chunks with performance decrees.
Could this heuristic be switchable?
Contributor guide
Assessment
This issue has not been assessed yet.