Clickhouse: Slow query response by using date time field instead of date

[stavros24metrics]

Hello,

I have created a table in CH using date and request_time as fields as shown below:

date Date DEFAULT toDate(request_time),
request_time DateTime DEFAULT now(),

I can see a huge difference in the response time when I run the following queries:

SELECT COUNT() FROM table WHERE field_name_1 ='sth' AND field_name_2 IN('sth') AND date >= '2019-04-16' AND date < '2019-04-17'

SELECT COUNT() FROM table WHERE field_name_1 ='sth' AND field_name_2 IN('sth') AND request_time >= '2019-04-16 00:00:00' AND request_time <= '2019-04-16 23:59:59'

The results respectively are shown below:

1. 0.55 sec.| 11,954,689 rows | response 143164
2. 2.25 sec.| 43,731,625 rows | response 143164

Could someone tell me why there is such a huge difference in the response time? I need to use the second query with the request time apparently.

[den-crane]

Just use request_time in a partitioning expression and primary key expression.

create table (
....)
partition by toYYYYMM(request_time)
order by field_name_1, request_time

CH able to eliminate partitions and uses a table order (index organized table) for locating rows.

[bzon]

@den-crane can you still modify these table settings after table creation?

[den-crane]

@bzon of course not (it has no sense it requires to remerge everything).
I am using https://clickhouse.yandex/docs/en/operations/table_engines/merge/ (Example 2:)