Slow down by using TO_DATE in WHERE instead of constants.
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
We have a table consisting of many columnar partitions on Citus 10:
`Partition key: LIST (_source_file)`
We noticed that simple command using `TO_DATE` functions in `WHERE` is much slower than the same command using simple `'YYYY-MM-DD'::date` constants, 80 seconds against 2 seconds.
I attach details in EXPLAIN ANALYZE outputs of both cases:
[fast.txt](https://github.com/citusdata/citus/files/11833038/fast.txt)
[slow.txt](https://github.com/citusdata/citus/files/11833039/slow.txt)
The data types seem to be the same:
```
# SELECT pg_typeof(TO_DATE('2023-02-19 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.US'));
pg_typeof
-----------
date
# SELECT pg_typeof('2023-02-19'::date);
pg_typeof
-----------
date
```
Output of `\d` shows
```
period | date | | not null |
```
Yesterday I was told in Slack by Ivan Vyazmitinov:
"Yep, definitely looks like a Citus issue... I suppose, the issue here is that with `to_date` Citus doesn't filter chunks and applies the filter to all rows instead: see how it is
`Rows Removed by Filter: 716532`
In the slow plan instead of
`Columnar Chunk Groups Removed by Filter: 63`
In the fast one"
Contributor guide
Assessment
This issue has not been assessed yet.