Vlákno názorů k článku Zákys jménem flattening od krtek - Velmi pekny clanek, sice ja delam s Oracle,...

Velmi pekny clanek, sice ja delam s Oracle, ale rad si prectu i o jinych databazich aby byl clovek trochu v obraze.
Nevite proc se tak brani zavedeni hintu, v oracle je to bezna vec ktera celkem dost pomaha. Normalne clovek vsechno nechava na optimalizatoru a az v pripade nejakych problematickych dotazu clovek vetsinou optimalizuje rucne a to vetsinou pomoci hintu.

tak tak, DB2 ma taky hinty, nevim jak je tomu u MS SQL

jo chce to podporu pro uzivatelem nastavovatelne jak moc ma planer optimalizovat http://www.pdc.kth.se/doc/SP/manuals/db2-7.1/html/db2s0/frame3.htm#sqls0661

jinak db2 je zadara pro linux i pro komercni pouziti. vubec db2 je hezka db, kdyz porovnate sql jazyk oracle a mysql, tak db2 ho ma nejlepsi, nejsou tam zadny nadstandardni zbytecnosti pro ruzne speedup hacky.

btw proc name pgsql podporu pro release savepoint? mysql to uz umi.

Chapu, co pisete, ale prijde mi to jako purismus; jako povyseni idelogie nad praktickou pouzitelnost.

A v praktickem software, aspon ve vsech projektech, kdy jsem se pohyboval, je dulezitejsi predikovatelnost doby nejhorsiho pripadu nezli prumerny cas nebo cas nejlepsiho pripadu. A podle me zkusenosti mi tuto predikovatelnost da hint, nebot hintem stabilizuji execution plan; jinak nikdy nevyloucim, ze mi spatna statistika vytvori nekolikadenni selecty.

Chapu, ze SQL je vymyslen jako jazyk typu "reknete mi, co vam mam dat, ne jak to mam ziskat" a ze hinty zkali tuto jeho pruzracnost.

Ovsem z druhe strany vzato: Optimalizator se ze statistik snazi zjistit vztahy mezi daty, podstatne pro efektivni provadeni prikazu. V aplikacni domene se ale muze stat, ze mezi daty jsou dalsi vztahy, ktere ze statistiky nezjistim (jako ze statistiky datumu nezjistim, ze po pondelku vzdy nasleduje utery). Pak potrebuji zpusob, jak bud optimalizator vyradit a zoptimalizovat to sam, anebo jak optimalizatoru ten vztah vysvetlit. "Hint" a "doprogramovavani optimalizatoru" jsou pak uz jen ruzne odstiny tehoz principu, pricemz hint je jednodussi, prehlednejsi a mene riskantni.

Ale vzdyt cely tenhle clanek je o tom, ze v pg hinty jsou. Akorat nemaji syntaxi /*+USE_HASH(a b)*/ ale OFFSET 0.

Prijde mi to cele takove zabavne intelektualne nepoctive :-)

Problém je v tom, že když se v DB změní data, například po přesunutí aplikace na produkční server, tak vám hinty více uškodí, než pomůžou.

Jestli chcete používat hinty, tak budete při každé změně rozložení dat přepisovat hromadu selectů v aplikaci.

Taky mi to připadá jako fundamentalismus říct, že hinty jsou špatné a basta. A v tomto světle je pak čiré farizejství, když si tam jeden hint převlečený za OFFSET nechají; když už optimalizovat, tak pořádně a OFFSET 0 by se měl vyhodnotit jako zbytečná klauzule, ne? Když to dotáhnu ad absurdum, tímto stylem by se taky mohlo zakazovat použití indexu přidáním "AND sloupec=sloupec" do klauzule WHERE.

Ne vždycky je nutné nad tabulkou vytvářet statistiky -- např. při různých agregacích a transformacích dat pro datový sklad. Např. vím, že ve vstupní tabulce mám ke každému identifikátoru max. 3 záznamy a unikátních identifikátorů jsou tam třeba 3 miliony a tuto tabulku potřebuju sumarizovat podle těch ID. Planner to chce groupovat hashováním, protože netuší, ze těch unikátních identifikatrů je tolik. Buď mu můžu říct, ať si spočítá statistiky, anebo připsat na konec dotazu kouzelné "option (order group)", čímž mu poradím, že je rychlejší si tabulku sesortovat a pak už jet sekvenčně. (což se testováním ukázalo jako rychlejší a po vytvoření statistik na to planner přijde taky) Ale vzhledem k tomu, že tato operace je jednorázová, pak se tabulka smaže a příště se bude agregovat zase nová tabulka, je to vytváření statistik trochu drahý "hint". (ten zápis hintu je pro MS SQL, čímž taky odpovídám na dotaz někde v diskuzi, jestli MS SQL má hinty)

Každopádně, uznávám, že je to dost specifický případ. Asi bych si hodně rozmýšlel použít hint někde v kódu aplikace, kde fakt hrozí, že sice zrovna teď mi to pomůže, ale až se změní charakter dat, tak si na tom ta aplikace vyláme zuby, kdežto se statistikama se tomu prostě přizpůsobí.

Jinak pro zajimavost, někdy před rokem a půl jsem u Postgresu narazil na toto zajímavé chování:

explain select neco_id from tabulka where neco_id between 100000 and 150000 order by neco_id;

navrhoval použít seq scan tabulky (a bylo to pomalé, v tabulce bylo tusim cca 700 tis. záznamů).
Pokud jsem to přepsal na

select * from
(select neco_id from tabulka where neco_id betweeen 100000 and 125000
union all
select neco_id from tabulka where neco_id betweeen 125001 and 150000) x
order by neco_id

tak uz se pouzil index scan (+ append).
Na tom, ze hinty jsou spatne asi neco bude, protoze me to aspon prinutilo podivat se do manualu a upravit parametr random_page_cost a pak se pouzil index i v prvnim pripade. Ten analyzer vykonostnich charakteristik disku, jak nekdo v diskuzi navrhoval, asi neni spatny napad :)

V zásadě platí, že hint by měl dodat optimalizátoru dodatečnou informaci, kterou ze statistik nedostane, nebo je zavádějící.

Nepoužívám a neznám PostgreSQL, používám ORACLE, ale i tato finta může být nebezpečná a dost bolet. Jde o to, že použitím OFFSET 0 podle mého názoru dojde k materializaci poddotazu (vytvoření výsledku v temporary paměti) a pokud je výsledkem poddotazu velká množina, tak výsledek může být horší než použití suboptimálního indexu.

To byl zřejmě případ Vašeho kolegy, který 10% dotazů zrychlil a u zbylých 90% došlo k trojnásobnému spomalení. Prostě to nejde použít pokaždé, pokud by to šlo, optimizer by to už dávno tak dělal :-)

Jinak v ORACLE se používá stejný trik právě tehdy, kdy chcete provést materializaci poddotazu, protože není vhodné, aby optimizer použil unnesting (nevím český ekvivalent a odhnízdění mi přijde divné). Použije se rownum, což je virtuální sloupec, který počítá řádky výstupu. Pokud bych použil Váš příklad, tak ten dotaz by vypadal takto:

select * from (select t1.*, rownum r from t1 where a = 20) s where s.b = 20

Toto provede v podstatě totéž, čehož chcete dosáhnout Vy, tj. vyhodnotí nejdříve vnořený dotaz, a na to aplikuje filter b = 20.

Protože tímto trikem napravujete statistiky, které neodpovídají datům, další technikou, která je použitelná v ORACLE, je modifikace statistik, ale to už je většinou černá magie a měla by být používaná ještě s větším rozmyslem, než "flattening"

Doufám, že to nevyznělo, že modifikace statistik je v ORACLE běžná praxe. Osobně jsem ji ještě nikdy nepoužil. Domnívám se, že k takovému voodoo se dospěje až při databázích v řádu stovek Gb - Tb.

Nevím jak probíhá sběr statistik v pg, ale v ORACLE se u tak velkých tabulek obvykle skenuje pouze vzorek cca 1-5%, což stačí, pokud jsou data rovnoměrně rozložena. Pokud ale nejsou, nebo se náhodou při sběru trefíte do blbých částí, tak statistiky neodpovídají skutečnosti. Pak nastává čas pro voodoo se statistikama.

Nebo když máte aplikace, které nevěří na NULL hodnoty (různé systémy, které jsou "multidatabázové") a tudíž místo NULL používají nějakou konstantu, např. 1.1.1900 pro datum, tak mají problém, protože matou optimizer. Ten spočívá v tom, že optimizer předpokládá, že hodnoty jsou v prostoru rovnoměrně rozdělené a přitom mezi 1.1.1900 a např. 1.1.2000 nemáte žádnou hodnotu, ale mezi 1.1.2000 do 1.1.2007 máte dejme tomu 1mio záznamů. Běžně ale děláte dotazy na konkrétní den, přičemž optimizer si myslí, že na jeden den připadá cca 25 záznamů (1m/39081), ale ve skutečnosti jich je 391 (1m/2557). Při malých datech ten rozdíl není podstatný, ale čím více dat, tím více bude v takových případech optimizer dělat chyby.

Obdobný problém může nastat, pokud se použije např. 1.1.4000 pro nekonečno.

Záludnost modifikace statistik je v tom, že má vliv na všechny dotazy nad danou tabulkou. A nejhorší je, že když statistiku opravíte tak, že odpovída skutečnosti (např. odstraníte vliv toho 1.1.1900), tak vám přestanou fungovat již odladěné dotazy.

V tom mají hinty velkou výhodu, protože i když neřeší přičinu (chybné statistiky), ale mají pouze lokální vliv, takže opravujete pouze to co nefunguje.