new $__timeFilter, $__fromTime, $__toTime macros break DateTime primary key indexes

[TimSimmons]

What happened:

After I upgraded the Grafana plugin, I noticed a lot of my queries got very slow. This was because the new macro logic for time filtering does toDateTime64(1707855105945 / 1000, 3) and that seems incompatible with primary key filtering when you order by a DateTime column.

How to reproduce it (as minimally and precisely as possible):

CREATE TABLE grafana_plugin_repro (
    timestamp DateTime,
    value Int32
) ENGINE = MergeTree()
ORDER BY timestamp;

INSERT INTO grafana_plugin_repro (timestamp, value) SELECT
    now() - number AS timestamp,
    rand() % 100000 AS value
FROM numbers(100000000)

~~~~~

SELECT avg(value)
FROM grafana_plugin_repro
WHERE (timestamp >= toDateTime64(1707851505945 / 1000, 3)) AND (timestamp <= toDateTime64(1707855105945 / 1000, 3))

┌────────avg(value)─┐
│ 49614.35085611295 │
└───────────────────┘

1 row in set. Elapsed: 0.710 sec. Processed 100.00 million rows, 400.02 MB (140.76 million rows/s., 563.07 MB/s.) Peak memory usage: 401.89 MiB.


--- workarounds

SELECT avg(value)
FROM grafana_plugin_repro
WHERE (timestamp >= toUnixTimestamp(toDateTime64(1707851505945 / 1000, 3))) AND (timestamp <= toUnixTimestamp(toDateTime64(1707855105945 / 1000, 3)))

┌────────avg(value)─┐
│ 49625.68138138138 │
└───────────────────┘

1 row in set. Elapsed: 0.005 sec. Processed 6.03 thousand rows, 48.26 KB (1.27 million rows/s., 10.14 MB/s.) Peak memory usage: 4.03 MiB.

SELECT avg(value)
FROM grafana_plugin_repro
WHERE (timestamp >= toDateTime(1707851505945 / 1000, 0)) AND (timestamp <= toDateTime(1707855105945 / 1000, 0))

┌────────avg(value)─┐
│ 49625.68138138138 │
└───────────────────┘

1 row in set. Elapsed: 0.004 sec. Processed 6.03 thousand rows, 48.26 KB (1.58 million rows/s., 12.60 MB/s.) Peak memory usage: 4.03 MiB.

SELECT avg(value)
FROM grafana_plugin_repro
WHERE (timestamp >= toUnixTimestamp(toDateTime64(1707851505945 / 1000, 0))) AND (timestamp <= toUnixTimestamp(toDateTime64(1707855105945 / 1000, 0)))

┌────────avg(value)─┐
│ 49625.68138138138 │
└───────────────────┘

1 row in set. Elapsed: 0.038 sec. Processed 6.03 thousand rows, 48.26 KB (157.21 thousand rows/s., 1.26 MB/s.) Peak memory usage: 4.03 MiB.

SELECT avg(value)
FROM grafana_plugin_repro
WHERE (timestamp >= 1707851505) AND (timestamp <= 1707855105)

Query id: 3dae9da5-28f0-4345-abb2-8010174f91b0

┌────────avg(value)─┐
│ 49625.68138138138 │
└───────────────────┘

1 row in set. Elapsed: 0.005 sec. Processed 6.03 thousand rows, 48.26 KB (1.15 million rows/s., 9.19 MB/s.) Peak memory usage: 4.03 MiB

Screenshot:

Environment:

• Grafana version: Grafana v10.0.2 (b2bbe10fbc)
• Plugin version: 4.0.2
• OS Grafana is installed on: Linux
• User OS & Browser: OSX, Firefox
• Others:

[hueiyuan]

We also have encountered the same problem in datasource version 4.0.2.

[SpencerTorres]

This was a change intended to support filtering with millisecond precision, but considering the performance impact I have reverted it for the $__timeFilter macro in #721.

Instead I have added a $__timeFilter_ms macro for those who need to have millisecond precision. Also instead of using the division operator, it uses the fromUnixTimestamp64Milli function.

[SpencerTorres]

As a reminder, this fix is now available in 4.0.3