SET send_logs_level = 'trace';

WITH
    initial_query_id = '82142964-0b5d-4263-b996-302ce14bd779' AS second,
    initial_query_id = '7ea39e31-2f89-4085-843c-7246cb3baa5c' AS first
SELECT
    PE.1 AS metric,
    sumIf(PE.2, first) AS v1,
    sumIf(PE.2, second) AS v2,
    10 * log10(v2 / v1) AS dB
FROM clusterAllReplicas(
    default, system.query_log)
ARRAY JOIN ProfileEvents AS PE
WHERE (first OR second)
    AND (event_date >= today() - 3) AND (type = 2)
GROUP BY metric
HAVING v1 != v2
ORDER BY
    dB DESC,
    v2 DESC,
    metric ASC FORMAT PrettyCompactMonoBlock

Query id: d7747d26-a231-47c8-ae8c-284895b1aeaf

┌─metric──────────────────────────────────────┬─────────v1─┬─────────v2─┬───────────────────────dB─┐
│ SystemTimeMicroseconds                      │   13812127 │   24081938 │       2.4143087099482767 │
│ SoftPageFaults                              │    2651887 │    4056889 │        1.846381108610876 │
│ DiskReadElapsedMicroseconds                 │    1113947 │    1273786 │        0.582319430863304 │
│ CachedReadBufferReadFromCacheMicroseconds   │    1126505 │    1285450 │         0.57322064922068 │
│ OSCPUVirtualTimeMicroseconds                │   70301588 │   80045377 │       0.5637111926869545 │
│ RealTimeMicroseconds                        │   86686457 │   96339471 │       0.4585300419916516 │
│ QueryProfilerRuns                           │        157 │        174 │       0.4464959587336597 │
│ NetworkSendBytes                            │     868197 │     940859 │        0.349062627796429 │
│ NetworkReceiveElapsedMicroseconds           │        161 │        174 │       0.3372337225075003 │
│ ArenaAllocBytes                             │ 1480589312 │ 1497366528 │      0.04893510724370622 │
│ OSWriteBytes                                │     380928 │     385024 │      0.04644905045763538 │
│ ArenaAllocChunks                            │       2153 │       2157 │      0.00806115279057892 │
│ FileOpen                                    │       7511 │       7516 │    0.0028900944828012766 │
│ OpenedFileCacheMisses                       │       7511 │       7516 │    0.0028900944828012766 │
│ ContextLock                                 │       5880 │       5881 │    0.0007385332589917156 │
│ OSReadChars                                 │ 2340791432 │ 2340789818 │ -0.000002994506583727971 │
│ OSWriteChars                                │    2521310 │    2513992 │    -0.012623549714419216 │
│ AggregationPreallocatedElementsInHashTables │  128039910 │  127563540 │    -0.016187974135432794 │
│ OSCPUWaitMicroseconds                       │    1543643 │    1536999 │    -0.018732829140838268 │
│ OpenedFileCacheHits                         │        539 │        534 │    -0.040475081581823065 │
│ UserTimeMicroseconds                        │   56490840 │   55961729 │     -0.04086908559606555 │
│ WaitMarksLoadMicroseconds                   │     388571 │     359985 │      -0.3318598023153847 │
│ ThreadpoolReaderTaskMicroseconds            │    3816669 │    3392522 │      -0.5116182478775457 │
│ NetworkSendElapsedMicroseconds              │       4745 │       4122 │      -0.6112822932011739 │
│ AsynchronousReadWaitMicroseconds            │    2380284 │    2025078 │      -0.7018702173136342 │
│ NetworkReceiveBytes                         │        516 │        372 │      -1.4210676174531387 │
└─────────────────────────────────────────────┴────────────┴────────────┴──────────────────────────┘

26 rows in set. Elapsed: 0.173 sec. Processed 5.86 million rows, 2.40 GB (33.92 million rows/s., 13.92 GB/s.)

-- create table with SpanNAme as String and ResourceAttributes as Map
DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl (
    `Timestamp` DateTime64(9) CODEC (Delta(8), ZSTD(1)),
    `TraceId` String CODEC (ZSTD(1)),
    `ServiceName` LowCardinality(String) CODEC (ZSTD(1)),
    `Duration` UInt8 CODEC (ZSTD(1)), -- Int64
    `SpanName` LowCardinality(String) CODEC (ZSTD(1)),
    `ResourceAttributes` Map(LowCardinality(String), String) CODEC (ZSTD(1))
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, SpanName, toUnixTimestamp(Timestamp), TraceId);

-- create UDF to generate random Map data for ResourceAttributes
DROP FUNCTION IF EXISTS genmap;
CREATE FUNCTION genmap AS (n) -> arrayMap (x-> (x::String, (x*rand32())::String), range(1, n));

-- check that genmap is working as intended
SELECT genmap(10)::Map(String, String);

-- insert 1M rows
INSERT INTO tbl
SELECT
    now() - randUniform(1, 1000000.) as Timestamp,
    randomPrintableASCII(2) as TraceId,
    randomPrintableASCII(2) as ServiceName,
    rand32() as Duration,
    randomPrintableASCII(2) as SpanName,
    genmap(rand64()%500)::Map(String, String) as ResourceAttributes
FROM numbers(1_000_000);

-- querying for SpanName is faster
-- [cold] 0 rows in set. Elapsed: 0.642 sec. Processed 1.00 million rows, 7.21 MB (1.56 million rows/s., 11.22 MB/s.)
-- [warm] 0 rows in set. Elapsed: 0.164 sec. Processed 1.00 million rows, 7.21 MB (6.10 million rows/s., 43.99 MB/s.)
SELECT
    COUNT(*),
    avg(Duration/1E6) as average,
    quantile(0.95)(Duration/1E6) as p95,
    quantile(0.99)(Duration/1E6) as p99,
    SpanName
FROM tbl
GROUP BY SpanName ORDER BY 1 DESC LIMIT 50 FORMAT Null;

-- query for ResourceAttributes is slower
-- [cold] 0 rows in set. Elapsed: 6.432 sec. Processed 1.00 million rows, 5.65 GB (155.46 thousand rows/s., 879.07 MB/s.)
-- [warm] 0 rows in set. Elapsed: 5.935 sec. Processed 1.00 million rows, 5.65 GB (168.50 thousand rows/s., 952.81 MB/s.)
SELECT
    COUNT(*),
    avg(Duration/1E6) as average,
    quantile(0.95)(Duration/1E6) as p95,
    quantile(0.99)(Duration/1E6) as p99,
    ResourceAttributes['1'] as hostname
FROM tbl
GROUP BY hostname ORDER BY 1 DESC LIMIT 50 FORMAT Null;

-- solution is to add a column with value defaulting to a particular key in Map
ALTER TABLE tbl ADD COLUMN hostname LowCardinality(String) DEFAULT ResourceAttributes['1'];
ALTER TABLE tbl MATERIALIZE COLUMN hostname;

-- query for hostname (new column) is now faster
-- [cold] 0 rows in set. Elapsed: 2.215 sec. Processed 1.00 million rows, 21.67 MB (451.52 thousand rows/s., 9.78 MB/s.)
-- [warm] 0 rows in set. Elapsed: 0.541 sec. Processed 1.00 million rows, 21.67 MB (1.85 million rows/s., 40.04 MB/s.)
SELECT
    COUNT(*),
    avg(Duration/1E6) as average,
    quantile(0.95)(Duration/1E6) as p95,
    quantile(0.99)(Duration/1E6) as p99,
    hostname
FROM tbl
GROUP BY hostname ORDER BY 1 DESC LIMIT 50 FORMAT Null;

-- drop cache to run query cold
SYSTEM DROP FILESYSTEM CACHE;