{"id":35259,"date":"2019-10-31T22:03:17","date_gmt":"2019-10-31T19:03:17","guid":{"rendered":"https:\/\/prohoster.info\/blog\/istoriya-odnogo-sql-rassledovaniya\/"},"modified":"2019-10-31T22:03:17","modified_gmt":"2019-10-31T19:03:17","slug":"istoriya-odnogo-sql-rassledovaniya","status":"publish","type":"post","link":"https:\/\/prohoster.info\/sk\/blog\/administrirovanie\/istoriya-odnogo-sql-rassledovaniya","title":{"rendered":"Pr\u00edbeh jedn\u00e9ho SQL vy\u0161etrovania","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>V decembri minul\u00e9ho roka som dostal zauj\u00edmav\u00fa spr\u00e1vu o chybe od t\u00edmu podpory VWO. \u010cas na\u010d\u00edtania jedn\u00e9ho z analytick\u00fdch reportov pre ve\u013ek\u00e9ho firemn\u00e9ho klienta sa zdalo by\u0165 neprimerane dlh\u00e9. Ke\u010f\u017ee je to oblas\u0165 mojej zodpovednosti, okam\u017eite som sa zameral na rie\u0161enie probl\u00e9mu.<\/p>\n<p><\/p>\n<h2>Pozadie<\/h2>\n<p><\/p>\n<p>Aby bolo jasn\u00e9, o \u010dom hovor\u00edm, poviem trochu o VWO. Je to platforma, pomocou ktorej m\u00f4\u017eete sp\u00fa\u0161\u0165a\u0165 r\u00f4zne c\u00edlen\u00e9 kampane na svojich str\u00e1nkach: vykon\u00e1va\u0165 A\/B experimenty, sledova\u0165 n\u00e1v\u0161tevn\u00edkov a konverzie, robi\u0165 anal\u00fdzu predajn\u00e9ho funnelu, zobrazova\u0165 tepeln\u00e9 mapy a prehr\u00e1va\u0165 z\u00e1znamy n\u00e1v\u0161tev.<\/p>\n<p><\/p>\n<p>Ale najd\u00f4le\u017eitej\u0161ie na platforme je zostavovanie reportov. V\u0161etky vy\u0161\u0161ie uveden\u00e9 funkcie s\u00fa medzi sebou prepojen\u00e9. A pre firemn\u00fdch klientov by obrovsk\u00e9 mno\u017estvo inform\u00e1ci\u00ed bolo jednoducho neu\u017eito\u010dn\u00e9 bez silnej platformy, ktor\u00e1 ich prezentuje vo forme pre anal\u00fdzu.<\/p>\n<p><\/p>\n<p>Pomocou platformy m\u00f4\u017eete vytvori\u0165 \u013eubovo\u013en\u00fd dotaz na ve\u013ekom mno\u017estve d\u00e1t. Tu je jednoduch\u00fd pr\u00edklad:<\/p>\n<p><\/p>\n<pre>Zobrazi\u0165 v\u0161etky kliknutia na str\u00e1nke \"abc.com\"\nOD &lt;d\u00e1tumu d1&gt; DO &lt;d\u00e1tumu d2&gt;\npre \u013eud\u00ed, ktor\u00ed\npou\u017e\u00edvali Chrome ALEBO\n(boli v Eur\u00f3pe A pou\u017e\u00edvali iPhone)<\/pre>\n<p><\/p>\n<p>V\u0161imnite si logick\u00e9 oper\u00e1tory. S\u00fa k dispoz\u00edcii pre klientov v rozhran\u00ed dotazu, aby vytv\u00e1rali dotazy \u013eubovo\u013enej zlo\u017eitosti na z\u00edskanie v\u00fdberov.<\/p>\n<p><\/p>\n<h2>Pomal\u00fd dotaz<\/h2>\n<p><\/p>\n<p>Klient, o ktorom hovor\u00edme, sa pok\u00fa\u0161al urobi\u0165 nie\u010do, \u010do intuit\u00edvne by malo pracova\u0165 r\u00fdchlo:<\/p>\n<p><\/p>\n<pre>Zobraz v\u0161etky z\u00e1znamy rel\u00e1ci\u00ed\npre pou\u017e\u00edvate\u013eov, ktor\u00ed nav\u0161t\u00edvili ak\u00fako\u013evek str\u00e1nku\ns URL, kde je \"\\\/jobs\"<\/pre>\n<p><\/p>\n<p>Na tejto str\u00e1nke bolo obrovsk\u00e9 mno\u017estvo n\u00e1v\u0161tevnosti a uchov\u00e1vali sme viac ako mili\u00f3n unik\u00e1tnych URL adries len pre \u0148u. A chceli n\u00e1js\u0165 celkom jednoduch\u00fa \u0161abl\u00f3nu URL, ktor\u00e1 sa t\u00fdka ich obchodn\u00e9ho modelu.<\/p>\n<p>\n<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<h2>Predbe\u017en\u00e9 vy\u0161etrovanie<\/h2>\n<p><\/p>\n<p>Pozrime sa, \u010do sa deje v datab\u00e1ze. Ni\u017e\u0161ie je uveden\u00fd p\u00f4vodn\u00fd pomal\u00fd SQL dotaz:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">SELECT \n    count(*) \nFROM \n    acc_{account_id}.urls as recordings_urls, \n    acc_{account_id}.recording_data as recording_data, \n    acc_{account_id}.sessions as sessions \nWHERE \n    recording_data.usp_id = sessions.usp_id \n    AND sessions.referrer_id = recordings_urls.id \n    AND  (  urls &amp;&amp;  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com\\\/jobs%')::text[]   ) \n    AND r_time &gt; to_timestamp(1542585600) \n    AND r_time =5 \n    AND recording_data.num_of_pages &gt; 0 ;<\/code><\/pre>\n<p><\/p>\n<p>A tu s\u00fa \u010dasovania:<\/p>\n<p><\/p>\n<pre>Pl\u00e1novan\u00fd \u010das: 1,480 ms\n\u010cas vykon\u00e1vania: 1,431,924.650 ms<\/pre>\n<p><\/p>\n<p>Dotaz prech\u00e1dzal 150 tis\u00edc riadkov. Pl\u00e1nova\u010d dotazov uk\u00e1zal p\u00e1r zauj\u00edmav\u00fdch detailov, ale \u017eiadne o\u010dividn\u00e9 \u00fazke miesta.<\/p>\n<p><\/p>\n<p>Po\u010fme sa na tento dotaz pozrie\u0165 bli\u017e\u0161ie. Ako vid\u00edte, vykon\u00e1va <code>JOIN<\/code> tri tabu\u013eky:<\/p>\n<p><\/p>\n<ol>\n<li><strong>sessions<\/strong>: na zobrazenie sedenia inform\u00e1ci\u00ed: prehliada\u010d, pou\u017e\u00edvate\u013esk\u00fd agent, krajina a tak \u010falej.<\/li>\n<li><strong>recording_data<\/strong>: zaznamenan\u00e9 URL adresy, str\u00e1nky, trvanie n\u00e1v\u0161tev<\/li>\n<li><strong>urls<\/strong>: aby sme predi\u0161li duplicite mimoriadne ve\u013ek\u00fdch URL adries, uchov\u00e1vame ich v samostatnej tabu\u013eke.<\/li>\n<\/ol>\n<p><\/p>\n<p>Tie\u017e si v\u0161imnite, \u017ee v\u0161etky na\u0161e tabu\u013eky u\u017e s\u00fa rozdelen\u00e9 pod\u013ea <code>account_id<\/code>. Takto je vyl\u00fa\u010den\u00e1 situ\u00e1cia, ke\u010f kv\u00f4li jedn\u00e9mu obzvl\u00e1\u0161\u0165 ve\u013ek\u00e9mu \u00fa\u010dtu maj\u00fa probl\u00e9my ostatn\u00ed.<\/p>\n<p><\/p>\n<h2>V h\u013eadan\u00ed d\u00f4kazov<\/h2>\n<p><\/p>\n<p>Pri bli\u017e\u0161om sk\u00faman\u00ed vid\u00edme, \u017ee nie\u010do v konkr\u00e9tnom dopyte nie je v poriadku. Stoj\u00ed za to pozrie\u0165 sa na tento riadok:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">urls &amp;&amp; array(\n\tselect id from acc_{account_id}.urls \n\twhere url ILIKE '%enterprise_customer.com\/jobs%'\n)::text[]<\/code><\/pre>\n<p><\/p>\n<p>Prv\u00e1 my\u0161lienka bola, \u017ee mo\u017eno kv\u00f4li <code>ILIKE<\/code> na v\u0161etk\u00fdch t\u00fdchto dlh\u00fdch URL adres\u00e1ch (m\u00e1me viac ako 1,4 mili\u00f3na <strong>unik\u00e1tnych\u00a0<\/strong>URL adries, zozbieran\u00fdch pre tento \u00fa\u010det) m\u00f4\u017ee by\u0165 v\u00fdkon slab\u00fd.<\/p>\n<p><\/p>\n<p>Ale nie \u2013 probl\u00e9m nie je v tomto!<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com\/jobs%';\n  id\n--------\n ...\n(198661 riadkov)\n\n\u010cas: 5,231.765 ms<\/code><\/pre>\n<p><\/p>\n<p>Samotn\u00fd dopyt vyh\u013ead\u00e1vania pod\u013ea vzoru trv\u00e1 iba 5 sek\u00fand. Vyh\u013ead\u00e1vanie pod\u013ea vzoru na mili\u00f3ne unik\u00e1tnych URL adres\u00e1ch zjavne nie je probl\u00e9m.<\/p>\n<p><\/p>\n<p>\u010eal\u0161\u00ed podozriv\u00fd na zozname \u2013 nieko\u013eko <code>JOIN<\/code>. Mo\u017eno ich nadmern\u00e9 pou\u017e\u00edvanie viedlo k spomaleniu? Zvy\u010dajne <code>JOIN<\/code>\u2018Ty\u2019 s\u00fa najzjavnej\u0161\u00edmi kandid\u00e1tmi na probl\u00e9my s v\u00fdkonom, ale neveril som, \u017ee n\u00e1\u0161 pr\u00edpad je typick\u00fd.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">analytics_db=# SELECT\n    count(*)\nFROM\n    acc_{account_id}.urls as recordings_urls,\n    acc_{account_id}.recording_data_0 as recording_data,\n    acc_{account_id}.sessions_0 as sessions\nWHERE\n    recording_data.usp_id = sessions.usp_id\n    AND sessions.referrer_id = recordings_urls.id\n    AND r_time &gt; to_timestamp(1542585600)\n    AND r_time = 5\n    AND recording_data.num_of_pages &gt; 0 ;\n count\n-------\n  8086\n(1 riadok)\n\n\u010cas: 147.851 ms<\/code><\/pre>\n<p><\/p>\n<p>A to takisto nebol n\u00e1\u0161 pr\u00edpad. <code>JOIN<\/code>\u2018Ty\u2019 sa uk\u00e1zali ako ve\u013emi r\u00fdchli.<\/p>\n<p><\/p>\n<h2>Z\u00fa\u017eime okruh podozriv\u00fdch<\/h2>\n<p><\/p>\n<p>Bol som pripraven\u00fd za\u010da\u0165 meni\u0165 dopyt na dosiahnutie ak\u00fdchko\u013evek mo\u017en\u00fdch zlep\u0161en\u00ed v\u00fdkonu. S t\u00edmom sme vyvinuli 2 hlavn\u00e9 n\u00e1pady:<\/p>\n<p><\/p>\n<ul>\n<li><strong>Pou\u017ei\u0165 EXISTS pre poddotaz URL<\/strong>: Chceli sme sa e\u0161te raz presved\u010di\u0165, \u010di nie s\u00fa probl\u00e9my s poddotazom pre URL adresy. Jeden zo sp\u00f4sobov, ako to dosiahnu\u0165, je jednoducho pou\u017ei\u0165 <code>EXISTS<\/code>. <code>EXISTS<\/code> <noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/current\/functions-subquery.html#FUNCTIONS-SUBQUERY-EXISTS\">m\u00f4\u017ee<\/a><\/noindex> v\u00fdrazne zlep\u0161i\u0165 v\u00fdkon, ke\u010f sa dokon\u010d\u00ed okam\u017eite, len \u010do n\u00e1jde jedin\u00fd riadok pod\u013ea podmienky.<\/li>\n<\/ul>\n<p><\/p>\n<pre><code class=\"plaintext\">SELECT\n\tcount(*) \nFROM \n    acc_{account_id}.urls as recordings_urls,\n    acc_{account_id}.recording_data as recording_data,\n    acc_{account_id}.sessions as sessions\nWHERE\n    recording_data.usp_id = sessions.usp_id\n    AND  (  1 = 1  )\n    AND sessions.referrer_id = recordings_urls.id\n    AND  (exists(select id from acc_{account_id}.urls where url  ILIKE '%enterprise_customer.com\/jobs%'))\n    AND r_time &gt; to_timestamp(1547585600)\n    AND r_time =5\n    AND recording_data.num_of_pages &gt; 0 ;\n count\n 32519\n(1 row)\nTime: 1636.637 ms<\/code><\/pre>\n<p><\/p>\n<p>\u00c1no. Poddotaz, ke\u010f je zabalen\u00fd do\u00a0<code>EXISTS<\/code>, rob\u00ed v\u0161etko super r\u00fdchlo. \u010eal\u0161ia logick\u00e1 ot\u00e1zka je, pre\u010do je dotaz s <code>JOIN<\/code>-ami a samotn\u00fd poddotaz r\u00fdchle z individu\u00e1lneho h\u013eadiska, ale spolu s\u00fa hrozne pomal\u00e9?<\/p>\n<p><\/p>\n<ul>\n<li><strong>Premiestnime poddotaz do CTE <\/strong>: ak je dotaz s\u00e1m o sebe r\u00fdchly, m\u00f4\u017eeme jednoducho najprv vypo\u010d\u00edta\u0165 r\u00fdchly v\u00fdsledok a potom ho predlo\u017ei\u0165 hlavn\u00e9mu dotazu.<\/li>\n<\/ul>\n<p><\/p>\n<pre><code class=\"plaintext\">WITH matching_urls AS (\n    select id::text from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com\/jobs%'\n)\n\nSELECT \n    count(*) FROM acc_{account_id}.urls as recordings_urls, \n    acc_{account_id}.recording_data as recording_data, \n    acc_{account_id}.sessions as sessions,\n    matching_urls\nWHERE \n    recording_data.usp_id = sessions.usp_id \n    AND  (  1 = 1  )  \n    AND sessions.referrer_id = recordings_urls.id\n    AND (urls &amp;&amp; array(SELECT id from matching_urls)::text[])\n    AND r_time &gt; to_timestamp(1542585600) \n    AND r_time =5 \n    AND recording_data.num_of_pages &gt; 0;<\/code><\/pre>\n<p><\/p>\n<p>Ale aj to bolo st\u00e1le ve\u013emi pomal\u00e9.<\/p>\n<p><\/p>\n<h2>N\u00e1jdeme vinn\u00edka<\/h2>\n<p><\/p>\n<p>Po cel\u00fd \u010das sa pred o\u010dami objavoval jeden detail, od ktor\u00e9ho som sa neust\u00e1le sna\u017eil odvr\u00e1ti\u0165. Ale ke\u010f\u017ee u\u017e nebolo ni\u010d in\u00e9, rozhodol som sa na neho pozrie\u0165. Hovor\u00edm o <code>&amp;&amp;<\/code> oper\u00e1tore. K\u00fdm <code>EXISTS<\/code> len zlep\u0161il v\u00fdkon, <code>&amp;&amp;<\/code> bol jedin\u00fdm zost\u00e1vaj\u00facim spolo\u010dn\u00fdm faktorom vo v\u0161etk\u00fdch verzi\u00e1ch pomal\u00e9ho dotazu.<\/p>\n<p><\/p>\n<p>Poh\u013eadom na <noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/9.1\/functions-array.html\">dokument\u00e1cii<\/a><\/noindex>, vid\u00edme, \u017ee <code>&amp;&amp;<\/code> sa pou\u017e\u00edva, ke\u010f je potrebn\u00e9 n\u00e1js\u0165 spolo\u010dn\u00e9 prvky medzi dvoma pol\u00ed\u010dkami.<\/p>\n<p><\/p>\n<p>V p\u00f4vodnom dotaze je to:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">AND  (  urls &amp;&amp;  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com\/jobs%')::text[]   )<\/code><\/pre>\n<p><\/p>\n<p>\u010co znamen\u00e1, \u017ee vykon\u00e1vame vyh\u013ead\u00e1vanie pod\u013ea vzoru na na\u0161ich URL, a potom n\u00e1jdeme prienik so v\u0161etk\u00fdmi URL s spolo\u010dn\u00fdmi z\u00e1znamami. Je to trochu zm\u00e4ten\u00e9, ke\u010f\u017ee \"urls\" tu odkazuje na st\u013apec \"urls\" v tabu\u013eke, nie na tabu\u013eku, ktor\u00e1 obsahuje v\u0161etky URL adresy. <code>recording_data<\/code>.<\/p>\n<p><\/p>\n<p>S rast\u00facim podozren\u00edm vo\u010di <code>&amp;&amp;<\/code>, sna\u017eil som sa n\u00e1js\u0165 potvrdenie v pl\u00e1ne dotazu vygenerovanom <code>EXPLAIN ANALYZE<\/code> (mal som u\u017e ulo\u017een\u00fd pl\u00e1n, ale zvy\u010dajne je pre m\u0148a jednoduch\u0161ie experimentova\u0165 v SQL, ne\u017e sa sna\u017ei\u0165 pochopi\u0165 nepreh\u013eadnosti pl\u00e1nova\u010dov dotazov).<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">Filter: ((urls &amp;&amp; ($0)::text[]) AND (r_time &gt; '2018-12-17 12:17:23+00'::timestamp with time zone) AND (r_time = '5'::double precision) AND (num_of_pages &gt; 0))\n                           Odstr\u00e1nen\u00e9 riadky filtrom: 52710<\/code><\/pre>\n<p><\/p>\n<p>Bolo tam nieko\u013eko riadkov filtrov len z <code>&amp;&amp;<\/code>. \u010co znamenalo, \u017ee t\u00e1to oper\u00e1cia nebola len n\u00e1kladn\u00e1, ale aj vykon\u00e1vala sa nieko\u013ekokr\u00e1t.<\/p>\n<p><\/p>\n<p>Skontroloval som to t\u00fdm, \u017ee som izoloval podmienku<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">SELECT 1\nFROM \n    acc_{account_id}.urls as recordings_urls, \n    acc_{account_id}.recording_data_30 as recording_data_30, \n    acc_{account_id}.sessions_30 as sessions_30 \nWHERE \n\turls &amp;&amp;  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com\/jobs%')::text[]<\/code><\/pre>\n<p><\/p>\n<p>Tento dotaz sa vykon\u00e1val pomaly. Preto\u017ee <code>JOIN<\/code>-y s\u00fa r\u00fdchle a poddotazy s\u00fa r\u00fdchle, zostal iba <code>&amp;&amp;<\/code> oper\u00e1tor.<\/p>\n<p><\/p>\n<p>Toto je k\u013e\u00fa\u010dov\u00e1 oper\u00e1cia. V\u017edy mus\u00edme vyh\u013ead\u00e1va\u0165 v hlavnej tabu\u013eke URL, aby sme h\u013eadali pod\u013ea vzoru, a v\u017edy mus\u00edme nach\u00e1dza\u0165 prieniky. Nem\u00f4\u017eeme h\u013eada\u0165 priamo v z\u00e1znamoch URL, preto\u017ee to s\u00fa len ID, ktor\u00e9 sa odkazuj\u00fa na <code>urls<\/code>.<\/p>\n<p><\/p>\n<h2>Na ceste k rie\u0161eniu<\/h2>\n<p><\/p>\n<p><code>&amp;&amp;<\/code> pomal\u00e9, preto\u017ee oba sety s\u00fa obrovsk\u00e9. Oper\u00e1cia bude relat\u00edvne r\u00fdchla, ak nahrad\u00edm <code>urls<\/code> na <code>{ \"http:\/\/google.com\/\", \"http:\/\/wingify.com\/\" }<\/code>.<\/p>\n<p><\/p>\n<p>Za\u010dal som h\u013eada\u0165 sp\u00f4sob, ako v Postgres vykona\u0165 prienik mno\u017e\u00edn bez pou\u017eitia <code>&amp;&amp;<\/code>, ale bez zvl\u00e1\u0161tneho \u00faspechu.<\/p>\n<p><\/p>\n<p>Nakoniec sme sa rozhodli jednoducho vyrie\u0161i\u0165 probl\u00e9m izolovane: daj mi v\u0161etky <code>urls<\/code> riadky, pre ktor\u00e9 URL zodpoved\u00e1 \u0161abl\u00f3ne. Bez \u010fal\u0161\u00edch podmienok to bude \u2014\u00a0<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">SELECT urls.url\nFROM \n\tacc_{account_id}.urls as urls,\n\t(SELECT unnest(recording_data.urls) AS id) AS unrolled_urls\nWHERE\n\turls.id = unrolled_urls.id AND\n\turls.url  ILIKE  '%jobs%'<\/code><\/pre>\n<p><\/p>\n<p>Namiesto\u00a0<code>JOIN<\/code> syntax, jednoducho som pou\u017eil poddotaz a rozvinul <code>recording_data.urls<\/code> pole, aby som mohol priamo aplikova\u0165 podmienku v <code>WHERE<\/code>.<\/p>\n<p><\/p>\n<p>Najd\u00f4le\u017eitej\u0161ie tu je, \u017ee <code>&amp;&amp;<\/code> sa pou\u017e\u00edva na kontrolu, \u010di dan\u00fd z\u00e1znam obsahuje pr\u00edslu\u0161n\u00fd URL. Trochu sa zamyslite, v tejto oper\u00e1cii m\u00f4\u017eete vidie\u0165 presun cez elementy po\u013ea (alebo riadky tabu\u013eky) a zastavenie, ke\u010f sa podmienka (zhody) spln\u00ed. Nie\u010do v\u00e1m to nepripom\u00edna? Aha, <code>EXISTS<\/code>.<\/p>\n<p><\/p>\n<p>Ke\u010f\u017ee na <code>recording_data.urls<\/code> sa d\u00e1 odkazova\u0165 zvonku kontextu poddotazu, ke\u010f k tomu d\u00f4jde, m\u00f4\u017eeme sa vr\u00e1ti\u0165 k n\u00e1\u0161mu star\u00e9mu priate\u013eovi <code>EXISTS<\/code> a obali\u0165 n\u00edm poddotaz.<\/p>\n<p><\/p>\n<p>Spojen\u00edm v\u0161etk\u00e9ho dokopy dostaneme kone\u010dn\u00fd optimalizovan\u00fd dotaz:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">VYBERTE \n    count(*) \nZ \n    acc_{account_id}.urls ako recordings_urls, \n    acc_{account_id}.recording_data ako recording_data, \n    acc_{account_id}.sessions ako sessions \nKDE \n    recording_data.usp_id = sessions.usp_id \n    A (1 = 1) \n    A sessions.referrer_id = recordings_urls.id \n    A r_time &gt; to_timestamp(1542585600) \n    A r_time = 5 \n    A recording_data.num_of_pages &gt; 0 \n    A EXISTUJE (\n        VYBERTE urls.url\n        Z \n            acc_{account_id}.urls ako urls,\n            (VYBERTE unnest(urls) AS rec_url_id Z acc_{account_id}.recording_data) \n            AKO unrolled_urls\n        KDE\n            urls.id = unrolled_urls.rec_url_id A\n            urls.url ILIKE '%enterprise_customer.com\/jobs%'\n    );\n<\/code><\/pre>\n<p><\/p>\n<p>A kone\u010dn\u00e9 vykon\u00e1vacie \u010das <code>\u010cas: 1898.717 ms<\/code> Je \u010das oslavova\u0165?!?<\/p>\n<p><\/p>\n<p>Nie tak r\u00fdchlo! Najsk\u00f4r je potrebn\u00e9 overi\u0165 spr\u00e1vnos\u0165. Bol som ve\u013emi podozrievav\u00fd vo\u010di <code>EXISTS<\/code> optimaliz\u00e1cii, preto\u017ee men\u00ed logiku na skor\u0161ie dokon\u010denie. Mus\u00edme si by\u0165 ist\u00ed, \u017ee sme do dopytu nepridali nevidite\u013en\u00fa chybu.<\/p>\n<p><\/p>\n<p>Jednoduch\u00e1 kontrola spo\u010d\u00edvala vo vykonan\u00ed <code>count(*)<\/code> a na pomal\u00fdch aj r\u00fdchlych dopytoch pre ve\u013ek\u00e9 mno\u017estvo r\u00f4znych s\u00faborov \u00fadajov. Potom som, pre mal\u00e9 podmno\u017estvo \u00fadajov, manu\u00e1lne skontroloval spr\u00e1vnos\u0165 v\u0161etk\u00fdch v\u00fdsledkov.<\/p>\n<p><\/p>\n<p>V\u0161etky kontroly poskytli stabilne pozit\u00edvne v\u00fdsledky. V\u0161etko sme opravili!<\/p>\n<p><\/p>\n<h2>Vy\u0165a\u017een\u00e9 Lekcie<\/h2>\n<p><\/p>\n<p>Z tejto hist\u00f3rie sa d\u00e1 na\u010derpa\u0165 mnoho lekci\u00ed:<\/p>\n<p><\/p>\n<ol>\n<li>Pl\u00e1ny dopytov neprezr\u00e1dzaj\u00fa cel\u00fa hist\u00f3riu, ale m\u00f4\u017eu poskytn\u00fa\u0165 n\u00e1znaky<\/li>\n<li>Hlavn\u00ed podozriv\u00ed nie s\u00fa v\u017edy skuto\u010dn\u00fdmi vinn\u00edkmi<\/li>\n<li>Pomal\u00e9 dopyty m\u00f4\u017eu by\u0165 rozdelen\u00e9, aby sa izolovali \u00fazke miesta<\/li>\n<li>Nie v\u0161etky optimaliz\u00e1cie s\u00fa prirodzene redukt\u00edvne<\/li>\n<li>Pou\u017eitie <code>EXIST<\/code>, kde je to mo\u017en\u00e9, m\u00f4\u017ee vies\u0165 k v\u00fdrazn\u00e9mu n\u00e1rastu v\u00fdkonu<\/li>\n<\/ol>\n<p><\/p>\n<h2>Z\u00e1ver<\/h2>\n<p><\/p>\n<p>Pre\u0161li sme od \u010dasu dopytu pribli\u017ene 24 min\u00fat na 2 sekundy \u2014 dos\u0165 v\u00fdrazn\u00fd n\u00e1rast v\u00fdkonu! Aj ke\u010f tento \u010dl\u00e1nok bol rozsiahly, v\u0161etky experimenty, ktor\u00e9 sme robili, prebehli v jeden de\u0148 a odhadom trvali od 1,5 do 2 hod\u00edn pre optimaliz\u00e1cie a testovania.<\/p>\n<p><\/p>\n<p>SQL je \u00fa\u017easn\u00fd jazyk, ak sa ho neboj\u00edte, ale sna\u017e\u00edte sa ho pochopi\u0165 a vyu\u017ei\u0165. S dobr\u00fdm porozumen\u00edm toho, ako sa vykon\u00e1vaj\u00fa SQL dopyty, ako datab\u00e1za generuje pl\u00e1ny dopytov, ako funguj\u00fa indexy a jednoducho ve\u013ekosti \u00fadajov, s ktor\u00fdmi pracujete, m\u00f4\u017eete excelova\u0165 v optimaliz\u00e1cii dopytov. Rovnako d\u00f4le\u017eit\u00e9 je v\u0161ak neust\u00e1le sk\u00fa\u0161a\u0165 r\u00f4zne pr\u00edstupy a pomaly analyzova\u0165 probl\u00e9m, pri\u010dom identifikujete \u00fazke miesta.<\/p>\n<p><\/p>\n<p>Najlep\u0161ia \u010das\u0165 dosahovania tak\u00fdchto v\u00fdsledkov je vidite\u013en\u00e9 zlep\u0161enie r\u00fdchlosti \u2014 kedy sa spr\u00e1va, ktor\u00e1 sa predt\u00fdm ani nena\u010d\u00edtala, teraz na\u010d\u00edtava takmer okam\u017eite.<\/p>\n<p><\/p>\n<p><strong>Osobitn\u00e1 v\u010faka\u00a0<\/strong>m\u00fdm kolegom\u00a0<em>v t\u00edme Aditye Mishry<\/em>,\u00a0<em>Aditye Gauru\u00a0<\/em>a\u00a0<em><noindex><a rel=\"nofollow\" href=\"https:\/\/twitter.com\/s0ftvar\">Varunovi Malhotrovi\u00a0<\/a><\/noindex><\/em>za brainstorming a\u00a0<em>Dinkarovi Pandirovi\u00a0<\/em>za n\u00e1jdenie d\u00f4le\u017eitej chyby v na\u0161ej kone\u010dnej \u017eiadosti, sk\u00f4r ne\u017e sme sa s \u0148ou definit\u00edvne rozl\u00fa\u010dili!<\/p>\n<p>Zdroj: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/455832\/\">habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u0412 \u0434\u0435\u043a\u0430\u0431\u0440\u0435 \u043f\u0440\u043e\u0448\u043b\u043e\u0433\u043e \u0433\u043e\u0434\u0430 \u044f \u043f\u043e\u043b\u0443\u0447\u0438\u043b \u0438\u043d\u0442\u0435\u0440\u0435\u0441\u043d\u044b\u0439 \u043e\u0442\u0447\u0435\u0442 \u043e\u0431 \u043e\u0448\u0438\u0431\u043a\u0435 \u043e\u0442 \u043a\u043e\u043c\u0430\u043d\u0434\u044b\u00a0\u043f\u043e\u0434\u0434\u0435\u0440\u0436\u043a\u0438 VWO. \u0412\u0440\u0435\u043c\u044f \u0437\u0430\u0433\u0440\u0443\u0437\u043a\u0438 \u043e\u0434\u043d\u043e\u0433\u043e \u0438\u0437 \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u043e\u0442\u0447\u0435\u0442\u043e\u0432 \u0434\u043b\u044f \u043a\u0440\u0443\u043f\u043d\u043e\u0433\u043e \u043a\u043e\u0440\u043f\u043e\u0440\u0430\u0442\u0438\u0432\u043d\u043e\u0433\u043e \u043a\u043b\u0438\u0435\u043d\u0442\u0430 \u043a\u0430\u0437\u0430\u043b\u043e\u0441\u044c \u043d\u0435\u043f\u043e\u043c\u0435\u0440\u043d\u043e \u0431\u043e\u043b\u044c\u0448\u0438\u043c. \u0410 \u0442\u0430\u043a \u043a\u0430\u043a \u044d\u0442\u043e \u0441\u0444\u0435\u0440\u0430 \u043c\u043e\u0435\u0439 \u043e\u0442\u0432\u0435\u0442\u0441\u0442\u0432\u0435\u043d\u043d\u043e\u0441\u0442\u0438, \u044f \u0442\u0443\u0442 \u0436\u0435 \u0441\u043e\u0441\u0440\u0435\u0434\u043e\u0442\u043e\u0447\u0438\u043b\u0441\u044f \u043d\u0430 \u0440\u0435\u0448\u0435\u043d\u0438\u0438 \u043f\u0440\u043e\u0431\u043b\u0435\u043c\u044b. \u041f\u0440\u0435\u0434\u044b\u0441\u0442\u043e\u0440\u0438\u044f \u0427\u0442\u043e\u0431\u044b \u0431\u044b\u043b\u043e \u043f\u043e\u043d\u044f\u0442\u043d\u043e \u043e \u0447\u0451\u043c \u0440\u0435\u0447\u044c, \u044f \u0440\u0430\u0441\u0441\u043a\u0430\u0436\u0443 \u0441\u043e\u0432\u0441\u0435\u043c \u043d\u0435\u043c\u043d\u043e\u0433\u043e \u043e VWO. \u042d\u0442\u043e \u043f\u043b\u0430\u0442\u0444\u043e\u0440\u043c\u0430, [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-35259","post","type-post","status-publish","format-standard","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.5 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u0412 \u0434\u0435\u043a\u0430\u0431\u0440\u0435 \u043f\u0440\u043e\u0448\u043b\u043e\u0433\u043e \u0433\u043e\u0434\u0430 \u044f \u043f\u043e\u043b\u0443\u0447\u0438\u043b \u0438\u043d\u0442\u0435\u0440\u0435\u0441\u043d\u044b\u0439 \u043e\u0442\u0447\u0435\u0442 \u043e\u0431 \u043e\u0448\u0438\u0431\u043a\u0435 \u043e\u0442 \u043a\u043e\u043c\u0430\u043d\u0434\u044b \u043f\u043e\u0434\u0434\u0435\u0440\u0436\u043a\u0438 VWO. \u0412\u0440\u0435\u043c\u044f \u0437\u0430\u0433\u0440\u0443\u0437\u043a\u0438 \u043e\u0434\u043d\u043e\u0433\u043e \u0438\u0437 \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u043e\u0442\u0447\u0435\u0442\u043e\u0432 \u0434\u043b\u044f \u043a\u0440\u0443\u043f\u043d\u043e\u0433\u043e \u043a\u043e\u0440\u043f\u043e\u0440\u0430\u0442\u0438\u0432\u043d\u043e\u0433\u043e \u043a\u043b\u0438\u0435\u043d\u0442\u0430 \u043a\u0430\u0437\u0430\u043b\u043e\u0441\u044c \u043d\u0435\u043f\u043e\u043c\u0435\u0440\u043d\u043e \u0431\u043e\u043b\u044c\u0448\u0438\u043c.\" \/>\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\/sk\/blog\/administrirovanie\/istoriya-odnogo-sql-rassledovaniya\" \/>\n\t\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.5\" \/>\n\t\t<meta property=\"og:locale\" content=\"sk_SK\" \/>\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\u0418\u0441\u0442\u043e\u0440\u0438\u044f \u043e\u0434\u043d\u043e\u0433\u043e SQL \u0440\u0430\u0441\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u043d\u0438\u044f | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u0412 \u0434\u0435\u043a\u0430\u0431\u0440\u0435 \u043f\u0440\u043e\u0448\u043b\u043e\u0433\u043e \u0433\u043e\u0434\u0430 \u044f \u043f\u043e\u043b\u0443\u0447\u0438\u043b \u0438\u043d\u0442\u0435\u0440\u0435\u0441\u043d\u044b\u0439 \u043e\u0442\u0447\u0435\u0442 \u043e\u0431 \u043e\u0448\u0438\u0431\u043a\u0435 \u043e\u0442 \u043a\u043e\u043c\u0430\u043d\u0434\u044b \u043f\u043e\u0434\u0434\u0435\u0440\u0436\u043a\u0438 VWO. \u0412\u0440\u0435\u043c\u044f \u0437\u0430\u0433\u0440\u0443\u0437\u043a\u0438 \u043e\u0434\u043d\u043e\u0433\u043e \u0438\u0437 \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u043e\u0442\u0447\u0435\u0442\u043e\u0432 \u0434\u043b\u044f \u043a\u0440\u0443\u043f\u043d\u043e\u0433\u043e \u043a\u043e\u0440\u043f\u043e\u0440\u0430\u0442\u0438\u0432\u043d\u043e\u0433\u043e \u043a\u043b\u0438\u0435\u043d\u0442\u0430 \u043a\u0430\u0437\u0430\u043b\u043e\u0441\u044c \u043d\u0435\u043f\u043e\u043c\u0435\u0440\u043d\u043e \u0431\u043e\u043b\u044c\u0448\u0438\u043c.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/sk\/blog\/administrirovanie\/istoriya-odnogo-sql-rassledovaniya\" \/>\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=\"2019-10-31T19:03:17+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2019-10-31T19:03:17+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\udd47Pr\u00edbeh jedn\u00e9ho SQL vy\u0161etrovania | ProHoster","description":"V decembri minul\u00e9ho roka som dostal zauj\u00edmav\u00fa spr\u00e1vu o chybe od podpory VWO. \u010cas na\u010d\u00edtania jednej z analytick\u00fdch spr\u00e1v pre ve\u013ek\u00e9ho korpor\u00e1tneho klienta sa zd\u00e1l neprimerane dlh\u00fd.","canonical_url":"https:\/\/prohoster.info\/sk\/blog\/administrirovanie\/istoriya-odnogo-sql-rassledovaniya","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"sk_SK","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\u0418\u0441\u0442\u043e\u0440\u0438\u044f \u043e\u0434\u043d\u043e\u0433\u043e SQL \u0440\u0430\u0441\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u043d\u0438\u044f | ProHoster","og:description":"\u0412 \u0434\u0435\u043a\u0430\u0431\u0440\u0435 \u043f\u0440\u043e\u0448\u043b\u043e\u0433\u043e \u0433\u043e\u0434\u0430 \u044f \u043f\u043e\u043b\u0443\u0447\u0438\u043b \u0438\u043d\u0442\u0435\u0440\u0435\u0441\u043d\u044b\u0439 \u043e\u0442\u0447\u0435\u0442 \u043e\u0431 \u043e\u0448\u0438\u0431\u043a\u0435 \u043e\u0442 \u043a\u043e\u043c\u0430\u043d\u0434\u044b \u043f\u043e\u0434\u0434\u0435\u0440\u0436\u043a\u0438 VWO. \u0412\u0440\u0435\u043c\u044f \u0437\u0430\u0433\u0440\u0443\u0437\u043a\u0438 \u043e\u0434\u043d\u043e\u0433\u043e \u0438\u0437 \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u043e\u0442\u0447\u0435\u0442\u043e\u0432 \u0434\u043b\u044f \u043a\u0440\u0443\u043f\u043d\u043e\u0433\u043e \u043a\u043e\u0440\u043f\u043e\u0440\u0430\u0442\u0438\u0432\u043d\u043e\u0433\u043e \u043a\u043b\u0438\u0435\u043d\u0442\u0430 \u043a\u0430\u0437\u0430\u043b\u043e\u0441\u044c \u043d\u0435\u043f\u043e\u043c\u0435\u0440\u043d\u043e \u0431\u043e\u043b\u044c\u0448\u0438\u043c.","og:url":"https:\/\/prohoster.info\/sk\/blog\/administrirovanie\/istoriya-odnogo-sql-rassledovaniya","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":"2019-10-31T19:03:17+00:00","article:modified_time":"2019-10-31T19:03:17+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"35259","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":"2026-01-21 22:33:19","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-03-01 02:07:28","updated":"2026-01-21 22:33:19","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts\/35259","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/comments?post=35259"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts\/35259\/revisions"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/media?parent=35259"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/categories?post=35259"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/tags?post=35259"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}