{"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\/cs\/blog\/administrirovanie\/testirovanie-proizvoditelnosti-analiticheskih-zaprosov-v-postgresql-clickhouse-i-clickhousedb_fdw-postgresql","title":{"rendered":"Testov\u00e1n\u00ed v\u00fdkonu analytick\u00fdch dotaz\u016f v PostgreSQL, ClickHouse a clickhousedb_fdw (PostgreSQL)","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>V t\u00e9to studii jsem cht\u011bl zkoumat, jak\u00fdch v\u00fdkonov\u00fdch zlep\u0161en\u00ed mohu dos\u00e1hnout pou\u017eit\u00edm datov\u00e9ho zdroje ClickHouse m\u00edsto PostgreSQL. V\u00edm, jak\u00e9 v\u00fdhody v\u00fdkonu z\u00edsk\u00e1v\u00e1m p\u0159i pou\u017e\u00edv\u00e1n\u00ed ClickHouse. Zachovaj\u00ed se tyto v\u00fdhody, pokud z\u00edsk\u00e1m p\u0159\u00edstup k ClickHouse z PostgreSQL pomoc\u00ed extern\u00ed datov\u00e9 ob\u00e1lky (FDW)? <\/p>\n<p><noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<p>Zkouman\u00fdmi datab\u00e1zov\u00fdmi prost\u0159ed\u00edmi jsou PostgreSQL v11, clickhousedb_fdw a datab\u00e1ze ClickHouse. Nakonec budeme z PostgreSQL v11 spou\u0161t\u011bt r\u016fzn\u00e9 SQL dotazy, kter\u00e9 budou sm\u011brov\u00e1ny p\u0159es n\u00e1\u0161 clickhousedb_fdw do datab\u00e1ze ClickHouse. Pot\u00e9 uvid\u00edme, jak se v\u00fdkon FDW porovn\u00e1 s t\u011bmi sam\u00fdmi dotazy prov\u00e1d\u011bn\u00fdmi v nativn\u00edm PostgreSQL a nativn\u00edm ClickHouse.<\/p>\n<p><\/p>\n<h3 id=\"baza-dannyh-clickhouse\">Datab\u00e1ze ClickHouse<\/h3>\n<p><\/p>\n<p>ClickHouse je syst\u00e9m spr\u00e1vy datab\u00e1z\u00ed zalo\u017een\u00fd na sloupc\u00edch s otev\u0159en\u00fdm zdrojov\u00fdm k\u00f3dem, kter\u00fd m\u016f\u017ee dosahovat v\u00fdkonu 100-1000x rychleji ne\u017e tradi\u010dn\u00ed p\u0159\u00edstupy k datab\u00e1z\u00edm a je schopen zpracov\u00e1vat v\u00edce ne\u017e miliardu \u0159\u00e1dk\u016f za m\u00e9n\u011b ne\u017e sekundu.<\/p>\n<p><\/p>\n<h3 id=\"clickhousedb_fdw\">Clickhousedb_fdw<\/h3>\n<p><\/p>\n<p>clickhousedb_fdw je ob\u00e1lka extern\u00edch dat datab\u00e1ze ClickHouse, nebo FDW, je projekt s otev\u0159en\u00fdm zdrojov\u00fdm k\u00f3dem od Percona. <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/Percona-Lab\/clickhousedb_fdw\">Zde je odkaz na repozit\u00e1\u0159 projektu GitHub<\/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\/\">V b\u0159eznu jsem napsal blog, ve kter\u00e9m se dozv\u00edte v\u00edce o na\u0161em FDW<\/a><\/noindex>.<\/p>\n<p><\/p>\n<p>Jak uvid\u00edte, poskytuje FDW pro ClickHouse, kter\u00fd umo\u017e\u0148uje SELECT from a INSERT INTO datab\u00e1ze ClickHouse ze serveru PostgreSQL v11.<\/p>\n<p><\/p>\n<p>FDW podporuje pokro\u010dil\u00e9 funkce, jako jsou agregace a spojen\u00ed. To v\u00fdrazn\u011b zvy\u0161uje v\u00fdkon d\u00edky vyu\u017eit\u00ed zdroj\u016f vzd\u00e1len\u00e9ho serveru pro tyto n\u00e1ro\u010dn\u00e9 operace.<\/p>\n<p><\/p>\n<h3 id=\"benchmark-environment\">Benchmarkov\u00e9 prost\u0159ed\u00ed<\/h3>\n<p><\/p>\n<ul>\n<li>Server Supermicro:\n<ul>\n<li>Intel&reg; Xeon&reg; CPU E5-2683 v3 @ 2.00GHz<\/li>\n<li>2 sokety \/ 28 jader \/ 56 vl\u00e1ken<\/li>\n<li>Pam\u011b\u0165: 256GB RAM<\/li>\n<li>\u00dalo\u017ei\u0161t\u011b: Samsung SM863 1.9TB Enterprise SSD<\/li>\n<li>Souborov\u00fd syst\u00e9m: 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: verze 11<\/li>\n<\/ul>\n<p><\/p>\n<h3 id=\"benchmark-tests\">Benchmarkov\u00e9 testy<\/h3>\n<p><\/p>\n<p>M\u00edsto pou\u017eit\u00ed n\u011bjak\u00e9 datov\u00e9 sady generovan\u00e9 strojov\u011b, jsme pro tento test pou\u017eili data \"V\u00fdkon podle \u010dasu, vykazovan\u00e9ho o dob\u011b provozu oper\u00e1tora\" od roku 1987 do 2018. K dat\u016fm se m\u016f\u017eete dostat <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/Percona-Lab\/ontime-airline-performance\/blob\/master\/download.sh\">pomoc\u00ed na\u0161eho skriptu, kter\u00fd je k dispozici zde<\/a><\/noindex>.<\/p>\n<p><\/p>\n<p>Velikost datab\u00e1ze je 85 GB, co\u017e zabezpe\u010duje jednu tabulku se 109 sloupci.<\/p>\n<p><\/p>\n<h4 id=\"benchmark-queries\">Benchmarkov\u00e9 dotazy<\/h4>\n<p><\/p>\n<p>Zde jsou dotazy, kter\u00e9 jsem pou\u017eil k porovn\u00e1n\u00ed ClickHouse, clickhousedb_fdw a PostgreSQL.<\/p>\n<p><\/p>\n<p><strong>Q#<\/strong><br \/>\n<strong>Dotaz obsahuje agregace a GROUP BY<\/strong><\/p>\n<p>Q1<br \/>\nSELECT 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 \/>\nVYBERTE DenVT\u00fddnu, COUNT(*) jako c FROM ontime WHERE DepDelay&gt;10 A Rok &gt;= 2000 A Rok &lt;= 2008 SKUPINA PODLE DenVT\u00fddnu OBJEDNAT PODLE c DESC;<\/p>\n<p>Q3<br \/>\nVYBERTE P\u016fvod, COUNT(*) jako c Z ontime WHERE DepDelay&gt;10 A Rok &gt;= 2000 A Rok &lt;= 2008 SKUPINA PODLE P\u016fvod OBJEDNAT PODLE c DESC LIMIT 10;<\/p>\n<p>Q4<br \/>\nVYBERTE Dopravce, COUNT(<em>) Z ontime WHERE DepDelay&gt;10 A Rok = 2007 SKUPINA PODLE Dopravce OBJEDNAT PODLE COUNT(<\/em>) DESC;<\/p>\n<p>Q5<br \/>\nVYBERTE a.Dopravce, c, c2, c<em>1000\/c2 jako c3 Z ( VYBERTE Dopravce, COUNT(<\/em>) jako c Z ontime WHERE DepDelay&gt;10 A Rok=2007 SKUPINA PODLE Dopravce ) a VNIT\u0158N\u00cd P\u0158IPOJEN\u00cd ( VYBERTE Dopravce, COUNT(*) jako c2 Z ontime WHERE Rok=2007 SKUPINA PODLE Dopravce) b na a.Dopravce=b.Dopravce OBJEDNAT PODLE c3 DESC;<\/p>\n<p>Q6<br \/>\nVYBERTE a.Dopravce, c, c2, c<em>1000\/c2 jako c3 Z ( VYBERTE Dopravce, COUNT(<\/em>) jako c Z ontime WHERE DepDelay&gt;10 A Rok &gt;= 2000 A Rok = 2000 A Rok &lt;= 2008 SKUPINA PODLE Dopravce ) b na a.Dopravce=b.Dopravce OBJEDNAT PODLE c3 DESC;<\/p>\n<p>Q7<br \/>\nVYBERTE Dopravce, pr\u016fm\u011br(DepDelay) * 1000 jako c3 Z ontime WHERE Rok &gt;= 2000 A Rok &lt;= 2008 SKUPINA PODLE Dopravce;<\/p>\n<p>Q8<br \/>\nVYBERTE Rok, pr\u016fm\u011br(DepDelay) Z ontime SKUPINA PODLE Rok;<\/p>\n<p>Q9<br \/>\nvyberte Rok, COUNT(*) jako c1 z ontime skupina podle Rok;<\/p>\n<p>Q10<br \/>\nVYBERTE pr\u016fm\u011br(cnt) Z (VYBERTE Rok, M\u011bs\u00edc, COUNT(*) jako cnt Z ontime WHERE DepDel15=1 SKUPINA PODLE Rok, M\u011bs\u00edc) a;<\/p>\n<p>Q11<br \/>\nvyberte pr\u016fm\u011br(c1) z (vyberte Rok, M\u011bs\u00edc, COUNT(*) jako c1 z ontime skupina podle Rok, M\u011bs\u00edc) a;<\/p>\n<p>Q12<br \/>\nVYBERTE P\u016fvodM\u011bsto, DestM\u011bsto, COUNT(*) jako c Z ontime SKUPINA PODLE P\u016fvodM\u011bsto, DestM\u011bsto OBJEDNAT PODLE c DESC LIMIT 10;<\/p>\n<p>Q13<br \/>\nVYBERTE P\u016fvodM\u011bsto, COUNT(*) jako c Z ontime SKUPINA PODLE P\u016fvodM\u011bsto OBJEDNAT PODLE c DESC LIMIT 10;<\/p>\n<p><strong>Dotaz obsahuje spojen\u00ed<\/strong><\/p>\n<p>Q14<br \/>\nVYBERTE a.Rok, c1\/c2 Z ( vyberte Rok, COUNT(<em>)<\/em>1000 jako c1 z ontime WHERE DepDelay&gt;10 SKUPINA PODLE Rok) a VNIT\u0158N\u00cd P\u0158IPOJEN\u00cd ( vyberte Rok, COUNT(*) jako c2 z ontime SKUPINA PODLE Rok ) b na a.Rok=b.Rok OBJEDNAT PODLE a.Rok;<\/p>\n<p>Q15<br \/>\nVYBERTE a.\"Rok\", c1\/c2 Z ( vyberte \"Rok\", COUNT(<em>)<\/em>1000 jako c1 Z fontime WHERE \"DepDelay\"&gt;10 SKUPINA PODLE \"Rok\") a VNIT\u0158N\u00cd P\u0158IPOJEN\u00cd (vyberte \"Rok\", COUNT(*) jako c2 Z fontime SKUPINA PODLE \"Rok\" ) b na a.\"Rok\"=b.\"Rok\";<\/p>\n<p><\/p>\n<p><em>Tabulka-1: Dotazy pou\u017eit\u00e9 v benchmarku<\/em><\/p>\n<p><\/p>\n<h4 id=\"query-executions\">Prov\u00e1d\u011bn\u00ed dotaz\u016f<\/h4>\n<p><\/p>\n<p>Zde jsou v\u00fdsledky ka\u017ed\u00e9ho dotazu p\u0159i prov\u00e1d\u011bn\u00ed v r\u016fzn\u00fdch nastaven\u00edch datab\u00e1ze: PostgreSQL s indexy a bez nich, vlastn\u00ed ClickHouse a clickhousedb_fdw. \u010cas je uv\u00e1d\u011bn v milisekund\u00e1ch.<\/p>\n<p><\/p>\n<p><strong>Q#<\/strong><br \/>\n<strong>PostgreSQL<\/strong><br \/>\n<strong>PostgreSQL (indexovan\u00fd)<\/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>Tabulka-1: \u010cas pot\u0159ebn\u00fd k proveden\u00ed dotaz\u016f pou\u017eit\u00fdch v benchmarku<\/em><\/p>\n<p><\/p>\n<p>P\u0159ehled v\u00fdsledk\u016f<\/p>\n<p><\/p>\n<p>Graf ukazuje dobu prov\u00e1d\u011bn\u00ed dotazu v milisekund\u00e1ch, osa X ukazuje \u010d\u00edslo dotazu z v\u00fd\u0161e uveden\u00fdch tabulek a osa Y ukazuje dobu prov\u00e1d\u011bn\u00ed v milisekund\u00e1ch. V\u00fdsledky ClickHouse a data z\u00edskan\u00e1 z postgres pomoc\u00ed clickhousedb_fdw jsou zobrazeny. Z tabulky je vid\u011bt, \u017ee mezi PostgreSQL a ClickHouse existuje obrovsk\u00fd rozd\u00edl, ale minim\u00e1ln\u00ed rozd\u00edl mezi ClickHouse a clickhousedb_fdw.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Testov\u00e1n\u00ed v\u00fdkonu analytick\u00fdch dotaz\u016f v PostgreSQL, ClickHouse a clickhousedb_fdw (PostgreSQL)\" src=\"\/wp-content\/uploads\/2020\/07\/e084243ea7b327f30de5cb78339d3d3a.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Tento graf ukazuje rozd\u00edl mezi ClickhouseDB a clickhousedb_fdw. U v\u011bt\u0161iny dotaz\u016f nejsou re\u017eijn\u00ed n\u00e1klady FDW tak velk\u00e9 a sotva v\u00fdznamn\u00e9, krom\u011b Q12. Tento dotaz zahrnuje spojen\u00ed a klauzuli ORDER BY. Kv\u016fli klauzuli ORDER BY GROUP\/BY a ORDER BY se nedoch\u00e1z\u00ed k ClickHouse.<\/p>\n<p><\/p>\n<p>V tabulce 2 vid\u00edme skok v \u010dase u dotaz\u016f Q12 a Q13. Opakuji, to je zp\u016fsobeno p\u0159\u00edkazem ORDER BY. Abych to potvrdil, vykonal jsem dotazy Q-14 a Q-15 s p\u0159\u00edkazem ORDER BY a bez n\u011bj. Bez p\u0159\u00edkazu ORDER BY je doba dokon\u010den\u00ed 259 ms, a s p\u0159\u00edkazem ORDER BY je to 1364212. Pro lad\u011bn\u00ed tohoto dotazu vysv\u011btluji oba dotazy, a zde jsou v\u00fdsledky vysv\u011btlen\u00ed.<\/p>\n<p><\/p>\n<p>Q15: Bez p\u0159\u00edkazu ORDER BY<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">bm=# VYSV\u011aTLIT VERB\u00d3ZN\u011a VYBERTE a.&quot;Rok&quot;, c1\/c2 \n     Z (VYBERTE &quot;Rok&quot;, count(*)*1000 JAKO c1 Z fontime KDE &quot;DepDelay&quot; &gt; 10 SKUPINA PODLE &quot;Rok&quot;) a\n     VNIT\u0158N\u00cd SPOJEN\u00cd (VYBERTE &quot;Rok&quot;, count(*) JAKO c2 Z fontime SKUPINA PODLE &quot;Rok&quot;) b NA a.&quot;Rok&quot;=b.&quot;Rok&quot;;<\/code><\/pre>\n<p><\/p>\n<p>Q15: Dotaz bez p\u0159\u00edkazu ORDER BY<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">PL\u00c1N DOTAZU                                                      \nHash Join  (n\u00e1klady=2250.00..128516.06 rows=50000000 width=12)  \nV\u00fdstup: fontime.&quot;Rok&quot;, (((count(*) * 1000)) \/ b.c2)  \nVnit\u0159n\u00ed unik\u00e1tn\u00ed: true   Hash Cond: (fontime.&quot;Rok&quot; = b.&quot;Rok&quot;)  \n-&gt;  Ciz\u00ed skenov\u00e1n\u00ed  (n\u00e1klady=1.00..-1.00 rows=100000 width=12)        \nV\u00fdstup: fontime.&quot;Rok&quot;, ((count(*) * 1000))        \nVztahy: Agregace na (fontime)        \nVzd\u00e1len\u00e9 SQL: VYBERTE &quot;Rok&quot;, (count(*) * 1000) Z &quot;default&quot;.ontime KDE ((&quot;DepDelay&quot; &gt; 10)) SKUPINA PODLE &quot;Rok&quot;  \n-&gt;  Hash  (n\u00e1klady=999.00..999.00 rows=100000 width=12)        \nV\u00fdstup: b.c2, b.&quot;Rok&quot;        \n-&gt;  Skenov\u00e1n\u00ed subdotazu na b  (n\u00e1klady=1.00..999.00 rows=100000 width=12)              \nV\u00fdstup: b.c2, b.&quot;Rok&quot;              \n-&gt;  Ciz\u00ed skenov\u00e1n\u00ed  (n\u00e1klady=1.00..-1.00 rows=100000 width=12)                    \nV\u00fdstup: fontime_1.&quot;Rok&quot;, (count(*))                    \nVztahy: Agregace na (fontime)                    \nVzd\u00e1len\u00e9 SQL: VYBERTE &quot;Rok&quot;, count(*) Z &quot;default&quot;.ontime SKUPINA PODLE &quot;Rok&quot;(16 \u0159\u00e1dk\u016f)<\/code><\/pre>\n<p><\/p>\n<p>Q14: Dotaz s p\u0159\u00edkazem ORDER BY<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">bm=# VYSV\u011aTLIT VERB\u00d3ZN\u011a VYBERTE a.&quot;Rok&quot;, c1\/c2 Z(SELECT &quot;Rok&quot;, count(*)*1000 JAKO c1 Z fontime KDE &quot;DepDelay&quot; &gt; 10 SKUPINA PODLE &quot;Rok&quot;) a \n     VNIT\u0158N\u00cd SPOJEN\u00cd(VYBERTE &quot;Rok&quot;, count(*) jako c2 Z fontime SKUPINA PODLE &quot;Rok&quot;) b  NA a.&quot;Rok&quot;= b.&quot;Rok&quot; \n     PO\u0158AD\u00cd PODLE a.&quot;Rok&quot;;<\/code><\/pre>\n<p><\/p>\n<p>Q14: Pl\u00e1n dotazu s p\u0159\u00edkazem ORDER BY<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">PL\u00c1N DOTAZU \nSlu\u010dov\u00e1n\u00ed spojen\u00ed\u00a0 (n\u00e1klady=2.00..628498.02 rows=50000000 width=12)\u00a0\u00a0 \nV\u00fdstup: fontime.&quot;Rok&quot;, (((count(*) * 1000)) \/ (count(*)))\u00a0\u00a0 \nVnit\u0159n\u00ed unik\u00e1tn\u00ed: true\u00a0\u00a0 Slu\u010dovac\u00ed podm\u00ednka: (fontime.&quot;Rok&quot; = fontime_1.&quot;Rok&quot;)\u00a0\u00a0 \n-&gt;\u00a0 Agregace ve skupin\u011b\u00a0 (n\u00e1klady=1.00..499.01 rows=1 width=12)\u00a0 \u00a0 \u00a0 \u00a0 \nV\u00fdstup: fontime.&quot;Rok&quot;, (count(*) * 1000)\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \nKl\u00ed\u010d skupiny: fontime.&quot;Rok&quot;\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \n-&gt;\u00a0 Ciz\u00ed skenov\u00e1n\u00ed na ve\u0159ejn\u00e9m.fontime\u00a0 (n\u00e1klady=1.00..-1.00 rows=100000 width=4)\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \nVzd\u00e1len\u00e9 SQL: VYBERTE &quot;Rok&quot; Z &quot;default&quot;.ontime KDE ((&quot;DepDelay&quot; &gt; 10)) \n            PO\u0158AD\u00cd PODLE &quot;Rok&quot; ASC\u00a0\u00a0 \n-&gt;\u00a0 Agregace ve skupin\u011b\u00a0 (n\u00e1klady=1.00..499.01 rows=1 width=12)\u00a0\u00a0 \nV\u00fdstup: fontime_1.&quot;Rok&quot;, count(*)\u00a0\u00a0 \u00a0 \u00a0 \u00a0 Kl\u00ed\u010d skupiny: fontime_1.&quot;Rok&quot;\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \n-&gt;\u00a0 Ciz\u00ed skenov\u00e1n\u00ed na ve\u0159ejn\u00e9m.fontime fontime_1\u00a0 (n\u00e1klady=1.00..-1.00 rows=100000 width=4)\u00a0\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \nVzd\u00e1len\u00e9 SQL: VYBERTE &quot;Rok&quot; Z &quot;default&quot;.ontime PO\u0158AD\u00cd PODLE &quot;Rok&quot; ASC(16 \u0159\u00e1dk\u016f)<\/code><\/pre>\n<p><\/p>\n<p>V\u00fdstup<\/p>\n<p><\/p>\n<p>V\u00fdsledky t\u011bchto experiment\u016f ukazuj\u00ed, \u017ee ClickHouse nab\u00edz\u00ed skute\u010dn\u011b dobr\u00fd v\u00fdkon, zat\u00edmco clickhousedb_fdw p\u0159in\u00e1\u0161\u00ed v\u00fdhody v\u00fdkonu ClickHouse z PostgreSQL. I kdy\u017e p\u0159i pou\u017e\u00edv\u00e1n\u00ed clickhousedb_fdw existuj\u00ed ur\u010dit\u00e9 re\u017eijn\u00ed n\u00e1klady, jsou nepatrn\u00e9 a srovnateln\u00e9 s v\u00fdkonem dosa\u017een\u00fdm p\u0159i p\u0159\u00edm\u00e9m spu\u0161t\u011bn\u00ed v datab\u00e1zi ClickHouse. To tak\u00e9 potvrzuje, \u017ee fdw v PostgreSQL poskytuje vynikaj\u00edc\u00ed v\u00fdsledky.<\/p>\n<p><\/p>\n<p>Telegramov\u00e1 skupina pro Clickhouse <noindex><a rel=\"nofollow\" href=\"https:\/\/t.me\/clickhouse_ru\">https:\/\/t.me\/clickhouse_ru<\/a><\/noindex><br \/>\nTelegramov\u00e1 skupina pro PostgreSQL <noindex><a rel=\"nofollow\" href=\"https:\/\/t.me\/pgsql\">https:\/\/t.me\/pgsql<\/a><\/noindex><\/p>\n<p>Zdroj: <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 5.0.3 - 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.\" \/>\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\/cs\/blog\/administrirovanie\/testirovanie-proizvoditelnosti-analiticheskih-zaprosov-v-postgresql-clickhouse-i-clickhousedb_fdw-postgresql\" \/>\n\t\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.3\" \/>\n\t\t<meta property=\"og:locale\" content=\"cs_CZ\" \/>\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.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/cs\/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\udd47Testov\u00e1n\u00ed v\u00fdkonu analytick\u00fdch dotaz\u016f v PostgreSQL, ClickHouse a clickhousedb_fdw (PostgreSQL) | ProHoster","description":"V t\u00e9to studii jsem se cht\u011bl pod\u00edvat, jak\u00e1 zlep\u0161en\u00ed v\u00fdkonu lze dos\u00e1hnout pou\u017eit\u00edm datov\u00e9ho zdroje ClickHouse m\u00edsto PostgreSQL.","canonical_url":"https:\/\/prohoster.info\/cs\/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":"cs_CZ","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.","og:url":"https:\/\/prohoster.info\/cs\/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","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/89498","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/comments?post=89498"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/89498\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media\/89499"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media?parent=89498"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/categories?post=89498"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/tags?post=89498"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}