Zobrazení zdroje stránky Optimalizace dotazu nad STAR schématem

PostgreSQL nepodporuje optimalizaci "star" schéma. Star schéma je určené pro analytické dotazy - v případě relačních databází pro takzvané ROLAP dotazy. V tomto schématu máme tabulku faktů - která obsahuje pouze cizí klíče a tzv. dimenze.

Dotaz na podmnožinu tabulky faktů by mohl vypadat následovně:
<pre>
select * 
  from f 
       join t1 
       on t1.a = r1 
       join t2 
       on t2.a = r2 
       join t3 
       on t3.a = r3 
       join t4 
       on t4.a = r4 
       join t5 
       on t5.a = r5 
       join t6 
       on t6.a = r6 
       join t7 
       on t7.a = r7 
       join t8 
       on t8.a = r8 
       join t9 
       on t9.a = r9 
       join t10 
       on t10.a = r10 
  where t2.v = 'uajecanehfsiqfjrallqnybkehswupufxnkjpjztyiyznprkeykorpszcgwychqxiktnstrkdpaszhrvccumylzwixirsoblqek'     
    and t8.v = 'eldnkuxotjcjmihdsxcsglejsxwvppydelsxmirtnmcbisazuiyxossrgofngqwidcixmdzmokuxeqqaucahobxvuocvpvtbzdl';
</pre>
PostgreSQL zvolí plán, kdy se postupně filtrují hodnoty a připojují dimensionální tabulky. V tomto případě by ovšem bylo výhodnější co nejdříve omezit tabulku faktů - což bohužel za nás optimalizátor neprovede, a je nutná ruční optimalizace. První možností je kartézský součin dimenzionálních tabulek se známými hodnotami. Tato optimalizace zrychlila dotaz z cca 370 ms na 80 ms.
<pre>
select *                                                                                                                                                         from f                                                                                                                                                                      join t1                                                                                                                                                                on t1.a = r1                                                                                                                                                
       join (select t2.a as t2a, t8.a as t8a, t2.v, t8.v 
                from t2,t8 
               where t2.v = 'uajecanehfsiqfjrallqnybkehswupufxnkjpjztyiyznprkeykorpszcgwychqxiktnstrkdpaszhrvccumylzwixirsoblqek' 
                 and t8.v = 'eldnkuxotjcjmihdsxcsglejsxwvppydelsxmirtnmcbisazuiyxossrgofngqwidcixmdzmokuxeqqaucahobxvuocvpvtbzdl' offset 0) dim 
       on dim.t2a = r2 and dim.t8a = r8 
       join t3 
       on t3.a = r3 
       join t4 
       on t4.a = r4 
       join t5 
       on t5.a = r5 
       join t6 
       on t6.a = r6 
       join t7 
       on t7.a = r7  
       join t9 on t9.a = r9 
       join t10 
       on t10.a = r10;
</pre>

Další možností je použití korelovaných poddotazů. Pozor - po transformovaný dotaz není 100% ekvivalentní původnímu dotazů - může zobrazit některé řádky, které by díky INNER JOINu nebyly zobrazeny. Optimalizace je efektivní pouze tehdy, je-li výsledek řádově menší než tabulka faktů:
<pre>
select (select v from t1 where a = r1), 
       (select v from t2 where a = r2), 
       (select v from t3 where a = r3), 
       (select v from t4 where a = r4), 
       (select v from t5 where a = r5), 
       (select v from t6 where a = r6), 
       (select v from t7 where a = r7),
       (select v from t8 where a = r8),
       (select v from t9 where a = r9), 
       (select v from t10 where a = r10)  
   from f 
  where f.r2 in (select a from t2 where t2.v = 'uajecanehfsiqfjrallqnybkehswupufxnkjpjztyiyznprkeykorpszcgwychqxiktnstrkdpaszhrvccumylzwixirsoblqek' offset 0) 
    and f.r8 in (select a from t8 where t8.v = 'eldnkuxotjcjmihdsxcsglejsxwvppydelsxmirtnmcbisazuiyxossrgofngqwidcixmdzmokuxeqqaucahobxvuocvpvtbzdl' offset 0);
</pre>
Tato optimalizace urychlila dotaz z původních 370 ms na 20 ms.

Poslední varianta je nejrychlejší - nicméně v případě, že pravděpodobně bude nejcitlivější na počet vrácených řádků - po dosažení určitého prahu se může jednat o nejpomalejší metodu.

Vstupem byla náhodně vygenerovaná tabulka faktů o 10 sloupcích a 800 tis. řádcích. V praxi bývá tabulka faktů několikanásobně širší a větší.
==Odkazy==
* http://www.dwoptimize.com/2007/06/cartesian-product-of-dimensions.html
* http://en.wikipedia.org/wiki/Star_schema