Last active
September 20, 2018 11:16
-
-
Save filimonov/40c987677122e173b5c4f6a6906a03d0 to your computer and use it in GitHub Desktop.
clickhouse matview vs max_block_size
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE mat_vew_src | |
( | |
id UInt64, | |
number UInt64 | |
) | |
ENGINE = MergeTree | |
PARTITION BY tuple() | |
ORDER BY id | |
-- Ok. | |
-- 0 rows in set. Elapsed: 0.013 sec. | |
INSERT INTO mat_vew_src SELECT | |
intDiv(number, 200) AS id, | |
number | |
FROM numbers(100) | |
-- Ok. | |
-- 0 rows in set. Elapsed: 0.006 sec. | |
CREATE MATERIALIZED VIEW test_materialized_view | |
ENGINE = AggregatingMergeTree | |
PARTITION BY tuple() | |
ORDER BY id AS | |
SELECT | |
id, | |
countState() | |
FROM mat_vew_src | |
GROUP BY id | |
-- Ok. | |
-- 0 rows in set. Elapsed: 0.014 sec. | |
SELECT | |
id, | |
countMerge(`countState()`) | |
FROM test_materialized_view | |
GROUP BY id | |
-- Ok. | |
-- 0 rows in set. Elapsed: 0.008 sec. | |
INSERT INTO mat_vew_src SELECT | |
intDiv(number, 200) AS id, | |
number | |
FROM numbers(100) | |
-- Ok. | |
-- 0 rows in set. Elapsed: 0.008 sec. | |
SELECT | |
id, | |
countMerge(`countState()`) | |
FROM test_materialized_view | |
GROUP BY id | |
-- ┌─id─┬─countMerge(countState())─┐ | |
-- │ 0 │ 100 │ | |
-- └────┴──────────────────────────┘ | |
-- 1 rows in set. Elapsed: 0.006 sec. | |
SET max_block_size = 10000000 | |
-- Ok. | |
-- 0 rows in set. Elapsed: 0.000 sec. | |
INSERT INTO mat_vew_src SELECT | |
intDiv(number, 200) AS id, | |
number | |
FROM numbers(100) | |
-- Ok. | |
-- 0 rows in set. Elapsed: 0.003 sec. | |
SELECT | |
id, | |
countMerge(`countState()`) | |
FROM test_materialized_view | |
GROUP BY id | |
-- ┌─id─┬─countMerge(countState())─┐ | |
-- │ 0 │ 200 │ | |
-- └────┴──────────────────────────┘ | |
-- 1 rows in set. Elapsed: 0.003 sec. | |
INSERT INTO mat_vew_src SELECT | |
intDiv(number, 200) AS id, | |
number | |
FROM numbers(1000) | |
-- Ok. | |
-- 0 rows in set. Elapsed: 0.005 sec. Processed 1.00 thousand rows, 8.00 KB (188.71 thousand rows/s., 1.51 MB/s.) | |
SELECT | |
id, | |
countMerge(`countState()`) | |
FROM test_materialized_view | |
GROUP BY id | |
-- ┌─id─┬─countMerge(countState())─┐ | |
-- │ 0 │ 400 │ | |
-- │ 4 │ 200 │ | |
-- │ 3 │ 200 │ | |
-- │ 2 │ 200 │ | |
-- │ 1 │ 200 │ | |
-- └────┴──────────────────────────┘ | |
-- 5 rows in set. Elapsed: 0.004 sec. | |
INSERT INTO mat_vew_src VALUES (1,1) | |
-- Ok. | |
-- 1 rows in set. Elapsed: 0.008 sec. | |
SELECT | |
id, | |
countMerge(`countState()`) | |
FROM test_materialized_view | |
GROUP BY id | |
-- ┌─id─┬─countMerge(countState())─┐ | |
-- │ 0 │ 400 │ | |
-- │ 4 │ 200 │ | |
-- │ 3 │ 200 │ | |
-- │ 2 │ 200 │ | |
-- │ 1 │ 201 │ | |
-- └────┴──────────────────────────┘ | |
-- 5 rows in set. Elapsed: 0.003 sec. | |
-- ================== | |
DROP TABLE test_materialized_view | |
-- Ok. | |
-- 0 rows in set. Elapsed: 0.002 sec. | |
CREATE MATERIALIZED VIEW test_materialized_view | |
ENGINE = MergeTree | |
PARTITION BY tuple() | |
ORDER BY id AS | |
SELECT | |
id, | |
number | |
FROM mat_vew_src | |
-- Ok. | |
-- 0 rows in set. Elapsed: 0.009 sec. | |
SELECT * | |
FROM test_materialized_view | |
-- Ok. | |
-- 0 rows in set. Elapsed: 0.002 sec. | |
insert into mat_vew_src values (1,1); | |
-- Ok. | |
-- 1 rows in set. Elapsed: 0.004 sec. | |
SELECT * | |
FROM test_materialized_view | |
-- ┌─id─┬─number─┐ | |
-- │ 1 │ 1 │ | |
-- └────┴────────┘ | |
-- 1 rows in set. Elapsed: 0.002 sec. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment