clickhouse matview vs max_block_size

matview_block_size.sql

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.