imported>Pavel v 18. 6. 2009, 14:26

2009-06-18T14:26:33Z

Nová stránka

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

Optimalizace dotazu nad STAR schématem - Historie editací

imported>Pavel v 18. 6. 2009, 14:26