ClickHouse Performance Test on a 600‑Million‑Row Table

Server Information

CPU: Intel Xeon Gold 6240 @ 8×2.594 GHz

Memory: 32 GB

OS: CentOS 7.6

Linux kernel: 3.10.0

Disk: Mechanical HDD

File system: ext4

ClickHouse Information

Deployment: Single‑node

Version: 20.8.11.17

Test Data and Space Usage

Test data were generated using ClickHouse’s official Star Schema Benchmark (https://clickhouse.com/docs/en/getting-started/example-datasets/star-schema/). The table lineorder_flat holds 600 million rows. Compression ratios exceed 50 % and can reach around 70 %, significantly reducing disk usage and improving I/O performance.

The schema follows a simple supplier‑customer‑order‑region star model; lineorder_flat merges these relationships into a wide table, allowing all analytical queries to run without joins.

Query Performance Details

Query 1.1

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM lineorder_flat
WHERE (toYear(LO_ORDERDATE) = 1993)
  AND ((LO_DISCOUNT >= 1) AND (LO_DISCOUNT <= 3))
  AND (LO_QUANTITY < 25)

Rows processed: 91,010,000. Elapsed: 0.242 s. Processed 728.06 MB (375.91 M rows/s, 3.01 GB/s). Result: revenue 44,652,567,249,651.

Query 1.2

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM lineorder_flat
WHERE (toYYYYMM(LO_ORDERDATE) = 199401)
  AND ((LO_DISCOUNT >= 4) AND (LO_DISCOUNT <= 6))
  AND ((LO_QUANTITY >= 26) AND (LO_QUANTITY <= 35))

Rows processed: 7,750,000. Elapsed: 0.040 s. Processed 61.96 MB (191.44 M rows/s, 1.53 GB/s). Result: revenue 9,624,332,170,119.

Query 2.1

SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRAND
FROM lineorder_flat
WHERE (P_CATEGORY = 'MFGR#12') AND (S_REGION = 'AMERICA')
GROUP BY year, P_BRAND
ORDER BY year ASC, P_BRAND ASC

Rows scanned: 600,040,000. Elapsed: 8.558 s. Processed 6.20 GB (70.11 M rows/s, 725.04 MB/s). Result: 280 rows.

Query 2.2

SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRAND
FROM lineorder_flat
WHERE (P_BRAND >= 'MFGR#2221') AND (P_BRAND <= 'MFGR#2228')
  AND (S_REGION = 'ASIA')
GROUP BY year, P_BRAND
ORDER BY year ASC, P_BRAND ASC

Rows scanned: 600,040,000. Elapsed: 1.242 s. Processed 5.60 GB (482.97 M rows/s, 4.51 GB/s). Result: 56 rows.

Query 3.1

SELECT C_NATION, S_NATION, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue
FROM lineorder_flat
WHERE (C_REGION = 'ASIA') AND (S_REGION = 'ASIA')
  AND (year >= 1992) AND (year <= 1997)
GROUP BY C_NATION, S_NATION, year
ORDER BY year ASC, revenue DESC

Rows scanned: 546,670,000. Elapsed: 3.533 s. Processed 5.48 GB (154.72 M rows/s, 1.55 GB/s). Result: 150 rows.

Query 3.2

SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue
FROM lineorder_flat
WHERE (C_NATION = 'UNITED STATES') AND (S_NATION = 'UNITED STATES')
  AND (year >= 1992) AND (year <= 1997)
GROUP BY C_CITY, S_CITY, year
ORDER BY year ASC, revenue DESC

Rows scanned: 546,670,000. Elapsed: 1.000 s. Processed 5.56 GB (546.59 M rows/s, 5.56 GB/s). Result: 600 rows.

Query 4.1

SELECT toYear(LO_ORDERDATE) AS year, C_NATION,
       sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM lineorder_flat
WHERE (C_REGION = 'AMERICA') AND (S_REGION = 'AMERICA')
  AND ((P_MFGR = 'MFGR#1') OR (P_MFGR = 'MFGR#2'))
GROUP BY year, C_NATION
ORDER BY year ASC, C_NATION ASC

Rows scanned: 600,040,000. Elapsed: 5.066 s. Processed 8.41 GB (118.43 M rows/s, 1.66 GB/s). Result: 35 rows.

Query 4.2

SELECT toYear(LO_ORDERDATE) AS year, S_NATION, P_CATEGORY,
       sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM lineorder_flat
WHERE (C_REGION = 'AMERICA') AND (S_REGION = 'AMERICA')
  AND (year = 1997 OR year = 1998)
  AND ((P_MFGR = 'MFGR#1') OR (P_MFGR = 'MFGR#2'))
GROUP BY year, S_NATION, P_CATEGORY
ORDER BY year ASC, S_NATION ASC, P_CATEGORY ASC

Rows scanned: 144,420,000. Elapsed: 0.826 s. Processed 2.17 GB (174.78 M rows/s, 2.63 GB/s). Result: 100 rows.

Summary of Results

In the tested hardware/software environment, scanning over 600 million rows takes roughly 8 seconds for a typical analytical query on its first execution. Subsequent executions of the same logical query drop to about 1 second, while simple column‑only scans complete within 2 seconds.