tuple_vs_columns_perf

tuple_vs_columns_perf.sql

CREATE TABLE dw.Xtuple (x Int64, tf Tuple(Float64, Float64), f1 Float64, f2 Float64) 
ENGINE = MergeTree ORDER BY x;

insert into Xtuple 
select number, tuple(number/4554334 as x1, number/121142 as x2), x1, x2 from numbers (1000000000);

optimize table Xtuple final;


SELECT
    avg(tf.1),
    avg(tf.2)
FROM Xtuple;

┌─avg(tupleElement(tf, 1))─┬─avg(tupleElement(tf, 2))─┐
│        109.7855360410545 │        4127.387689653463 │
└──────────────────────────┴──────────────────────────┘
1 rows in set. Elapsed: 3.334 sec. Processed 1.00 billion rows, 16.00 GB (299.95 million rows/s., 4.80 GB/s.)


SELECT
    avg(f1),
    avg(f2)
FROM Xtuple;

┌───────────avg(f1)─┬───────────avg(f2)─┐
│ 109.7855360410545 │ 4127.387689653469 │
└───────────────────┴───────────────────┘
1 rows in set. Elapsed: 2.852 sec. Processed 1.00 billion rows, 16.00 GB (350.63 million rows/s., 5.61 GB/s.)



set max_threads=1;

SELECT
    avg(tf.1),
    avg(tf.2)
FROM Xtuple
┌─avg(tupleElement(tf, 1))─┬─avg(tupleElement(tf, 2))─┐
│       109.78553604105454 │        4127.387689653466 │
└──────────────────────────┴──────────────────────────┘
1 rows in set. Elapsed: 18.380 sec. Processed 1.00 billion rows, 16.00 GB (54.41 million rows/s., 870.52 MB/s.)

SELECT
    avg(f1),
    avg(f2)
FROM Xtuple
┌────────────avg(f1)─┬───────────avg(f2)─┐
│ 109.78553604105454 │ 4127.387689653466 │
└────────────────────┴───────────────────┘
1 rows in set. Elapsed: 18.750 sec. Processed 1.00 billion rows, 16.00 GB (53.33 million rows/s., 853.33 MB/s.)



SELECT avg(tf.1)
FROM Xtuple
┌─avg(tupleElement(tf, 1))─┐
│       109.78553604105454 │
└──────────────────────────┘
1 rows in set. Elapsed: 17.679 sec. Processed 1.00 billion rows, 16.00 GB (56.57 million rows/s., 905.04 MB/s.)

SELECT avg(f1)
FROM Xtuple
┌────────────avg(f1)─┐
│ 109.78553604105454 │
└────────────────────┘

1 rows in set. Elapsed: 9.273 sec. Processed 1.00 billion rows, 8.00 GB (107.84 million rows/s., 862.73 MB/s.)