Clickhouse ReplacingMergeTree new syntax (single partition)

gistfile1.txt

drop table replacing_test;

create table replacing_test(userid Int64, propery0 String, property1 Int8, deleted UInt8 default 0)
ENGINE = ReplacingMergeTree  ORDER BY (userid);

insert into replacing_test select  number, concat('propery',toString(number)), number+4,0 from system.numbers limit 500000000;
insert into replacing_test select  number, concat('propery',toString(number)), number+4,0 from system.numbers limit 5000;

OPTIMIZE table replacing_test PARTITION tuple() final;

select count(*) from replacing_test;
┌───count()─┐
│ 500005000 │
└───────────┘

1 rows in set. Elapsed: 0.628 sec. Processed 500.00 million rows, 500.00 MB (795.74 million rows/s., 795.74 MB/s.)


select count(*) from replacing_test final;
┌───count()─┐
│ 500000000 │
└───────────┘
1 rows in set. Elapsed: 41.151 sec. Processed 500.00 million rows, 4.50 GB (12.15 million rows/s., 109.36 MB/s.)


select count(*) from replacing_test prewhere userid =666;
┌─count()─┐
│       2 │
└─────────┘
1 rows in set. Elapsed: 0.016 sec. Processed 13.19 thousand rows, 105.54 KB (819.21 thousand rows/s., 6.55 MB/s.)


select count(*) from replacing_test final prewhere userid =666;
┌─count()─┐
│       1 │
└─────────┘
1 rows in set. Elapsed: 0.002 sec. Processed 13.19 thousand rows, 105.54 KB (8.07 million rows/s., 64.58 MB/s.)