A question about query performance

[wangDong524]

CrateDB version:
3.0.2

Environment description:

• JVM version: java version "1.8.0_231"
• Kernel: Linux localhost.localdomain 3.10.0-693.el7.x86_64
• Distribution: CentOS7
• Number of nodes：5

There are two tables in a cluster，and the other parameters are the same
table1: 10 Configured Shards ,4 Replicas ,57.9 Million Records ,96.7 GB
table2: 10 Configured Shards ,4 Replicas ,57.9 Million Records ,41.2 GB
Why is the data in table 2 small but the select time is much longer than that in table 1

[mfussenegger]

Could you post the exact queries and durations?

[wangDong524]

table1: SELECT count(1) FROM ydb.inv_ddlbcx kj WHERE kj.st_NSRSBH = '91110JC4856' AND st_CJSJ >= '2020-08-26 00:00:00' AND st_CJSJ <= '2020-08-26 23:59:59' AND kj.st_KJ_ZT != '7000' limit 100;

table2: SELECT count(1) FROM ydb.inv_combition kj WHERE kj.st_NSRSBH = '911104856' AND st_CJSJ >= '2020-08-26 00:00:00' AND st_CJSJ <= '2020-08-26 23:59:59' AND kj.st_KJ_ZT != '7000' limit 100;

table1 has 95 columns,table2 has 31 columns

query times:(millisecond)
  | table1 | table2
  | 0.259 | 0.885
  | 1.039 | 0.188
  | 1.024 | 0.18
  | 0.164 | 0.067
  | 0.801 | 0.231
  | 0.163 | 0.133
  | 0.155 | 0.079
  | 0.146 | 0.233
  | 0.765 | 0.184
  | 0.198 | 0.16
  | 0.186 | 0.103
  | 1.181 | 0.111
  | 0.175 | 0.101
  | 1.255 | 0.153
  | 0.191 | 0.092
  | 0.18 | 0.124
  | 0.164 | 0.116
  | 0.961 | 0.143
  | 0.156 | 0.079
  | 0.149 | 0.253
average time(millisecond) | 0.4656 | 0.18075

[wangDong524]

Table 2 is a small table generated from partial column data extracted from table 1

[mfussenegger]

average time(millisecond) | 0.4656 | 0.18075

Based on this the queries on the larger table are slower than the one on the smaller table. That seems to make sense?

Overall the filesystem cache can have a big impact on the query speed. So if a table is "hot", queries can be faster than on a "cold" table.

[wangDong524]

Thank you for your reply
Both of them are performing write and query operations with the same frequency. Why is there a difference between "hot" and "cold"？
Can I manually set table 2 “hot”?

[mfussenegger]

With "hot" and "cold" I was referring to whether data of a table is within the page cache of the kernel. You cannot manually set a table to hot or cold, nor is that managed by CrateDB - it's logic within the system kernel that is based on frequency and recency of accesses to files. Whenever you read from disk there is a chance that the data is already in a page cache and that it will be served from there instead of hitting the filesystem.

There is additionally a query-cache - that one is managed by CrateDB and also depends on refreshes on the table (which cause the cache to be invalidated) and some other factors. But again, there are not many tuning knobs for that.