{"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\/it\/blog\/administrirovanie\/testirovanie-proizvoditelnosti-analiticheskih-zaprosov-v-postgresql-clickhouse-i-clickhousedb_fdw-postgresql","title":{"rendered":"Test delle prestazioni delle query analitiche in PostgreSQL, ClickHouse e clickhousedb_fdw (PostgreSQL)","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>In questa ricerca, volevo esaminare quali miglioramenti delle prestazioni si possano ottenere utilizzando ClickHouse come fonte di dati anzich\u00e9 PostgreSQL. Sono consapevole dei vantaggi di prestazione che ottengo utilizzando ClickHouse. Questi vantaggi saranno mantenuti se accedo a ClickHouse da PostgreSQL attraverso un'estensione di dati esterna (FDW)? <\/p>\n<p><noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<p>Gli ambienti di database sotto analisi sono PostgreSQL v11, clickhousedb_fdw e il database ClickHouse. In definitiva, eseguiremo vari query SQL da PostgreSQL v11, instradate attraverso il nostro clickhousedb_fdw al database ClickHouse. In seguito, vedremo come le prestazioni di FDW si confrontano con le stesse query eseguite nel nativo PostgreSQL e nel nativo ClickHouse.<\/p>\n<p><\/p>\n<h3 id=\"baza-dannyh-clickhouse\">Database ClickHouse<\/h3>\n<p><\/p>\n<p>ClickHouse \u00e8 un sistema di gestione di database basato su colonne open-source, in grado di raggiungere prestazioni da 100 a 1000 volte pi\u00f9 veloci rispetto ai tradizionali approcci ai database, capace di gestire pi\u00f9 di un miliardo di righe in meno di un secondo.<\/p>\n<p><\/p>\n<h3 id=\"clickhousedb_fdw\">clickhousedb_fdw<\/h3>\n<p><\/p>\n<p>clickhousedb_fdw \u2014 il wrapper per i dati esterni del database ClickHouse, o FDW, \u00e8 un progetto open source di Percona. <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/Percona-Lab\/clickhousedb_fdw\">Ecco il link al repository del progetto 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\/\">A marzo ho scritto un blog che ti racconta di pi\u00f9 sul nostro FDW<\/a><\/noindex>.<\/p>\n<p><\/p>\n<p>Come vedrai, questo fornisce un FDW per ClickHouse che consente di SELECT from e INSERT INTO un database ClickHouse da un server PostgreSQL v11.<\/p>\n<p><\/p>\n<p>Il FDW supporta funzionalit\u00e0 avanzate come aggregate e join. Questo aumenta significativamente le prestazioni sfruttando le risorse del server remoto per queste operazioni ad alta intensit\u00e0 di risorse.<\/p>\n<p><\/p>\n<h3 id=\"benchmark-environment\">Ambiente di benchmark<\/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 socket \/ 28 core \/ 56 thread<\/li>\n<li>Memoria: 256GB di RAM<\/li>\n<li>Storage: Samsung SM863 1.9TB Enterprise SSD<\/li>\n<li>Filesystem: 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: versione 11<\/li>\n<\/ul>\n<p><\/p>\n<h3 id=\"benchmark-tests\">Test di benchmark<\/h3>\n<p><\/p>\n<p>Invece di utilizzare un set di dati generato da una macchina per questo test, abbiamo usato i dati \"Performance over Time reported by the operator's uptime\" dal 1987 al 2018. Puoi accedere ai dati <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/Percona-Lab\/ontime-airline-performance\/blob\/master\/download.sh\">utilizzando il nostro script, disponibile qui<\/a><\/noindex>.<\/p>\n<p><\/p>\n<p>Dimensione del database di 85 GB, fornendo una tabella con 109 colonne.<\/p>\n<p><\/p>\n<h4 id=\"benchmark-queries\">Query di benchmark<\/h4>\n<p><\/p>\n<p>Questi sono i query che ho utilizzato per confrontare ClickHouse, clickhousedb_fdw e PostgreSQL.<\/p>\n<p><\/p>\n<p><strong>Q#<\/strong><br \/>\n<strong>La query contiene aggregati e 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>La query contiene join<\/strong><\/p>\n<p>Q14<br \/>\nSELEZIONA a.Year, c1\/c2 DA (seleziona Anno, conta(<em>)<\/em>1000 come c1 da ontime DOVE DepDelay&gt;10 GRUPPA PER Anno) a UNISCI A (seleziona Anno, conta(*) come c2 da ontime GRUPPA PER Anno) b su a.Year=b.Year ORDINAMENTO PER a.Year;<\/p>\n<p>Q15<br \/>\nSELEZIONA a.\u201dAnno\u201d, c1\/c2 DA (seleziona \u201cAnno\u201d, conta(<em>)<\/em>1000 come c1 DA fontime DOVE \u201cDepDelay\u201d&gt;10 GRUPPA PER \u201cAnno\u201d) a UNISCI A (seleziona \u201cAnno\u201d, conta(*) come c2 DA fontime GRUPPA PER \u201cAnno\u201d) b su a.\u201dAnno\u201d=b.\u201dAnno\u201d;<\/p>\n<p><\/p>\n<p><em>Tabella-1: Query utilizzate nel benchmark<\/em><\/p>\n<p><\/p>\n<h4 id=\"query-executions\">Esecuzioni di query<\/h4>\n<p><\/p>\n<p>Ecco i risultati di ciascuna delle query eseguite con diverse configurazioni del database: PostgreSQL con e senza indici, ClickHouse e clickhousedb_fdw. Il tempo \u00e8 mostrato in millisecondi.<\/p>\n<p><\/p>\n<p><strong>Q#<\/strong><br \/>\n<strong>PostgreSQL<\/strong><br \/>\n<strong>PostgreSQL (Indicizzato)<\/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>Tabella-1: Tempo impiegato per eseguire le query utilizzate nel benchmark<\/em><\/p>\n<p><\/p>\n<p>Visualizzazione dei risultati<\/p>\n<p><\/p>\n<p>Il grafico mostra il tempo di esecuzione della query in millisecondi, l'asse X mostra il numero della query dalle tabelle sopra, mentre l'asse Y mostra il tempo di esecuzione in millisecondi. I risultati di ClickHouse e i dati ottenuti da postgres tramite clickhousedb_fdw sono mostrati. Dalla tabella si evince che esiste una grande differenza tra PostgreSQL e ClickHouse, ma una minima differenza tra ClickHouse e clickhousedb_fdw.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Test delle prestazioni delle query analitiche in PostgreSQL, ClickHouse e clickhousedb_fdw (PostgreSQL)\" src=\"\/wp-content\/uploads\/2020\/07\/e084243ea7b327f30de5cb78339d3d3a.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Questo grafico mostra la differenza tra ClickhouseDB e clickhousedb_fdw. Nella maggior parte delle interrogazioni, le spese generali di FDW non sono cos\u00ec elevate e risultano appena significative, tranne che per la Q12. Questa interrogazione include join e una clausola ORDER BY. A causa della clausola ORDER BY, GROUP\/BY e ORDER BY non vengono omessi fino a ClickHouse.<\/p>\n<p><\/p>\n<p>Nella tabella 2 vediamo un picco nei tempi delle interrogazioni Q12 e Q13. Ripeto, questo \u00e8 causato dalla clausola ORDER BY. Per confermare ci\u00f2, ho eseguito le interrogazioni Q-14 e Q-15 con e senza la clausola ORDER BY. Senza la clausola ORDER BY, il tempo di completamento \u00e8 di 259 ms, mentre con la clausola ORDER BY \u00e8 di 1364212. Per il debug di questa interrogazione, spiego entrambe le interrogazioni e qui sono riportati i risultati dell'analisi.<\/p>\n<p><\/p>\n<p>Q15: Senza clausola 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: Interrogazione senza clausola ORDER BY<\/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: Query Con CLAUSOLA 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: Piano di Query con CLAUSOLA ORDER BY<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">Piano di QUERY \nMerge Join\u00a0 (costo=2.00..628498.02 righe=50000000 larghezza=12)\u00a0\u00a0 \nOutput: fontime.\"Year\", (((count(*) * 1000)) \/ (count(*)))\u00a0\u00a0 \nUnico interno: vero\u00a0\u00a0 Condizione di unione: (fontime.\"Year\" = fontime_1.\"Year\")\u00a0\u00a0 \n-&gt;\u00a0 GroupAggregate\u00a0 (costo=1.00..499.01 righe=1 larghezza=12)\u00a0 \u00a0 \u00a0 \u00a0 \nOutput: fontime.\"Year\", (count(*) * 1000)\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \nChiave di gruppo: fontime.\"Year\"\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \n-&gt;\u00a0 Scansione estera su public.fontime\u00a0 (costo=1.00..-1.00 righe=100000 larghezza=4)\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \nSQL remoto: SELECT \"Year\" FROM \"default\".ontime WHERE ((\"DepDelay\" &gt; 10)) \n            ORDINA PER \"Year\" ASC\u00a0\u00a0 \n-&gt;\u00a0 GroupAggregate\u00a0 (costo=1.00..499.01 righe=1 larghezza=12)\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \nOutput: fontime_1.\"Year\", count(*)\u00a0\u00a0 \u00a0 \u00a0 \u00a0 Chiave di gruppo: fontime_1.\"Year\"\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \n-&gt;\u00a0 Scansione estera su public.fontime fontime_1\u00a0 (costo=1.00..-1.00 righe=100000 larghezza=4)\u00a0\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \nSQL remoto: SELECT \"Year\" FROM \"default\".ontime ORDINA PER \"Year\" ASC(16 righe)<\/code><\/pre>\n<p><\/p>\n<p>Risultato<\/p>\n<p><\/p>\n<p>I risultati di questi esperimenti mostrano che ClickHouse offre davvero prestazioni elevate, e clickhousedb_fdw offre i vantaggi delle prestazioni di ClickHouse da PostgreSQL. Anche se ci sono alcune spese generali nell'uso di clickhousedb_fdw, esse sono trascurabili e comparabili alle prestazioni ottenute con l'esecuzione diretta nel database ClickHouse. Questo conferma anche che fdw in PostgreSQL produce risultati straordinari.<\/p>\n<p><\/p>\n<p>Chat Telegram su Clickhouse <noindex><a rel=\"nofollow\" href=\"https:\/\/t.me\/clickhouse_ru\">https:\/\/t.me\/clickhouse_ru<\/a><\/noindex><br \/>\nChat Telegram su PostgreSQL <noindex><a rel=\"nofollow\" href=\"https:\/\/t.me\/pgsql\">https:\/\/t.me\/pgsql<\/a><\/noindex><\/p>\n<p>Fonte: <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\/it\/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=\"it_IT\" \/>\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\/it\/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\udd47Test delle prestazioni delle query analitiche in PostgreSQL, ClickHouse e clickhousedb_fdw (PostgreSQL) | ProHoster","description":"In questa ricerca, volevo esaminare quali migliorie nelle prestazioni possono essere ottenute utilizzando la sorgente di dati ClickHouse anzich\u00e9 PostgreSQL. Sono consapevole dei vantaggi in termini di prestazioni che ottengo usando ClickHouse. Questi vantaggi saranno mantenuti se accedo a ClickHouse da PostgreSQL tramite un'interfaccia di dati esterna (FDW)? Gli ambienti di database analizzati sono PostgreSQL v11 e clickhousedb_fdw.","canonical_url":"https:\/\/prohoster.info\/it\/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":"it_IT","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\/it\/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\/it\/wp-json\/wp\/v2\/posts\/89498","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/comments?post=89498"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/posts\/89498\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/media\/89499"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/media?parent=89498"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/categories?post=89498"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/tags?post=89498"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}