{"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\/pl\/blog\/administrirovanie\/testirovanie-proizvoditelnosti-analiticheskih-zaprosov-v-postgresql-clickhouse-i-clickhousedb_fdw-postgresql","title":{"rendered":"Testowanie wydajno\u015bci zapyta\u0144 analitycznych w PostgreSQL, ClickHouse i clickhousedb_fdw (PostgreSQL)","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>W tym badaniu chcia\u0142em zbada\u0107, jakie poprawy wydajno\u015bci mo\u017cna osi\u0105gn\u0105\u0107, u\u017cywaj\u0105c \u017ar\u00f3d\u0142a danych ClickHouse zamiast PostgreSQL. Znam korzy\u015bci wydajno\u015bciowe, jakie oferuje ClickHouse. Czy te zalety b\u0119d\u0105 zachowane, je\u015bli uzyskam dost\u0119p do ClickHouse z PostgreSQL za pomoc\u0105 zewn\u0119trznej pow\u0142oki danych (FDW)? <\/p>\n<p><noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<p>Badanymi \u015brodowiskami baz danych s\u0105 PostgreSQL v11, clickhousedb_fdw i baza danych ClickHouse. Ostatecznie z PostgreSQL v11 wykonamy r\u00f3\u017cne zapytania SQL, kierowane przez nasz clickhousedb_fdw do bazy danych ClickHouse. Nast\u0119pnie zobaczymy, jak wydajno\u015b\u0107 FDW por\u00f3wnuje si\u0119 z tymi samymi zapytaniami wykonywanymi w natywnym PostgreSQL i natywnym ClickHouse.<\/p>\n<p><\/p>\n<h3 id=\"baza-dannyh-clickhouse\">Baza danych Clickhouse<\/h3>\n<p><\/p>\n<p>ClickHouse to system zarz\u0105dzania bazami danych typu kolumnowego z otwartym kodem \u017ar\u00f3d\u0142owym, kt\u00f3ry mo\u017ce osi\u0105ga\u0107 wydajno\u015b\u0107 od 100 do 1000 razy szybciej ni\u017c tradycyjne podej\u015bcia do baz danych, zdolny do przetwarzania ponad miliarda wierszy w mniej ni\u017c sekund\u0119.<\/p>\n<p><\/p>\n<h3 id=\"clickhousedb_fdw\">Clickhousedb_fdw<\/h3>\n<p><\/p>\n<p>clickhousedb_fdw to zewn\u0119trzna pow\u0142oka danych dla bazy danych ClickHouse, lub FDW, b\u0119d\u0105ca projektem z otwartym kodem \u017ar\u00f3d\u0142owym od Percona. <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/Percona-Lab\/clickhousedb_fdw\">Oto link do repozytorium 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\/\">W marcu napisa\u0142em bloga, kt\u00f3ry opowiada wi\u0119cej o naszym FDW<\/a><\/noindex>.<\/p>\n<p><\/p>\n<p>Jak zobaczycie, to udost\u0119pnia FDW dla ClickHouse, kt\u00f3re pozwala na SELECT from i INSERT INTO bazy danych ClickHouse z serwera PostgreSQL v11.<\/p>\n<p><\/p>\n<p>FDW wspiera zaawansowane funkcje, takie jak agregaty i \u0142\u0105czenia. To znacznie zwi\u0119ksza wydajno\u015b\u0107, wykorzystuj\u0105c zasoby zdalnego serwera do tych zasobo\u017cernych operacji.<\/p>\n<p><\/p>\n<h3 id=\"benchmark-environment\">\u015arodowisko benchmarkowe<\/h3>\n<p><\/p>\n<ul>\n<li>Serwer Supermicro:\n<ul>\n<li>Intel&reg; Xeon&reg; CPU E5-2683 v3 @ 2.00GHz<\/li>\n<li>2 gniazda \/ 28 rdzeni \/ 56 w\u0105tk\u00f3w<\/li>\n<li>Pami\u0119\u0107: 256 GB RAM<\/li>\n<li>Przechowywanie: Samsung SM863 1.9TB SSD Enterprise<\/li>\n<li>System plik\u00f3w: 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: wersja 11<\/li>\n<\/ul>\n<p><\/p>\n<h3 id=\"benchmark-tests\">Testy benchmarkowe<\/h3>\n<p><\/p>\n<p>Zamiast u\u017cywa\u0107 jakiego\u015b zbioru danych wygenerowanego przez maszyn\u0119 do tego testu, u\u017cyli\u015bmy danych \u201eWydajno\u015b\u0107 czasu, informuj\u0105ca o czasie pracy operatora\u201d z lat 1987-2018. Mo\u017cesz uzyska\u0107 dost\u0119p do danych <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/Percona-Lab\/ontime-airline-performance\/blob\/master\/download.sh\">za pomoc\u0105 naszego skryptu, dost\u0119pnego tutaj<\/a><\/noindex>.<\/p>\n<p><\/p>\n<p>Rozmiar bazy danych wynosi 85 GB, zapewniaj\u0105c jedn\u0105 tabel\u0119 z 109 kolumnami.<\/p>\n<p><\/p>\n<h4 id=\"benchmark-queries\">Zapytania benchmarkowe<\/h4>\n<p><\/p>\n<p>Oto zapytania, kt\u00f3re u\u017cy\u0142em do por\u00f3wnania ClickHouse, clickhousedb_fdw i PostgreSQL.<\/p>\n<p><\/p>\n<p><strong>Q#<\/strong><br \/>\n<strong>Zapytanie zawiera agregaty i 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 \/>\nSELECT 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 \/>\nSELECT 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 \/>\nSELECT 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 \/>\nSELECT 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 \/>\nSELECT 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 \/>\nSELECT Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Year &gt;= 2000 AND Year &lt;= 2008 GROUP BY Carrier;<\/p>\n<p>Q8<br \/>\nSELECT 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 \/>\nSELECT 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 \/>\nSELECT OriginCityName, DestCityName, count(*) AS c FROM ontime GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;<\/p>\n<p>Q13<br \/>\nSELECT OriginCityName, count(*) AS c FROM ontime GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;<\/p>\n<p><strong>Query Contains Joins<\/strong><\/p>\n<p>Q14<br \/>\nSELECT 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>Oto wyniki ka\u017cdego z zapyta\u0144 przy wykonywaniu w r\u00f3\u017cnych ustawieniach bazy danych: PostgreSQL z indeksami i bez nich, w\u0142asny ClickHouse i clickhousedb_fdw. Czas jest podawany w milisekundach.<\/p>\n<p><\/p>\n<p><strong>Q#<\/strong><br \/>\n<strong>PostgreSQL<\/strong><br \/>\n<strong>PostgreSQL (Indexed)<\/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: Czas wykonania zapyta\u0144 u\u017cytych w benchmarku<\/em><\/p>\n<p><\/p>\n<p>Przegl\u0105danie wynik\u00f3w<\/p>\n<p><\/p>\n<p>Wykres pokazuje czas wykonania zapytania w milisekundach, o\u015b X pokazuje numer zapytania z powy\u017cszych tabel, a o\u015b Y pokazuje czas wykonania w milisekundach. Wyniki ClickHouse i dane uzyskane z postgres za pomoc\u0105 clickhousedb_fdw s\u0105 pokazane. Z tabeli wida\u0107, \u017ce istnieje ogromna r\u00f3\u017cnica mi\u0119dzy PostgreSQL a ClickHouse, ale minimalna r\u00f3\u017cnica mi\u0119dzy ClickHouse a clickhousedb_fdw.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Testowanie wydajno\u015bci zapyta\u0144 analitycznych w PostgreSQL, ClickHouse i clickhousedb_fdw (PostgreSQL)\" src=\"\/wp-content\/uploads\/2020\/07\/e084243ea7b327f30de5cb78339d3d3a.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ten wykres pokazuje r\u00f3\u017cnic\u0119 mi\u0119dzy ClickhouseDB a clickhousedb_fdw. W wi\u0119kszo\u015bci zapyta\u0144 koszty FDW nie s\u0105 tak du\u017ce i s\u0105 ledwo zauwa\u017calne, z wyj\u0105tkiem Q12. To zapytanie zawiera po\u0142\u0105czenia i klauzul\u0119 ORDER BY. Z powodu klauzuli ORDER BY GROUP\/BY i ORDER BY nie opadaj\u0105 do ClickHouse.<\/p>\n<p><\/p>\n<p>W tabeli 2 widzimy skok czasu w zapytaniach Q12 i Q13. Powtarzam, jest to spowodowane u\u017cyciem klauzuli ORDER BY. Aby to potwierdzi\u0107, wykona\u0142em zapytania Q-14 i Q-15 z klauzul\u0105 ORDER BY i bez niej. Bez klauzuli ORDER BY czas zako\u0144czenia wynosi 259 ms, a z klauzul\u0105 ORDER BY \u2014 1364212. Dla debugowania tego zapytania wyja\u015bniam oba zapytania, a tutaj przedstawione s\u0105 wyniki wyja\u015bnienia.<\/p>\n<p><\/p>\n<p>Q15: Bez klauzuli ORDER BY<\/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: Zapytanie bez klauzuli ORDER BY<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">PLAN ZAPYTANIA                                                      \nHash Join  (cost=2250.00..128516.06 rows=50000000 width=12)  \nWyj\u015bcie: fontime.\"Year\", (((count(*) * 1000)) \/ b.c2)  \nUnikalne wewn\u0119trzne: true   Warunek haszuj\u0105cy: (fontime.\"Year\" = b.\"Year\")  \n-&gt;  Foreign Scan  (cost=1.00..-1.00 rows=100000 width=12)        \nWyj\u015bcie: fontime.\"Year\", ((count(*) * 1000))        \nRelacje: Agregacja na (fontime)        \nZdalne 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)        \nWyj\u015bcie: b.c2, b.\"Year\"        \n-&gt;  Subquery Scan na b  (cost=1.00..999.00 rows=100000 width=12)              \nWyj\u015bcie: b.c2, b.\"Year\"              \n-&gt;  Foreign Scan  (cost=1.00..-1.00 rows=100000 width=12)                    \nWyj\u015bcie: fontime_1.\"Year\", (count(*))                    \nRelacje: Agregacja na (fontime)                    \nZdalne SQL: SELECT \"Year\", count(*) FROM \"default\".ontime GROUP BY \"Year\"(16 rows)<\/code><\/pre>\n<p><\/p>\n<p>Q14: Zapytanie z klauzul\u0105 ORDER BY<\/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: Plan zapytania z klauzul\u0105 ORDER BY<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">PLAN ZAPYTANIA \nMerge Join\u00a0 (cost=2.00..628498.02 rows=50000000 width=12)\u00a0\u00a0 \nWyj\u015bcie: fontime.\"Year\", (((count(*) * 1000)) \/ (count(*)))\u00a0\u00a0 \nUnikalne wewn\u0119trzne: true\u00a0\u00a0 Warunek scalania: (fontime.\"Year\" = fontime_1.\"Year\")\u00a0\u00a0 \n-&gt;\u00a0 GroupAggregate\u00a0 (cost=1.00..499.01 rows=1 width=12)\u00a0 \u00a0 \u00a0 \u00a0 \nWyj\u015bcie: fontime.\"Year\", (count(*) * 1000)\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \nKlucz grupowy: fontime.\"Year\"\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \n-&gt;\u00a0 Foreign Scan na public.fontime\u00a0 (cost=1.00..-1.00 rows=100000 width=4)\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \nZdalne SQL: SELECT \"Year\" FROM \"default\".ontime WHERE ((\"DepDelay\" &gt; 10)) \n            ORDER BY \"Year\" ASC\u00a0\u00a0 \n-&gt;\u00a0 GroupAggregate\u00a0 (cost=1.00..499.01 rows=1 width=12)\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \nWyj\u015bcie: fontime_1.\"Year\", count(*)\u00a0\u00a0 \u00a0 \u00a0 \u00a0 Klucz grupowy: fontime_1.\"Year\"\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \n-&gt;\u00a0 Foreign Scan na public.fontime fontime_1\u00a0 (cost=1.00..-1.00 rows=100000 width=4)\u00a0\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \nZdalne SQL: SELECT \"Year\" FROM \"default\".ontime ORDER BY \"Year\" ASC(16 rows)<\/code><\/pre>\n<p><\/p>\n<p>Wnioski<\/p>\n<p><\/p>\n<p>Wyniki tych eksperyment\u00f3w pokazuj\u0105, \u017ce ClickHouse oferuje naprawd\u0119 dobr\u0105 wydajno\u015b\u0107, a clickhousedb_fdw przynosi korzy\u015bci wydajno\u015bci ClickHouse z PostgreSQL. Chocia\u017c korzystanie z clickhousedb_fdw wi\u0105\u017ce si\u0119 z pewnymi kosztami, s\u0105 one niewielkie i por\u00f3wnywalne z wydajno\u015bci\u0105 osi\u0105gan\u0105 podczas naturalnego uruchamiania w bazie danych ClickHouse. Potwierdza to r\u00f3wnie\u017c, \u017ce fdw w PostgreSQL zapewnia znakomite wyniki.<\/p>\n<p><\/p>\n<p>Telegramowy czat o Clickhouse <noindex><a rel=\"nofollow\" href=\"https:\/\/t.me\/clickhouse_ru\">https:\/\/t.me\/clickhouse_ru<\/a><\/noindex><br \/>\nTelegramowy czat o PostgreSQL <noindex><a rel=\"nofollow\" href=\"https:\/\/t.me\/pgsql\">https:\/\/t.me\/pgsql<\/a><\/noindex><\/p>\n<p>\u0179r\u00f3d\u0142o: <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.1.1 - 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\/pl\/blog\/administrirovanie\/testirovanie-proizvoditelnosti-analiticheskih-zaprosov-v-postgresql-clickhouse-i-clickhousedb_fdw-postgresql\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.1.1\" \/>\n\t\t<meta property=\"og:locale\" content=\"pl_PL\" \/>\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\/pl\/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\udd47Testowanie wydajno\u015bci zapyta\u0144 analitycznych w PostgreSQL, ClickHouse i clickhousedb_fdw (PostgreSQL) | ProHoster","description":"W tym badaniu chcia\u0142em sprawdzi\u0107, jakie poprawy wydajno\u015bci mo\u017cna osi\u0105gn\u0105\u0107, korzystaj\u0105c z \u017ar\u00f3d\u0142a danych ClickHouse, a nie PostgreSQL.","canonical_url":"https:\/\/prohoster.info\/pl\/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":"pl_PL","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\/pl\/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\/pl\/wp-json\/wp\/v2\/posts\/89498","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/pl\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/pl\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/pl\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/pl\/wp-json\/wp\/v2\/comments?post=89498"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/pl\/wp-json\/wp\/v2\/posts\/89498\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/pl\/wp-json\/wp\/v2\/media\/89499"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/pl\/wp-json\/wp\/v2\/media?parent=89498"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/pl\/wp-json\/wp\/v2\/categories?post=89498"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/pl\/wp-json\/wp\/v2\/tags?post=89498"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}