citusdata / citusdata/citus

improving heuristics on "chunk group filtering" with a switch

Open
#7,414 3 comments 0 reactions 0 assignees View on GitHub
columnar
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.