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ě:
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';
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.
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;
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ů:
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);
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ší.