citusdata / citusdata/citus

Slow down by using TO_DATE in WHERE instead of constants.

Open
#7,029 1 comment 1 reaction 0 assignees View on GitHub
columnar
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

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.