{"id":89498,"date":"2020-07-23T13:42:07","date_gmt":"2020-07-23T11:42:07","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/testirovanie-proizvoditelnosti-analiticheskih-zaprosov-v-postgresql-clickhouse-i-clickhousedb_fdw-postgresql"},"modified":"2020-07-23T13:42:07","modified_gmt":"2020-07-23T11:42:07","slug":"testirovanie-proizvoditelnosti-analiticheskih-zaprosov-v-postgresql-clickhouse-i-clickhousedb_fdw-postgresql","status":"publish","type":"post","link":"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/testirovanie-proizvoditelnosti-analiticheskih-zaprosov-v-postgresql-clickhouse-i-clickhousedb_fdw-postgresql","title":{"rendered":"Anal\u00fc\u00fctiliste p\u00e4ringute j\u00f5udluse testimine PostgreSQL-is, ClickHouse-is ja clickhousedb_fdw-s (PostgreSQL)","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>Selles uuringus soovisin uurida, milliseid j\u00f5udlusparandusi on v\u00f5imalik saavutada andmeallika ClickHouse'i kasutamisel PostgreSQL-i asemel. Ma tean, milliseid j\u00f5udlusv\u00f5lusid saan ClickHouse'i kasutamisel. Kas need eelised p\u00fcsivad, kui p\u00e4\u00e4sen ClickHouse'ile PostgreSQL'i kaudu v\u00e4lise andmek\u00e4ivituse (FDW) abil? <\/p>\n<p><noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<p>Uuritavad andmebaasid on PostgreSQL v11, clickhousedb_fdw ja ClickHouse andmebaas. L\u00f5ppkokkuv\u00f5ttes k\u00e4ivitame PostgreSQL v11-st erinevaid SQL-p\u00e4ringuid, mis suunatakse meie clickhousedb_fdw kaudu ClickHouse andmebaasi. Seej\u00e4rel vaatame, kuidas FDW j\u00f5udlus v\u00f5rreldes sama p\u00e4ringuga, mida teostatakse natiivses PostgreSQL-is ja natiivses ClickHouse'is.<\/p>\n<p><\/p>\n<h3 id=\"baza-dannyh-clickhouse\">ClickHouse andmebaas<\/h3>\n<p><\/p>\n<p>ClickHouse on avatud l\u00e4htekoodiga veergude p\u00f5hine andmebaasi haldamise s\u00fcsteem, mis v\u00f5ib saavutada j\u00f5udluse, mis on 100\u20131000 korda kiirem kui traditsioonilised andmebaasi l\u00e4henemised, suudab t\u00f6\u00f6delda \u00fcle miljardi rida v\u00e4hem kui sekundiga.<\/p>\n<p><\/p>\n<h3 id=\"clickhousedb_fdw\">Clickhousedb_fdw<\/h3>\n<p><\/p>\n<p>clickhousedb_fdw on ClickHouse andmebaasi v\u00e4line andmek\u00e4ivituse (FDW) jahutustooted, avatud l\u00e4htekoodiga projekt, mille on loonud Percona. <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/Percona-Lab\/clickhousedb_fdw\">Siin on link projekti GitHubi hoidlatele<\/a><\/noindex>.<\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/www.percona.com\/blog\/2019\/03\/29\/postgresql-access-clickhouse-one-of-the-fastest-column-dbmss-with-clickhousedb_fdw\/\">M\u00e4rtsis kirjutasin blogi, mis r\u00e4\u00e4gib rohkem meie FDW-st<\/a><\/noindex>.<\/p>\n<p><\/p>\n<p>Nagu n\u00e4ete, pakub see FDW ClickHouse'ile, mis v\u00f5imaldab SELECT andmeid ja INSERT andmeid PostgreSQL v11-serverist ClickHouse andmebaasi.<\/p>\n<p><\/p>\n<p>FDW toetab t\u00e4iustatud funktsioone, nagu aggregate ja join. See suurendab m\u00e4rkimisv\u00e4\u00e4rselt j\u00f5udlust, kasutades kaugs\u00fcsteemi ressursse nende ressursimahukate operatsioonide jaoks.<\/p>\n<p><\/p>\n<h3 id=\"benchmark-environment\">Benchmark keskkond<\/h3>\n<p><\/p>\n<ul>\n<li>Supermicro server:\n<ul>\n<li>Intel&reg; Xeon&reg; CPU E5-2683 v3 @ 2.00GHz<\/li>\n<li>2 pesa \/ 28 tuuma \/ 56 l\u00f5ime<\/li>\n<li>M\u00e4lu: 256GB RAM-i<\/li>\n<li>Salvestus: Samsung SM863 1.9TB ettev\u00f5tte SSD<\/li>\n<li>Failis\u00fcsteem: ext4\/xfs<\/li>\n<\/ul>\n<\/li>\n<li>OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu<\/li>\n<li>PostgreSQL: versioon 11<\/li>\n<\/ul>\n<p><\/p>\n<h3 id=\"benchmark-tests\">Benchmark testid<\/h3>\n<p><\/p>\n<p>Kuna ei kasutanud mingit masinaga genereeritud andmestikku, kasutasime \u00fchte andmestikku \u201eAja j\u00f5udlus, mida operaatori t\u00f6\u00f6aja raportid\u201d aastatel 1987-2018. Andmetele p\u00e4\u00e4sete ligi <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/Percona-Lab\/ontime-airline-performance\/blob\/master\/download.sh\">meie skripti kaudu, mis on saadaval siin<\/a><\/noindex>.<\/p>\n<p><\/p>\n<p>Andmebaasi suurus on 85 GB, pakkudes \u00fchte tabelit 109 veerust.<\/p>\n<p><\/p>\n<h4 id=\"benchmark-queries\">Benchmark p\u00e4ringud<\/h4>\n<p><\/p>\n<p>Siin on p\u00e4ringud, mida kasutasin ClickHouse'i, clickhousedb_fdw ja PostgreSQL-i v\u00f5rdlemiseks.<\/p>\n<p><\/p>\n<p><strong>Q#<\/strong><br \/>\n<strong>P\u00e4ring sisaldab aggregaatfunktsioone ja r\u00fchmitamisi<\/strong><\/p>\n<p>Q1<br \/>\nVALI DayOfWeek, count(*) AS c FROM ontime WHERE Year &gt;= 2000 AND Year &lt;= 2008 GROUP BY DayOfWeek ORDER BY c DESC;<\/p>\n<p>Q2<br \/>\nVALI DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay &gt; 10 AND Year &gt;= 2000 AND Year &lt;= 2008 GROUP BY DayOfWeek ORDER BY c DESC;<\/p>\n<p>Q3<br \/>\nVALI Origin, count(*) AS c FROM ontime WHERE DepDelay &gt; 10 AND Year &gt;= 2000 AND Year &lt;= 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10;<\/p>\n<p>Q4<br \/>\nVALI Carrier, count(<em>) FROM ontime WHERE DepDelay &gt; 10 AND Year = 2007 GROUP BY Carrier ORDER BY count(<\/em>) DESC;<\/p>\n<p>Q5<br \/>\nVALI a.Carrier, c, c2, c<em>1000 \/ c2 AS c3 FROM ( SELECT Carrier, count(<\/em>) AS c FROM ontime WHERE DepDelay &gt; 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 a.Carrier = b.Carrier ORDER BY c3 DESC;<\/p>\n<p>Q6<br \/>\nVALI a.Carrier, c, c2, c<em>1000 \/ c2 AS c3 FROM ( SELECT Carrier, count(<\/em>) AS c FROM ontime WHERE DepDelay &gt; 10 AND Year &gt;= 2000 AND Year = 2000 AND Year &lt;= 2008 GROUP BY Carrier ) b ON a.Carrier = b.Carrier ORDER BY c3 DESC;<\/p>\n<p>Q7<br \/>\nVALI Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Year &gt;= 2000 AND Year &lt;= 2008 GROUP BY Carrier;<\/p>\n<p>Q8<br \/>\nVALI Year, avg(DepDelay) FROM ontime GROUP BY Year;<\/p>\n<p>Q9<br \/>\nSELECT Year, count(*) AS c1 FROM ontime GROUP BY Year;<\/p>\n<p>Q10<br \/>\nVALI avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year, Month) a;<\/p>\n<p>Q11<br \/>\nSELECT avg(c1) FROM (SELECT Year,Month,count(*) AS c1 FROM ontime GROUP BY Year, Month) a;<\/p>\n<p>Q12<br \/>\nVALI OriginCityName, DestCityName, count(*) AS c FROM ontime GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;<\/p>\n<p>Q13<br \/>\nVALI OriginCityName, count(*) AS c FROM ontime GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;<\/p>\n<p><strong>K\u00fcsimus sisaldab liitumisi<\/strong><\/p>\n<p>Q14<br \/>\nVALI a.Year, c1 \/ c2 FROM ( SELECT Year, count(<em>)<\/em>1000 as c1 from ontime WHERE DepDelay&gt;10 GROUP BY Year) a INNER JOIN (select Year, count(*) as c2 from ontime GROUP BY Year ) b on a.Year=b.Year ORDER BY a.Year;<\/p>\n<p>Q15<br \/>\nSELECT a.Year, c1\/c2 FROM ( select Year, count(<em>)<\/em>1000 as c1 FROM fontime WHERE DepDelay&gt;10 GROUP BY Year) a INNER JOIN (select Year, count(*) as c2 FROM fontime GROUP BY Year ) b on a.Year=b.Year;<\/p>\n<p><\/p>\n<p><em>Table-1: Queries used in benchmark<\/em><\/p>\n<p><\/p>\n<h4 id=\"query-executions\">Query executions<\/h4>\n<p><\/p>\n<p>Siin on iga p\u00e4ringu tulemused erinevates andmebaasi seadistustes: PostgreSQL koos ja ilma indeksitega, eraldi ClickHouse ning clickhousedb_fdw. Aeg on n\u00e4idatud millisekundites.<\/p>\n<p><\/p>\n<p><strong>Q#<\/strong><br \/>\n<strong>PostgreSQL<\/strong><br \/>\n<strong>PostgreSQL (Indekseeritud)<\/strong><br \/>\n<strong>ClickHouse<\/strong><br \/>\n<strong>clickhousedb_fdw<\/strong><\/p>\n<p>Q1<br \/>\n27920<br \/>\n19634<br \/>\n23<br \/>\n57<\/p>\n<p>Q2<br \/>\n35124<br \/>\n17301<br \/>\n50<br \/>\n80<\/p>\n<p>Q3<br \/>\n34046<br \/>\n15618<br \/>\n67<br \/>\n115<\/p>\n<p>Q4<br \/>\n31632<br \/>\n7667<br \/>\n25<br \/>\n37<\/p>\n<p>Q5<br \/>\n47220<br \/>\n8976<br \/>\n27<br \/>\n60<\/p>\n<p>Q6<br \/>\n58233<br \/>\n24368<br \/>\n55<br \/>\n153<\/p>\n<p>Q7<br \/>\n30566<br \/>\n13256<br \/>\n52<br \/>\n91<\/p>\n<p>Q8<br \/>\n38309<br \/>\n60511<br \/>\n112<br \/>\n179<\/p>\n<p>Q9<br \/>\n20674<br \/>\n37979<br \/>\n31<br \/>\n81<\/p>\n<p>Q10<br \/>\n34990<br \/>\n20102<br \/>\n56<br \/>\n148<\/p>\n<p>Q11<br \/>\n30489<br \/>\n51658<br \/>\n37<br \/>\n155<\/p>\n<p>Q12<br \/>\n39357<br \/>\n33742<br \/>\n186<br \/>\n1333<\/p>\n<p>Q13<br \/>\n29912<br \/>\n30709<br \/>\n101<br \/>\n384<\/p>\n<p>Q14<br \/>\n54126<br \/>\n39913<br \/>\n124<br \/>\n1364212<\/p>\n<p>Q15<br \/>\n97258<br \/>\n30211<br \/>\n245<br \/>\n259<\/p>\n<p><\/p>\n<p><em>Table-1: Time taken to execute the queries used in benchmark<\/em><\/p>\n<p><\/p>\n<p>Tulemuste vaatamine<\/p>\n<p><\/p>\n<p>Graafik n\u00e4itab p\u00e4ringu t\u00e4itmise aega millisekundites, X-teljel on p\u00e4ringu number \u00fclaltoodud tabelitest, Y-teljel aga t\u00e4itmise aeg millisekundites. Tulemused ClickHouse'ist ja andmed, mis saadud postgres'ist clickhousedb_fdw abil, on n\u00e4idatud. Tabelist on n\u00e4ha, et PostgreSQL ja ClickHouse'i vahel on suur vahe, kuid ClickHouse'i ja clickhousedb_fdw vahel minimaalne vahe.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Anal\u00fc\u00fctiliste p\u00e4ringute j\u00f5udluse testimine PostgreSQL-is, ClickHouse-is ja clickhousedb_fdw-s (PostgreSQL)\" src=\"\/wp-content\/uploads\/2020\/07\/e084243ea7b327f30de5cb78339d3d3a.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>See graafik n\u00e4itab, kuidas ClickhouseDB ja clickhousedb_fdw erinevad. Enamikus p\u00e4ringutes ei ole FDW kulud nii suured ja vaevum\u00e4rgatavad, v\u00e4lja arvatud Q12. See p\u00e4ring sisaldab \u00fchendusi ja ORDER BY lauset. ORDER BY GROUP\/BY ja ORDER BY ei ole teist ferrulede alla ClickHouse'i.<\/p>\n<p><\/p>\n<p>Tabelis 2 n\u00e4eme ajah\u00fcpet p\u00e4ringutes Q12 ja Q13. Kordan, et see on p\u00f5hjustatud ORDER BY lausest. Selle kinnitamiseks tegin p\u00e4ringud Q-14 ja Q-15 koos ja ilma ORDER BY lauseta. Ilma ORDER BY lauseta on l\u00f5ppemise aeg 259 ms ja ORDER BY lausaga 1364212. Selle p\u00e4ringu t\u00f5rkeotsingu jaoks selgitan m\u00f5lemat p\u00e4ringut, ja siin on antud selgituse tulemused.<\/p>\n<p><\/p>\n<p>Q15: Ilma ORDER BY lauseta<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">bm=# EXPLAIN VERBOSE SELECT a.\"Year\", c1\/c2 \n     FROM (SELECT \"Year\", count(*)*1000 AS c1 FROM fontime WHERE \"DepDelay\" &gt; 10 GROUP BY \"Year\") a\n     INNER JOIN(SELECT \"Year\", count(*) AS c2 FROM fontime GROUP BY \"Year\") b ON a.\"Year\"=b.\"Year\";<\/code><\/pre>\n<p><\/p>\n<p>Q15: P\u00e4ring ilma ORDER BY lauseta<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">QUERY PLAN                                                      \nHash Join  (cost=2250.00..128516.06 rows=50000000 width=12)  \nOutput: fontime.\"Year\", (((count(*) * 1000)) \/ b.c2)  \nInner Unique: true   Hash Cond: (fontime.\"Year\" = b.\"Year\")  \n-&gt;  Foreign Scan  (cost=1.00..-1.00 rows=100000 width=12)        \nOutput: fontime.\"Year\", ((count(*) * 1000))        \nRelations: Aggregate on (fontime)        \nRemote SQL: SELECT \"Year\", (count(*) * 1000) FROM \"default\".ontime WHERE ((\"DepDelay\" &gt; 10)) GROUP BY \"Year\"  \n-&gt;  Hash  (cost=999.00..999.00 rows=100000 width=12)        \nOutput: b.c2, b.\"Year\"        \n-&gt;  Subquery Scan on b  (cost=1.00..999.00 rows=100000 width=12)              \nOutput: b.c2, b.\"Year\"              \n-&gt;  Foreign Scan  (cost=1.00..-1.00 rows=100000 width=12)                    \nOutput: fontime_1.\"Year\", (count(*))                    \nRelations: Aggregate on (fontime)                    \nRemote SQL: SELECT \"Year\", count(*) FROM \"default\".ontime GROUP BY \"Year\"(16 rows)<\/code><\/pre>\n<p><\/p>\n<p>Q14: K\u00fcsige koos ORDER BY klausliga<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">bm=# EXPLAIN VERBOSE SELECT a.\"Year\", c1\/c2 FROM(SELECT \"Year\", count(*)*1000 AS c1 FROM fontime WHERE \"DepDelay\" &gt; 10 GROUP BY \"Year\") a \n     INNER JOIN(SELECT \"Year\", count(*) as c2 FROM fontime GROUP BY \"Year\") b  ON a.\"Year\"= b.\"Year\" \n     ORDER BY a.\"Year\";<\/code><\/pre>\n<p><\/p>\n<p>Q14: K\u00fcsige plaan koos ORDER BY klausliga<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">K\u00dcSIMUS PLANEERIMINE \nMerge Join\u00a0 (kulud=2.00..628498.02 read=50000000 laius=12)\u00a0\u00a0 \nV\u00e4ljund: fontime.&quot;Aasta&quot;, (((count(*) * 1000)) \/ (count(*)))\u00a0\u00a0 \nSisemine unikaalne: t\u00f5ene\u00a0\u00a0 Merge Cond: (fontime.&quot;Aasta&quot; = fontime_1.&quot;Aasta&quot;)\u00a0\u00a0 \n-&gt;\u00a0 GroupAggregate\u00a0 (kulud=1.00..499.01 read=1 laius=12)\u00a0 \u00a0 \u00a0 \u00a0 \nV\u00e4ljund: fontime.&quot;Aasta&quot;, (count(*) * 1000)\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \nR\u00fchma v\u00f5ti: fontime.&quot;Aasta&quot;\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \n-&gt;\u00a0 V\u00f5\u00f5r-skannimine avalikul.fontime\u00a0 (kulud=1.00..-1.00 read=100000 laius=4)\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \nKaug-SQL: SELECT &quot;Aasta&quot; FROM &quot;default&quot;.ontime WHERE ((&quot;DepDelay&quot; &gt; 10)) \n            Telli &quot;Aasta&quot; ASC\u00a0\u00a0 \n-&gt;\u00a0 GroupAggregate\u00a0 (kulud=1.00..499.01 read=1 laius=12)\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \nV\u00e4ljund: fontime_1.&quot;Aasta&quot;, count(*)\u00a0\u00a0 \u00a0 \u00a0 \u00a0 R\u00fchma v\u00f5ti: fontime_1.&quot;Aasta&quot;\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \n-&gt;\u00a0 V\u00f5\u00f5r-skannimine avalikul.fontime fontime_1\u00a0 (kulud=1.00..-1.00 read=100000 laius=4)\u00a0\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \nKaug-SQL: SELECT &quot;Aasta&quot; FROM &quot;default&quot;.ontime Telli &quot;Aasta&quot; ASC(16 read)<\/code><\/pre>\n<p><\/p>\n<p>Kokkuv\u00f5te<\/p>\n<p><\/p>\n<p>Need eksperimendid n\u00e4itavad, et ClickHouse pakub t\u00f5eliselt head j\u00f5udlust ja clickhousedb_fdw toob PostgreSQL-se kaasa ClickHouse'i j\u00f5udluse eelised. Kuigi clickhousedb_fdw kasutamisel on teatud overhead, on need ebaolulised ja v\u00f5rreldavad j\u00f5udlusega, mis saavutatakse ClickHouse'i andmebaasis loomulikus k\u00e4ivitamises. See t\u00f5endab ka, et fdw PostgreSQL-s tagab suurep\u00e4raseid tulemusi.<\/p>\n<p><\/p>\n<p>Clickhouse'i Telegrami vestlus <noindex><a rel=\"nofollow\" href=\"https:\/\/t.me\/clickhouse_ru\">https:\/\/t.me\/clickhouse_ru<\/a><\/noindex><br \/>\nPostgreSQLi Telegrami vestlus <noindex><a rel=\"nofollow\" href=\"https:\/\/t.me\/pgsql\">https:\/\/t.me\/pgsql<\/a><\/noindex><\/p>\n<p>Allikas: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/511992\/\">habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u0412 \u044d\u0442\u043e\u043c \u0438\u0441\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u043d\u0438\u0438 \u044f \u0445\u043e\u0442\u0435\u043b \u043f\u043e\u0441\u043c\u043e\u0442\u0440\u0435\u0442\u044c, \u043a\u0430\u043a\u0438\u0435 \u0443\u043b\u0443\u0447\u0448\u0435\u043d\u0438\u044f \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u043c\u043e\u0436\u043d\u043e \u043f\u043e\u043b\u0443\u0447\u0438\u0442\u044c, \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u044f \u0438\u0441\u0442\u043e\u0447\u043d\u0438\u043a \u0434\u0430\u043d\u043d\u044b\u0445 ClickHouse, \u0430 \u043d\u0435 PostgreSQL. \u042f \u0437\u043d\u0430\u044e, \u043a\u0430\u043a\u0438\u0435 \u043f\u0440\u0435\u0438\u043c\u0443\u0449\u0435\u0441\u0442\u0432\u0430 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u043f\u0440\u0438 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0438 ClickHouse \u044f \u043f\u043e\u043b\u0443\u0447\u0430\u044e. \u0411\u0443\u0434\u0443\u0442 \u043b\u0438 \u044d\u0442\u0438 \u043f\u0440\u0435\u0438\u043c\u0443\u0449\u0435\u0441\u0442\u0432\u0430 \u0441\u043e\u0445\u0440\u0430\u043d\u0435\u043d\u044b, \u0435\u0441\u043b\u0438 \u044f \u043f\u043e\u043b\u0443\u0447\u0443 \u0434\u043e\u0441\u0442\u0443\u043f \u043a ClickHouse \u0438\u0437 PostgreSQL \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e \u0432\u043d\u0435\u0448\u043d\u0435\u0439 \u043e\u0431\u043e\u043b\u043e\u0447\u043a\u0438 \u0434\u0430\u043d\u043d\u044b\u0445 (FDW)? \u0418\u0441\u0441\u043b\u0435\u0434\u0443\u0435\u043c\u044b\u043c\u0438 \u0441\u0440\u0435\u0434\u0430\u043c\u0438 \u0431\u0430\u0437 \u0434\u0430\u043d\u043d\u044b\u0445 \u044f\u0432\u043b\u044f\u044e\u0442\u0441\u044f PostgreSQL v11, clickhousedb_fdw [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":89499,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-89498","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 4.9.10 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u0412 \u044d\u0442\u043e\u043c \u0438\u0441\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u043d\u0438\u0438 \u044f \u0445\u043e\u0442\u0435\u043b \u043f\u043e\u0441\u043c\u043e\u0442\u0440\u0435\u0442\u044c, \u043a\u0430\u043a\u0438\u0435 \u0443\u043b\u0443\u0447\u0448\u0435\u043d\u0438\u044f \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u043c\u043e\u0436\u043d\u043e \u043f\u043e\u043b\u0443\u0447\u0438\u0442\u044c, \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u044f \u0438\u0441\u0442\u043e\u0447\u043d\u0438\u043a \u0434\u0430\u043d\u043d\u044b\u0445 ClickHouse, \u0430 \u043d\u0435 PostgreSQL. \u042f \u0437\u043d\u0430\u044e, \u043a\u0430\u043a\u0438\u0435 \u043f\u0440\u0435\u0438\u043c\u0443\u0449\u0435\u0441\u0442\u0432\u0430 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u043f\u0440\u0438 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0438 ClickHouse \u044f \u043f\u043e\u043b\u0443\u0447\u0430\u044e. \u0411\u0443\u0434\u0443\u0442 \u043b\u0438 \u044d\u0442\u0438 \u043f\u0440\u0435\u0438\u043c\u0443\u0449\u0435\u0441\u0442\u0432\u0430 \u0441\u043e\u0445\u0440\u0430\u043d\u0435\u043d\u044b, \u0435\u0441\u043b\u0438 \u044f \u043f\u043e\u043b\u0443\u0447\u0443 \u0434\u043e\u0441\u0442\u0443\u043f \u043a ClickHouse \u0438\u0437 PostgreSQL \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e \u0432\u043d\u0435\u0448\u043d\u0435\u0439 \u043e\u0431\u043e\u043b\u043e\u0447\u043a\u0438 \u0434\u0430\u043d\u043d\u044b\u0445 (FDW)? \u0418\u0441\u0441\u043b\u0435\u0434\u0443\u0435\u043c\u044b\u043c\u0438 \u0441\u0440\u0435\u0434\u0430\u043c\u0438 \u0431\u0430\u0437 \u0434\u0430\u043d\u043d\u044b\u0445 \u044f\u0432\u043b\u044f\u044e\u0442\u0441\u044f PostgreSQL v11, clickhousedb_fdw\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/testirovanie-proizvoditelnosti-analiticheskih-zaprosov-v-postgresql-clickhouse-i-clickhousedb_fdw-postgresql\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 4.9.10\" \/>\n\t\t<meta property=\"og:locale\" content=\"et_EE\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47\u0422\u0435\u0441\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 \u0432 PostgreSQL, ClickHouse \u0438 clickhousedb_fdw (PostgreSQL) | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u0412 \u044d\u0442\u043e\u043c \u0438\u0441\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u043d\u0438\u0438 \u044f \u0445\u043e\u0442\u0435\u043b \u043f\u043e\u0441\u043c\u043e\u0442\u0440\u0435\u0442\u044c, \u043a\u0430\u043a\u0438\u0435 \u0443\u043b\u0443\u0447\u0448\u0435\u043d\u0438\u044f \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u043c\u043e\u0436\u043d\u043e \u043f\u043e\u043b\u0443\u0447\u0438\u0442\u044c, \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u044f \u0438\u0441\u0442\u043e\u0447\u043d\u0438\u043a \u0434\u0430\u043d\u043d\u044b\u0445 ClickHouse, \u0430 \u043d\u0435 PostgreSQL. \u042f \u0437\u043d\u0430\u044e, \u043a\u0430\u043a\u0438\u0435 \u043f\u0440\u0435\u0438\u043c\u0443\u0449\u0435\u0441\u0442\u0432\u0430 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u043f\u0440\u0438 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0438 ClickHouse \u044f \u043f\u043e\u043b\u0443\u0447\u0430\u044e. \u0411\u0443\u0434\u0443\u0442 \u043b\u0438 \u044d\u0442\u0438 \u043f\u0440\u0435\u0438\u043c\u0443\u0449\u0435\u0441\u0442\u0432\u0430 \u0441\u043e\u0445\u0440\u0430\u043d\u0435\u043d\u044b, \u0435\u0441\u043b\u0438 \u044f \u043f\u043e\u043b\u0443\u0447\u0443 \u0434\u043e\u0441\u0442\u0443\u043f \u043a ClickHouse \u0438\u0437 PostgreSQL \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e \u0432\u043d\u0435\u0448\u043d\u0435\u0439 \u043e\u0431\u043e\u043b\u043e\u0447\u043a\u0438 \u0434\u0430\u043d\u043d\u044b\u0445 (FDW)? \u0418\u0441\u0441\u043b\u0435\u0434\u0443\u0435\u043c\u044b\u043c\u0438 \u0441\u0440\u0435\u0434\u0430\u043c\u0438 \u0431\u0430\u0437 \u0434\u0430\u043d\u043d\u044b\u0445 \u044f\u0432\u043b\u044f\u044e\u0442\u0441\u044f PostgreSQL v11, clickhousedb_fdw\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/testirovanie-proizvoditelnosti-analiticheskih-zaprosov-v-postgresql-clickhouse-i-clickhousedb_fdw-postgresql\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2020-07-23T11:42:07+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2020-07-23T11:42:07+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47Ajaloolise p\u00e4randi p\u00e4randi p\u00e4randi t\u00f6\u00f6tlemise testimise anal\u00fc\u00fcs PostgreSQL-is, ClickHouse'is ja clickhousedb_fdw (PostgreSQL) | ProHoster","description":"Selles uuringus soovisin uurida, milliseid j\u00f5udluse paranemisi saab saavutada, kasutades andmeallikat ClickHouse, mitte PostgreSQL-i. Tean, millised j\u00f5udluse eelised on ClickHouse'i kasutamisel. Kas need eelised s\u00e4ilivad, kui p\u00e4\u00e4sen ClickHouse'ile PostgreSQL-ist andmev\u00e4lise m\u00e4hise (FDW) kaudu? Uuritud andmebaasi keskkonnad on PostgreSQL v11, clickhousedb_fdw","canonical_url":"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/testirovanie-proizvoditelnosti-analiticheskih-zaprosov-v-postgresql-clickhouse-i-clickhousedb_fdw-postgresql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"et_EE","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u0422\u0435\u0441\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 \u0432 PostgreSQL, ClickHouse \u0438 clickhousedb_fdw (PostgreSQL) | ProHoster","og:description":"\u0412 \u044d\u0442\u043e\u043c \u0438\u0441\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u043d\u0438\u0438 \u044f \u0445\u043e\u0442\u0435\u043b \u043f\u043e\u0441\u043c\u043e\u0442\u0440\u0435\u0442\u044c, \u043a\u0430\u043a\u0438\u0435 \u0443\u043b\u0443\u0447\u0448\u0435\u043d\u0438\u044f \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u043c\u043e\u0436\u043d\u043e \u043f\u043e\u043b\u0443\u0447\u0438\u0442\u044c, \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u044f \u0438\u0441\u0442\u043e\u0447\u043d\u0438\u043a \u0434\u0430\u043d\u043d\u044b\u0445 ClickHouse, \u0430 \u043d\u0435 PostgreSQL. \u042f \u0437\u043d\u0430\u044e, \u043a\u0430\u043a\u0438\u0435 \u043f\u0440\u0435\u0438\u043c\u0443\u0449\u0435\u0441\u0442\u0432\u0430 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u043f\u0440\u0438 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0438 ClickHouse \u044f \u043f\u043e\u043b\u0443\u0447\u0430\u044e. \u0411\u0443\u0434\u0443\u0442 \u043b\u0438 \u044d\u0442\u0438 \u043f\u0440\u0435\u0438\u043c\u0443\u0449\u0435\u0441\u0442\u0432\u0430 \u0441\u043e\u0445\u0440\u0430\u043d\u0435\u043d\u044b, \u0435\u0441\u043b\u0438 \u044f \u043f\u043e\u043b\u0443\u0447\u0443 \u0434\u043e\u0441\u0442\u0443\u043f \u043a ClickHouse \u0438\u0437 PostgreSQL \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e \u0432\u043d\u0435\u0448\u043d\u0435\u0439 \u043e\u0431\u043e\u043b\u043e\u0447\u043a\u0438 \u0434\u0430\u043d\u043d\u044b\u0445 (FDW)? \u0418\u0441\u0441\u043b\u0435\u0434\u0443\u0435\u043c\u044b\u043c\u0438 \u0441\u0440\u0435\u0434\u0430\u043c\u0438 \u0431\u0430\u0437 \u0434\u0430\u043d\u043d\u044b\u0445 \u044f\u0432\u043b\u044f\u044e\u0442\u0441\u044f PostgreSQL v11, clickhousedb_fdw","og:url":"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/testirovanie-proizvoditelnosti-analiticheskih-zaprosov-v-postgresql-clickhouse-i-clickhousedb_fdw-postgresql","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-07-23T11:42:07+00:00","article:modified_time":"2020-07-23T11:42:07+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"89498","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 13:10:17","updated":"2022-10-10 22:45:45"},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts\/89498","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/comments?post=89498"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts\/89498\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media\/89499"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media?parent=89498"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/categories?post=89498"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/tags?post=89498"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}