á€áá±á·áá¬ááŸá¯ááœááºá PostgreSQL ááẠClickHouse áá±áá¬áááºážááŒá áºááᯠá¡áá¯á¶ážááŒá¯ááŒááºážááŒáá·áº á áœááºážáá±á¬ááºááẠááŒáŸáá·áºáááºááŸá¯áá»á¬ážááᯠáá»áœááºá¯ááºááœá±á·ááŒááºááá¯áá«áááºá ClickHouse ááᯠá¡áá¯á¶ážááŒá¯ááŒááºážá០áááŸááá±á¬ áá¯ááºáá¯ááºá áœááºážá¡á¬áž á¡áá»áá¯ážáá»á±ážáá°ážáá»á¬ážááᯠáá»áœááºá¯áẠáááá«áááºá Foreign Data Wrapper (FDW) ááᯠá¡áá¯á¶ážááŒá¯á PostgreSQL á០ClickHouse ááá¯áááºáá±á¬ááºáá«á á€á¡áá»áá¯ážáá¶á á¬ážááœáá·áºáá»á¬áž áááºáááºáááŸááá«áááºáá¬ážá
áá±á·áá¬áá²á·ááá·áº áá±áá¬áá±á·á áºáááºáááºážáá»ááºáá»á¬ážááẠPostgreSQL v11á clickhousedb_fdw ááŸáá·áº ClickHouse database ááá¯á·ááŒá áºáááºá áá±á¬ááºáá¯á¶ážááœááºá PostgreSQL v11 á០áá»áœááºá¯ááºááá¯á·ááẠáá»áœááºá¯ááºááá¯á·á clickhousedb_fdw ááŸáááá·áº ClickHouse áá±áá¬áá±á·á áºááá¯á· ááŒááºáááºážááœá¬ážáá±á¬ SQL Queries á¡áá»áá¯ážáá»áá¯ážááᯠáá¯ááºáá±á¬ááºáá«áááºá ááá¯á·áá±á¬ááºááœáẠFDW á á áœááºážáá±á¬ááºáááºááẠáá°áááºáž PostgreSQL ááŸáá·áº áá°áááºáž ClickHouse ááœáẠáá¯ááºáá±á¬ááºáá±ááá·áº áá°áá®áá±á¬ áá±ážááœááºážáá»á¬ážááŸáá·áº ááŸáá¯ááºážááŸááºááŒáá·áºááẠááŒá áºáááºá
Clickhouse áá±áá¬áá±á·á áº
ClickHouse ááẠááá¬ážááá¯ážáá» áá±áá¬áá±á·á áºáá»ááºážáááºááŸá¯áá»á¬ážááẠá¡á 100-1000 ááá¯ááá¯ááŒááºáááºáá±á¬ á áœááºážáá±á¬ááºáááºááᯠáááŸáááá¯ááºááá·áº ááœáá·áºáááºážáááºážááŒá Ạáá±á¬áºáá¶áá¬áž áá±áá¬áá±á·á Ạá á®áá¶ááá·áºááœá²ááŸá¯á áá áºáá áºáá¯ááŒá áºááŒá®áž áá áºá áá¹ááá·áºá¡ááœááºáž á¡áááºážáá áºáá®áá®áá¶áá»á±á¬áºááᯠáá¯ááºáá±á¬ááºááá¯ááºá áœááºážááŸááááºá
Clickhousedb_fdw
clickhousedb_fdw - ClickHouse áá±áá¬áá±á·á Ạááá¯á·ááá¯áẠFDW á¡ááœáẠááŒááºááá±áᬠáá¯ááºááá¯ážááŸá¯ááẠPercona á០á¡áááºážá¡ááŒá áºááá±á¬áá»ááºáá áºáá¯ááŒá áºáááºá .
.
áááºááœá±á·ááŒááºáááá·áºá¡ááá¯ááºážá áááºážááẠPostgreSQL v11 áá¬áá¬á០ClickHouse áá±áá¬áá±á·á áºááᯠSELECT ááŸááœáá·áºááŒá¯ááŒá®áž INTO áá²ááá¯á· ááá·áºááœááºážááẠClickHouse á¡ááœáẠFDW ááᯠáá¶á·ááá¯ážáá±ážáá«áááºá
FDW ááẠá¡á á¯ááá¯ááºááŸáá·áº áá«áááºááŒááºážáá²á·ááá¯á·áá±á¬ á¡ááá·áºááŒáá·áºá¡ááºá¹áá«áááºáá»á¬ážááᯠáá¶á·ááá¯ážáá±ážáááºá áááºážááẠá¡áááºážá¡ááŒá áº-á¡áá±ážáá±ážáá¯ááºáá±á¬ááºááŸá¯áá»á¬ážá¡ááœáẠá¡áá±ážááááºážáá¬áá¬á á¡áááºážá¡ááŒá áºáá»á¬ážááᯠá¡áá¯á¶ážááŒá¯ááŒááºážááŒáá·áº á áœááºážáá±á¬ááºáááºááᯠáááááá¬áᬠááá¯ážáááºá á±áááºá
á á¶áááºááŸááºáá»ááºáááºáááºážáá»ááº
- á
á°áá«ááá¯ááºáááá¯áá¬áá¬-
- Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
- 2 sockets / 28 cores / 56 threads
- Memory: 256GB RAM
- ááá¯ááŸá±á¬ááºááŸá¯- Samsung SM863 1.9TB Enterprise SSD
- ááá¯ááºá áá áº- ext4/xfs
- OS- Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL- áá¬ážááŸááºáž 11
á á¶ááŸá¯ááºážá á áºáá±ážááŸá¯áá»á¬áž
á€á ááºážáááºááŸá¯á¡ááœáẠá ááºááŸáá¯ááºáá¯ááºáá¬ážáá±á¬ áá±áá¬á¡áá»áá¯á·ááᯠá¡áá¯á¶ážááŒá¯ááá·áºá¡á á¬ážá áá»áœááºá¯ááºááá¯á·ááẠ"á¡áá»áááºá¡ááá¯áẠá¡á á®áááºáá¶áá¬ážáá±á¬ á¡á±á¬áºááá±áá¬á¡áá»áááºá¡ááá¯áẠáá¯ááºáá¯ááºááŸá¯á áœááºážá¡á¬áž" áá±áá¬ááᯠ1987 á០2018 á¡áá á¡áá¯á¶ážááŒá¯áá²á·áááºá áá±áá¬ááá¯áááºáááºáá±á¬ááºááá¯ááºáááºá .
áá±áá¬áá±á·á áºá¡ááœááºá¡á á¬ážááŸá¬ 85 GB ááŒá áºááŒá®áž ááá¬ážáá áºáá¯á¡á¬áž áá±á¬áºáᶠ109 áᯠáá±ážáá±á¬ááºáááºá
á á¶ááŒáá±ážááœááºážáá»á¬áž
á€áááºááŸá¬ ClickHouseá clickhousedb_fdw ááŸáá·áº PostgreSQL ááá¯á·ááᯠááŸáá¯ááºážááŸááºááẠá¡áá¯á¶ážááŒá¯áá²á·áá±á¬ áá±ážááœááºážáá»á¬ážááŒá áºáááºá
Q#
Query ááœáẠá¡á
á¯ááá¯ááºááŸáá·áº á¡á¯ááºá
á¯á¡ááá¯áẠáá«ááŸááááºá
Q1
SELECT DayOfWeeká count(*) as c á០á¡áá»áááºááŸááºááŸá
áºá
áẠ>= 2000 ááŸáá·áº Year <= 2008 Group BY DayOfWeek ORDER BY c DESC;
Q2
DayOfWeek ááá¯ááœá±ážáá»ááºáá«á count(*) as c á០á¡áá»áááºááŸáẠDepDelay>10 ááŸáá·áº Year >= 2000 ááŸáá·áº Year <= 2008 Group BY DayOfWeek Order BY c DESC;
Q3
áá¬á
áºááŒá
áºááᯠSELECTá count(*) á¡ááŒá
Ạc á¡áá»áááºáá±á«áºáá±áá¬á០DepDelay>10 ááŸáá·áº Year >= 2000 ááŸáá·áº Year <= 2008 áá°áááºážááŸá¬áá°ááŸá¯á¡ááá¯áẠá¡á¯ááºá
á¯ááœá²á· c DESC LIMIT 10;
Q4
SELECT áááºáá±á¬ááºááŸá¯áá±ážáá°á áá±ááœááº() á¡áá»áááºááŸááºáá±áá¬á០DepDelay> 10 ááŸáá·áº Year = 2007 áááºáá®áᬠááŸá¬áá°ááŸá¯á¡ááá¯áẠá¡á¯ááºá
á¯ááœá²á·ááŒááºáž() DESC;
Q5
a.Carrierácác2ác ááá¯ááœá±ážáá«á1000/c2 á០c3 á¡ááŒá
Ạ( SELECT Carrier á count() á¡áá»áááºáá±á«áºá¡áá»áááºááŸá
ááá·áºá¡ááá¯ááºáž c10 WHERE WHERE Year=2007 AND Year=2 GROUP BY Carrier ) áá¡ááœááºážááá¯ááºážáá°ážáá±á«ááºážááŸá¯áá
áºáᯠ( SELECT Carrierácount(*) AS c2007 AS ontime WHERE Year=3 áááºáá®áá¬ááŒáá·áºá¡ááœá²á·ááá¯ááº)b ááœáẠa.Carrier=b.Carrier cXNUMX ááŒáá·áº DESC;
Q6
a.Carrierácác2ác ááá¯ááœá±ážáá«á1000/c2 á០c3 á¡ááŒá
Ạ( SELECT Carrier á count() á¡áá»áááºáá±á«áºá¡áá»áááºááŸá
áááºááá·áºá¡ááá¯ááºáž C ááẠDepDelay>10 ááŸáá·áº Year >= 2000 ááŸáá·áº Year <= 2008 GROUP BY Carrier) áá
áºáŠážá¡ááœááºážááá¯ááºážáá°ážáá±á«ááºážááŒááºáž ( SELECT Carrier, count(*) AS c2 AS on time WHERE Year >= 2000 ááŸáá·áº Year <= 2008 ááŸáá·áº Year <= 3 áááºáá±á¬ááºááŸá¯áá±ážáá° ) b on a.Carrier=b.Carrier ORDER BY cXNUMX DESC;
Q7
SELECT áááºáá±á¬ááºááŸá¯áá±ážáá°á avg(DepDelay) * 1000 AS c3 ááẠá¡áá»áááºááŸáá·áºáááŒá±ážáá® áááºááá·áºáá±áá¬á០ááŸá
Ạ>= 2000 ááŸáá·áº ááŸá
Ạ<= 2008 áááºáá®áá¬á¡ááœá²á·ááŒáá·áº á¡ááœá²á·ááá¯ááºá
Q8
SELECT Yeará avg(DepDelay) á¡áá»áááºáá±á«áºá¡á¯ááºá
á¯á¡ááá¯áẠááŸá
áºá¡ááá¯ááºá
Q9
ááŸá
áºá¡ááá¯áẠá¡áá»áááºááŸááºá¡á¯ááºá
á¯á០áá
áºááŸá
áºá áá±ááœááº(*) ááᯠááœá±ážáá»ááºáá«á
Q10
SELECT avg(cnt) á០(SELECT YearáMonth,count(*) AS cnt AS cnt á០á¡áá»áááºááŸááºáá±áá¬á០DepDel15=1 á¡á¯ááºá
á¯á¡ááá¯áẠáá
áºááŸá
áºá á) a;
Q11
a;
Q12
OriginCityNameá DestCityNameá count(*) á¡ááá¯ááºáž c ááᯠáá°áááºážááŒáá¯á·á¡áááºááŒáá·áº á¡áá»áááºáá±á«áºá¡á¯ááºá
á¯á DestCityName ááŸááŸá¬áá°ááŸá¯ c DESC ááá·áºáááºáá»áẠ10;
Q13
OriginCityName ááᯠSELECTá count(*) á¡áá±ááŒáá·áº c á០á¡áá»áááºáá® á¡á¯ááºá
á¯ááœá²á·ááŒááºáž áá°áááºážááŒáá¯á·á¡ááẠááŸá¬áá°ááŸá¯ c DESC LIMIT 10;
Query ááœáẠJoins áá»á¬ážáá«ááŸááááºá
Q14
SELECT a.Yeará c1/c2 FROM (ááŸá
áºááá¯ááœá±ážáá«á áá±ááœááºáá«()1000 á¡áá»áááºáá±á«áºá០C1 á¡ááŒá
Ạ10 WHERE DepDelay>2 GROUP BY Year) a Inner Join (áá
áºááŸá
áºááᯠááœá±ážáá«á á¡áá»áááºá¡ááá¯áẠGROUP BY Year á០cXNUMX á¡ááŒá
ẠcXNUMX á¡ááŒá
áº) b on a.Year=b.Year ááŸá¬áá±á¬ a.Year;
Q15
ááœá±áž aâYearâá c1/c2 á០(âááŸá
áºâ ááá¯ááœá±ážáá«á áá±ááœááºáá«()1000 á¡ááŒá
Ạc1 áá²á០âDepDelayâ> âYearâ á¡ááá¯áẠá¡á¯ááºá
ᯠ10) á¡ááœááºážááá¯ááºáž Join (âYearâ ááá¯ááœá±ážáá«á count(*) á¡ááŒá
Ạc2 á¡ááŒá
Ạfontime GROUP BY âYearâ) b on a.âYearâ=bá "ááŸá
áº";
ááá¬áž-á- á á¶áááºááŸááºáá»ááºááœáẠá¡áá¯á¶ážááŒá¯áá¬ážáá±á¬ áá±ážááœááºážáá»á¬áž
áá±ážááœááºážááᯠááœááºáá»ááºááŒááºážá
ááá°áá®áá±á¬áá±áá¬áá±á·á áºáááºáááºáá»á¬ážááœáẠrun áá±á¬á¡áá«áá±ážááœááºážáá áºáá¯á á®áááááºáá»á¬ážááŒá áºáááº- 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
ááá¬áž-á- á á¶áááºááŸááºááŸá¯ááœáẠá¡áá¯á¶ážááŒá¯ááá·áº áá±ážááœááºážáá»á¬ážááᯠáá¯ááºáá±á¬ááºááẠá¡áá»áááºáá°áááºá
ááááºáá»á¬ážááá¯ááŒáá·áºáá«á
ááááºááẠáá±ážááŒááºážááŸá¯áá¯ááºáá±á¬ááºáá»áááºááᯠáá®áá®á áá¹ááá·áºáá»á¬ážááŒáá·áºááŒááááºá X áááºááá¯ážááẠá¡áááºáá«ááá¬ážáá»á¬ážá០á á¯á¶á ááºážááŸá¯áá¶áá«ááºááá¯ááŒáááŒá®áž Y áááºááá¯ážááẠáá¯ááºáá±á¬ááºáá»áááºááᯠáá®áá®á áá¹ááá·áºááŒáá·áºááŒááááºá clickhousedb_fdw ááᯠá¡áá¯á¶ážááŒá¯á postgres á០ááá°áá¬ážáá±á¬ ClickHouse ááááºáá»á¬ážááŸáá·áº áá±áá¬áá»á¬ážááᯠááŒááá¬ážáááºá ááá¬ážá០PostgreSQL ááŸáá·áº ClickHouse á¡ááŒá¬áž ááŒá®ážáá¬ážáá±á¬ ááŒá¬ážáá¬ážáá»áẠááŸááááºááᯠáááºááœá±á·ááá¯ááºáááºá ááá¯á·áá±á¬áº ClickHouse ááŸáá·áº clickhousedb_fdw á¡ááŒá¬áž á¡áááºážáááºáᬠááœá¬ááŒá¬ážáá»ááºááŸááááºá
á€ááááºááẠClickhouseDB ááŸáá·áº clickhousedb_fdw á¡ááŒá¬áž ááŒá¬ážáá¬ážáá»ááºááᯠááŒááááºá áá±ážááœááºážá¡áá»á¬ážá á¯ááœááºá FDW áá¡áááºááá¯ááºážááẠááŒáá·áºáá¬ážáááºááá¯ááºáá±á Q12 ááŸááœá²á áááá¬áááºááŸá¬ážááŸá¯áááŸááá«á á€áá±ážááŒááºážáá»ááºááœáẠáá«áááºááŸá¯áá»á¬ážááŸáá·áº ORDER BY clause áá«áááºáááºá ORDER BY GROUP/BY á¡ááá¯ááºááŒá±á¬áá·áºá ORDER BY ááẠClickHouse ááá¯á· ááááºážáá«á
ááá¬áž 2 ááœáẠáá±ážááœááºážáá»á¬áž Q12 ááŸáá·áº Q13 ááœáẠá¡áá»áááºáá¯ááºáá±áááºááᯠááœá±á·ááá«áááºá áááẠá€áááºááŸá¬ ORDER BY clause ááŒá±á¬áá·áºááŒá áºáááºá áááºážááá¯á¡áááºááŒá¯áááºá áá»áœááºá¯ááºááẠQ-14 ááŸáá·áº Q-15 ááá¯á·ááᯠORDER BY clause ááá«áá² á á¯á¶á ááºážáá±ážááŒááºážáá¬ážáá«áááºá ORDER BY clause ááá«áá² ááŒá®ážáá¯á¶ážáá»áááºááẠ259ms ááŒá áºááŒá®áž ORDER BY clause ááŒáá·áº áááºážááẠ1364212 ááŒá áºáááºá á€áá±ážááœááºážááᯠá¡ááŸá¬ážááŸá¬áááºá¡ááœáẠáá»áœááºá¯ááºááẠáá±ážááœááºážááŸá áºáá¯áá¯á¶ážááᯠááŸááºážááŒááŒá®áž á€ááœáẠááŸááºážáááºážáá»ááºá ááááºáá»á¬ážááŒá áºáááºá
Q15: á¡ááá¯ááºá¡ááá¯áẠááŸá¬áá°ááŸá¯áááŸááá²
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- á¡ááá¯ááºá¡ááá¯áẠááŸá¬áá°ááŸá¯áááŸááá² áá±ážááŒááºážááŸá¯
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- á¡ááá¯ááºá¡ááá¯áẠááŸá¬áá°ááŸá¯ááŒáá·áº áá±ážááŒááºážááŸá¯
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 Clause ááŒáá·áº áá±ážááŒááºážááŸá¯ á¡á á®á¡á ááº
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 chat
PostgreSQL ááᯠá¡áá¯á¶ážááŒá¯á Telegram áá»ááº
source: www.habr.com