在這項研究中，我想看看透過使用 ClickHouse 資料來源而不是 PostgreSQL 可以實現哪些效能改進。 我知道使用 ClickHouse 可以提高工作效率。 如果我使用外部資料包裝器 (FDW) 從 PostgreSQL 存取 ClickHouse，這些好處還會繼續嗎？
研究的資料庫環境是PostgreSQL v11、clickhousedb_fdw和ClickHouse資料庫。 最終，從 PostgreSQL v11 開始，我們將執行透過 clickhousedb_fdw 路由到 ClickHouse 資料庫的各種 SQL 查詢。 然後，我們將了解 FDW 的效能與在本機 PostgreSQL 和本機 ClickHouse 中執行的相同查詢相比如何。
Clickhouse資料庫
ClickHouse 是一種開源列式資料庫管理系統，其效能比傳統資料庫方法快 100-1000 倍，能夠在不到一秒的時間內處理超過 XNUMX 億行。
Clickhousedb_fdw
clickhousedb_fdw -ClickHouse 資料庫的外部資料包裝器（FDW）是 Percona 的一個開源專案。 .
.
正如您將看到的，這為 ClickHouse 提供了一個 FDW，允許從 PostgreSQL v11 伺服器中對 ClickHouse 資料庫進行 SELECT 和 INSERT INTO。
FDW 支援聚合和連接等進階功能。 透過使用遠端伺服器的資源來執行這些資源密集型操作，可以顯著提高效能。
基準環境
- 超微伺服器：
- 英特爾® 至強® CPU E5-2683 v3 @ 2.00GHz
- 2 個插槽/28 核/56 線程
- 內存：256GB RAM
- 儲存：三星 SM863 1.9TB 企業級 SSD
- 檔案系統：ext4/xfs
- 作業系統：Linux sblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL：版本 11
基準測試
我們沒有使用一些機器產生的資料集進行此測試，而是使用了 1987 年至 2018 年的「按時間報告操作員時間的生產力」資料。 您可以存取數據 .
資料庫大小為85 GB，提供一張109列的表格。
基準查詢
以下是我用來比較 ClickHouse、clickhousedb_fdw 和 PostgreSQL 的查詢。
Q#
查詢包含聚合和分組依據
Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q2
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q3
SELECT Origin, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10;
Q4
選擇運營商，計數（) 從準時開始，其中 DepDelay>10 AND Year = 2007 GROUP BY Carrier ORDER BY count() 降序；
Q5
SELECT a.Carrier, c, c2, c1000/c2as c3 FROM ( 選擇運營商, 計數() AS c FROM ontime WHERE DepDelay>10 AND Year=2007 GROUP BY Carrier ) a INNER JOIN ( SELECT Carrier,count(*) AS c2 FROM ontime WHERE Year=2007 GROUP BY Carrier)b on aCarrier.按c3 描述；
Q6
SELECT a.Carrier, c, c2, c1000/c2as c3 FROM ( 選擇運營商, 計數() AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Carrier) a INNER JOIN ( SELECT Carrier, count(*) AS c2 FROM ontime WHERE Year >= 2000 AND Year.承運人) b on a.Carrier=b.Carrier ORDER BY c2008 DESC;
Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Carrier；
Q8
SELECT Year, avg(DepDelay) FROM ontime GROUP BY Year；
Q9
select Year, count(*) as c1 from ontime group by Year；
Q10
SELECT avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Month) a;
Q11
select avg(c1) from (select Year,Month,count(*) as c1 from ontime group by Year,Month) a;
Q12
SELECT OriginCityName, DestCityName, count(*) AS c FROM ontime GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;
Q13
SELECT OriginCityName, count(*) AS c FROM ontime GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;
查詢包含連接
Q14
SELECT a.Year, c1/c2 FROM ( 選擇 Year, count()1000 as c1 from ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (選擇 Year, count(*) as c2 from ontime GROUP BY Year ) b on a.Year=b.Year ORDER BY a.Year;
Q15
SELECT a.「年份」, c1/c2 FROM ( 選擇「年份」, count()1000 as c1 FROM fonttime WHERE “DepDelay”>10 GROUP BY “Year”) a INNER JOIN（選擇“Year”，count(*) as c2 FROM fonttime GROUP BY “Year”）b on a.”Year”=b。 「年」;
表 1：基準測試中使用的查詢
查詢執行
以下是在不同資料庫設定中執行時每個查詢的結果：帶有索引和不含索引的 PostgreSQL、本機 ClickHouse 和 clickhousedb_fdw。 時間以毫秒為單位顯示。
Q#
PostgreSQL的
PostgreSQL（索引）
點擊之家
clickhousedb_fdw
Q1
27920
19634
23
57
Q2
35124
17301
50
80
Q3
34046
15618
67
115
Q4
31632
7667
25
37
Q5
47220
8976
27
60
Q6
58233
24368
55
153
Q7
30566
13256
52
91
Q8
38309
60511
112
179
Q9
20674
37979
31
81
Q10
34990
20102
56
148
Q11
30489
51658
37
155
Q12
39357
33742
186
1333
Q13
29912
30709
101
384
Q14
54126
39913
124
1364212
Q15
97258
30211
245
259
表 1：執行基準測試中所使用的查詢所花費的時間
查看結果
此圖顯示以毫秒為單位的查詢執行時間，X 軸顯示上表中的查詢編號，Y 軸顯示以毫秒為單位的執行時間。 顯示了 ClickHouse 結果和使用 clickhousedb_fdw 從 postgres 檢索的資料。 從表中可以看出，PostgreSQL 和 ClickHouse 之間存在著巨大差異，但 ClickHouse 和 clickhousedb_fdw 之間差異很小。
此圖顯示了 ClickhouseDB 和 clickhousedb_fdw 之間的差異。 在大多數查詢中，FDW 開銷並沒有那麼高，除了 Q12 之外幾乎不顯著。 此查詢包括聯結和 ORDER BY 子句。 由於 ORDER BY GROUP/BY 子句，ORDER BY 不會下拉到 ClickHouse。
在表 2 中，我們看到查詢 Q12 和 Q13 中的時間跳躍。 同樣，這是由 ORDER BY 子句引起的。 為了確認這一點，我執行了有或沒有 ORDER BY 子句的查詢 Q-14 和 Q-15。 如果沒有 ORDER BY 子句，完成時間為 259 毫秒，而使用 ORDER BY 子句則為 1364212 毫秒。為了偵錯此查詢，我將解釋這兩個查詢，以下是解釋的結果。
Q15：沒有 ORDER BY 子句
bm=# EXPLAIN VERBOSE SELECT a."Year", c1/c2
FROM (SELECT "Year", count(*)*1000 AS c1 FROM fontime WHERE "DepDelay" > 10 GROUP BY "Year") a
INNER JOIN(SELECT "Year", count(*) AS c2 FROM fontime GROUP BY "Year") b ON a."Year"=b."Year";Q15：不使用 ORDER BY 子句的查詢
QUERY PLAN
Hash Join (cost=2250.00..128516.06 rows=50000000 width=12)
Output: fontime."Year", (((count(*) * 1000)) / b.c2)
Inner Unique: true Hash Cond: (fontime."Year" = b."Year")
-> Foreign Scan (cost=1.00..-1.00 rows=100000 width=12)
Output: fontime."Year", ((count(*) * 1000))
Relations: Aggregate on (fontime)
Remote SQL: SELECT "Year", (count(*) * 1000) FROM "default".ontime WHERE (("DepDelay" > 10)) GROUP BY "Year"
-> Hash (cost=999.00..999.00 rows=100000 width=12)
Output: b.c2, b."Year"
-> Subquery Scan on b (cost=1.00..999.00 rows=100000 width=12)
Output: b.c2, b."Year"
-> Foreign Scan (cost=1.00..-1.00 rows=100000 width=12)
Output: fontime_1."Year", (count(*))
Relations: Aggregate on (fontime)
Remote SQL: SELECT "Year", count(*) FROM "default".ontime GROUP BY "Year"(16 rows)Q14：使用 ORDER BY 子句進行查詢
bm=# EXPLAIN VERBOSE SELECT a."Year", c1/c2 FROM(SELECT "Year", count(*)*1000 AS c1 FROM fontime WHERE "DepDelay" > 10 GROUP BY "Year") a
INNER JOIN(SELECT "Year", count(*) as c2 FROM fontime GROUP BY "Year") b ON a."Year"= b."Year"
ORDER BY a."Year";Q14：使用 ORDER BY 子句的查詢計劃
QUERY PLAN
Merge Join (cost=2.00..628498.02 rows=50000000 width=12)
Output: fontime."Year", (((count(*) * 1000)) / (count(*)))
Inner Unique: true Merge Cond: (fontime."Year" = fontime_1."Year")
-> GroupAggregate (cost=1.00..499.01 rows=1 width=12)
Output: fontime."Year", (count(*) * 1000)
Group Key: fontime."Year"
-> Foreign Scan on public.fontime (cost=1.00..-1.00 rows=100000 width=4)
Remote SQL: SELECT "Year" FROM "default".ontime WHERE (("DepDelay" > 10))
ORDER BY "Year" ASC
-> GroupAggregate (cost=1.00..499.01 rows=1 width=12)
Output: fontime_1."Year", count(*) Group Key: fontime_1."Year"
-> Foreign Scan on public.fontime fontime_1 (cost=1.00..-1.00 rows=100000 width=4)
Remote SQL: SELECT "Year" FROM "default".ontime ORDER BY "Year" ASC(16 rows)產量
這些實驗的結果顯示 ClickHouse 提供了非常好的效能，而 clickhousedb_fdw 提供了 PostgreSQL 中 ClickHouse 的效能優勢。 雖然使用 clickhousedb_fdw 時會產生一些開銷，但它可以忽略不計，並且與在 ClickHouse 資料庫上本地運行所獲得的效能相當。 這也證實了 PostgreSQL 中的 fdw 提供了出色的結果。
透過 Clickhouse 進行 Telegram 聊天
使用 PostgreSQL 進行電報聊天
來源： www.habr.com