Repository navigation
Description
I have an in-house clickhouse cluster where OTEL database and tables for logs are setup in following format:
otel_logs(Distributed Table) -> otel_logs_merged (to read from multiple tables like otel_logs_local, otel_logs_local_old, etc) -> otel_logs_local
When using lucene search option on HyperDx UI, full text search was slow and thereby after investigating the generated SQL via HyperDx UI using explain indexes=1, it was identified that HyperDx is not creating the query which will actually use FTS.
Generated SQL
SELECT
Timestamp,
ServiceName as service,
SeverityText as level,
Body
FROM
oteldb.otel_logs
WHERE
(
Timestamp >= fromUnixTimestamp64Milli(1777574225000)
AND Timestamp <= fromUnixTimestamp64Milli(1777574285000)
)
AND ((hasToken(lower(Body), lower('test'))))
ORDER BY
Timestamp DESC
LIMIT
200 OFFSET 0
SETTINGS
enable_full_text_index = 1
Later it was confirmed that this is because of the merged tree which is causing the issue on the Clickhouse slack channel.
Link for the thread
I have verified that after removing the merge tree, HyperDx is using the right query to trigger FTS index.
New SQL:
SELECT
Timestamp,
ServiceName as service,
SeverityText as level,
Body
FROM
oteldb.otel_logs_new
WHERE
(
Timestamp >= fromUnixTimestamp64Milli(1777573954661)
AND Timestamp <= fromUnixTimestamp64Milli(1777574854661)
)
AND ((hasAllTokens (Body, 'test')))
ORDER BY
(toUnixTimestamp(Timestamp), Timestamp) DESC
LIMIT
200
SETTINGS
enable_full_text_index = 1
explain indexes output
EXPLAIN indexes = 1
SELECT
Timestamp,
ServiceName AS service,
SeverityText AS level,
Body
FROM oteldb.otel_logs_new
WHERE ((Timestamp >= fromUnixTimestamp64Milli(1777573954661)) AND (Timestamp <= fromUnixTimestamp64Milli(1777574854661))) AND hasAllTokens(Body, 'test')
ORDER BY (toUnixTimestamp(Timestamp), Timestamp) DESC
LIMIT 200
SETTINGS enable_full_text_index = 1
FORMAT lineasstring
Query id: b8221ea4-3781-4360-8b74-7e79977144f9
Expression (Project names)
Limit (preliminary LIMIT)
Sorting (Merge sorted streams after aggregation stage for ORDER BY)
Union
Sorting (Sorting for ORDER BY)
Expression ((Before ORDER BY + Projection))
Expression
ReadFromMergeTree (oteldb.otel_logs_local)
Indexes:
MinMax
Keys:
Timestamp
Condition: and((Timestamp in (-Inf, '1777574854.661']), (Timestamp in ['1777573954.661', +Inf)))
Parts: 0/1
Granules: 0/1
Partition
Keys:
toDate(Timestamp)
Condition: and((toDate(Timestamp) in (-Inf, 20573]), (toDate(Timestamp) in [20573, +Inf)))
Parts: 0/0
Granules: 0/0
PrimaryKey
Keys:
toUnixTimestamp(Timestamp)
Condition: and((toUnixTimestamp(Timestamp) in (-Inf, 1777574854]), (toUnixTimestamp(Timestamp) in [1777573954, +Inf)))
Parts: 0/0
Granules: 0/0
Skip
Name: fts_body
Description: text GRANULARITY 100000000
Condition: (mode: All; tokens: ["test"])
Parts: 0/0
Granules: 0/0
Ranges: 0
ReadFromRemote (Read from remote replica)
35 rows in set. Elapsed: 0.007 sec.
Activity
pulpdrew commentedon May 20, 2026
Thanks for raising this issue. #2277 (just merged) provides an option to force-enable the text index support (hasAllTokens for implicit column search) for schemas where the text index isn't auto-detected