Názory k článku
Korelované vnořené dotazy: proč nepoužívat a čím nahradit

WITH aneb CTE slouží řeší hiearchické dotazy - tudíž pro tento článek off topic. Analytické dotazy (warehouse/windowed) nepodporuje žádná OSS databáze, takže proč bych o nich psal. Existuje komerční odnož pg - Bisgres, který je snad používá. Jinak tento článek je určený hlavně začátečníkům v SQL, kteří stále vesele používají korelované dotazy, a to i na Oraclu nebo SQL serveru - přestože tam je k dispozici dost solidní náhrada - včetně OLAPu, který celou tuhle oblast řeší jinak a lépe.

Kdyby si Amicí nehráli na spasitele, a nenasrali hned po invazi Iráčany masivním rozprodejem ropného průmyslu US firmám, nebyli by teď v prdeli. Nicméně si nemyslím, že by firmy byly nějak nucené přecházet na OSS. Oracle má poměrně velké finanční rezervy, takže patrně razantně sníží ceny, stejně tak jako Microsoft a IBM. Jinak Javisti by si měli zvykat na horší platy - a nebo se začít dívat směrem na Rusko.

Proč je u Vás pg tak pomalá netuším, musel bych se na to podívat.

Myslím, že politicky motivované průjmy do odborné diskuse na téma SQL nepatří.

Pravděpodobně to sem nepatří, nicméně je to pravda. Americké ekonomice teď chybí miliardy utopené v Iráku a Afghánistánu. Což se samozřejmě projeví i v IT, jelikož ty nejdůležitější firmy produkující sw jsou z USA nebo na trhu USA závislé.

Myslím, že autor tím chtěl jenom naznačit, že "Lenin" je trochu mimo mísu.

Jak utopene? Kupovali snad ceskou munici? Kupovali americkou, penize jsou porad doma jen maji jine cisla uctu :)

Souhlasim s autorem. Valka je i na USA trosku moc draha sranda. btw: na jinem foru by mel ten prispevek vetsi ohlas

WITH temp1 (col1) AS
(VALUES 0 UNION ALL
SELECT col1 + 1 FROM temp1
WHERE col1 + 1 < 100
)
SELECT * FROM temp1;

hierarchicky dotaz rikate tomuhle?

jinak by to chtelo nejaky lepsi dotazovaci jazyk. slozitejsi sql statementy nejsou zrovna easy to read.

ju. Použití CTE je o něco širší nežli pouze na hierarchické dotazy, i když si myslím, že hlavně u nich se člověk může s CTE setkat. Syntaxe CONNECT BY Oracle je jednodušší, klasičtější. CTE je ale univerzálnější: http://www.ksi.mff.cuni.cz/~pokorny/dj/prezentace/2_73.ppt . Čím dál se SQL posouvá od čistě relačního modelu, tím je to s přirozeností, čitelností a jednoduchostí horší :(. Ukázkou je CTE. Navíc, kromě DB2 si jak Microsoft, tak Oracle berou ze standardu jen části, které se jim hodí, což k ničemu dobrému nepřispívá. Do ANSI SQL se navíc dostává něco z Oracle (analytické dotazy), něco z DB2 (CTE), a občas některé rozumné věci se do standardu nedostanou. Např. Váš příklad se v dá poměrně jednoduše napsat jak ve Firebirdu, tak PostgreSQL (v pg: SELECT col1 FROM generate_series(0,100) temp1(col1)).

Generovat vzestupnou řadu rekurzí (což dělá Váš příklad CTE) je samo o sobě docela hukot. Podobné příklady se používají v učebnicích (patrně proto, aby se ukázalo, jak SQL může být komplikované), podobně jako korelované poddotazy. Praxe je trochu jiná - víc se uplatní procedury.

postgres sql si taky bere z normy jen to co mu vyhovuje, kuprikladu fulltext nema podle normy, navic ignoruje de facto prumyslove standardy (Oracle/db2 kompatibilni syntax). Kdyz neco nedefinuje norma dostatecne presne, tak je to v pgsql vzdycky jinak nez v Oraclu.

Jinak CTE neni hukot, hukot je select dynamicky generujici jine sql statementy, komunita db2 si na tom zaklada. Nekteri lide zjevne nemaji radi stored procedury a nechteji tohle delat v klientu.

db2 ma connect by taky, zrovna jsem si to zkusil.

1 SELECT name,
2 LEVEL,
3 salary,
4 CONNECT_BY_ROOT name AS root,
5 SUBSTR(SYS_CONNECT_BY_PATH(name, ':'), 1, 25) AS chain
6 FROM emp
7 START WITH name = 'Goyal'
8 CONNECT BY PRIOR empid = mgrid
9 ORDER SIBLINGS BY salary;

Kupodivu existuje standard na fulltext (je částí SQL/MM), který ovšem není žádnou databází implementován - i když pg by se měl alespoň jedním operátorem ke standardu přiblížit. Cílem PostgreSQL není být Oracle kompatibilní databází, ale ANSI SQL kompatilní, o to se snaží komerční odnož EnterpriseDB. Navíc extenze ANSI SQL podporují minimálně dva různé nekompatibilní zápisy: klasické funkce nebo metody, takže je v tom stejně hokej. Zatím pg implementuje jen věci z ANSI SQL:200x core.

V cem presne je oracle syntaxe mene univerzalni? napriklad ze nepodporuje NOCYCLE a musi se to obchazet silenymi hacky? U Profesora Pokorneho (kdyz uz citujeme) se na prednaskach ukazovalo jak se tomuto vyhnout pomoci toho, ze si postupne do VARCHAR policka ukladam cestu kterou jdu a v kazdem kroku kontroluji zda uz jsem tam nebyl, to mi vubec neprijde elegantni, natoz vykonne.

Slidy z teto serie bych nebral zas tak vazne - jsou to referaty studentu !

CONNECT BY je minimalne stejne silne jako CTE (CTE se pomoci toho da nasimulovat) a ma nejakou funkcionalitu navic (napriklad jiz zminene NOCYCLE, ktere mnoho veci usnadni)

Ja myslim ze Oracle 10gR2 nocycle umi, devitka urcite ne. db2 9.5 nocycle umi, 8micka ne, 9.1 maybe.

Nemám to pouze z této prezentace. WITH se může použít i nerekurzivně - jako pojmenovaná dočasná tabulka existující pouze po dobu provádění dotazu.

Mozno ma dump ako INSERTy :-D. To je fakt neskutocne pomale.

K obnove dat - porovnavate hrusky s jabkama
obnova neceho na RAID1 disky bude podle kvality radice dost pomalejsi nez na stejne disky bez RAID1 !!! (U raid1 se musi vse napsat dvakrat, cteni rychlejsi je).

Predpokladam, ze stroj s 100GB databazi ma RAID5 pole s vice nez 3 diskama, nebo jeste lepe SAN a pak je pochopitelnym omezenim sirka linky k diskum a ne disky jako takove.

Mimochodem I na SAN radici se necha jemnou upravou rozdeleni pomeru read/write cache pomerne zasadne menit rychlost disku z pohledu systemu.

Super, jen tak dál, člověka takové články vytáhnou ze stereotypu a donutí ho kouknout se jestli to nejde jinak ;o)

Ahoj,
článek super. Díky. Mám dotaz k těm korelovaným mezisoučtům. Ten výpočet sale_price tak jak tam je uveden závisí na nějakém defaultním pořadí v jakém pracuje ten select jestli to chápu dobře. Nemělo by tam být někde raději ORDER podle toho datumu, nebo asi spíš primárního klíče. Nebo je to vlastnost PostgreSQL že VŽDY pokud není uvedeno řadí dle primárního klíče? Nevíte jak to je u ostatních databází, jestli je daný nějakou normou že vždy když napíšu SELECT * FROM tabulka vypíše záznamy ve stejném pořadí?

Chápete to dobře. Nad tabulkou history ORDER BY chybí spíš z principu. Do audit tabulek se zapisuje pouze na konec .. proto jsou automaticky seřazené podle id (za předpokladu, že s tou tabulkou tak pracuji). Jakmile bych provedl UPDATE, tak už by to nebylo pravdivé a výpis by byl přeházený. Nicméně hodnoty budou stejné: filtr WHERE product = o.product AND id <= o.id zajistí nezávislost na pořadí zpracování vnějšího dotazu.

Podle normy je pořadí výpisu záznamů nedefinované - bez použití ORDER BY. PostgreSQL zobrazuje záznamy chronologicky - jak byly vloženy do databáze nebo jak byly modifikovány. Nejsem si jistý, snad InnoDB vypisuje záznamy podle primárního klíče, jelikož udržuje fyzicky záznamy v pořadí, které definuje primární klíč.

To "z principu" mi moc není jasné - o jaký pricip jde? Pokud to chci mít setříděné, tak bych měl použít order by, jinak jde o chybu bez ohledu na použitou datbázi.

Korektní zápis by byl s ORDER BY. Pokud ale vím, jak se daný RDBMS chová, a jak pracuji s tabulkou (a v tomto případě vím), tak ORDER BY představuje zbytečnou operaci SORT. Přiznávám, že to není úplně košér. Abzch měl jistotu, musel bych pro všechnu uživatele zakázat UPDATE na tabulce history.

Správné řešení je použití cluster indexu na sloupci id a ORDER BY dle id. Potom by měl ORDER BY neměl generovat další zátěž. U innodb by to mělo fungovat. U pg to ale fungovat nebude, takže je jednodušší zakázat UPDATE a pak se mohu spolehnout na pořadí záznamů (tohle je čistě pragmatický přístup).

databaze bezne delaji synchronized tablescany, pokud jedna transakce nacita tabulku a uz je v pulce a druha ji chce nacitat tablescanem taky, tak ta druha nezacne od zacatku, ale pripoji se k ty prvni a obe dve projedou pulku tabulky spolecne za jedny penize /diskove io/ a pak ta pozdejsi transakce vezme zacatek.

pravda netusim zda tohle umeji oss databaze nepracuji s nima. jen jsem slysel ze mysql se v poslednich verzich jiz dost zlepsilo. pry do nove verze uz planuji online backupy a rollforward.

podle mne je stejne lepsi si vzit free verzi komercni db oproti oss db, nemusite se pak ucit novou db a prepisovat aplikace kdyz budete potrebovat neco co oss databaze neumi nebo kdyz budete pak provozovat aplikaci, ktera nad oss db nejede.

Krom toho lidi co umeji komercni databaze vic berou, kdyz se podivam na platy u nas tak clovek co dela lamp nebere vic jak 2.5 litru a to jeste jen pokud je team leader, protoze tahle prace se da dobre outscorovat ruskym a indickym firmam, tak neni duvod mu platit vic. LAMP patlal je komodita. Javisti to je jina trida, tech kvalitnich je stale nedostatek. Proto taky berou nekolikanasobne vic.

Pritom J2EE neni tak tezka na nauceni, jen se to holt lidi nechteji ucit kdyz uz umi to php a mysql co se naucili nekde na stredni skole. Kdyz j2ee umi, tak se jim pak nesmi sahnout na hibernate, protoze jsou liny se ucit sql. Kdyz umi sql, tak jsou liny se naucit jak funguji zamky aby nemeli v aplikaci races. A kdyz umi i zamky, tak protestuji ze se nechteji ucit psat testcases.

synchronized scan je podporovan v 8.3.

Jinak zbytek nemam potrebu komentovat - Vase firma je velice specificka.

umi taky reordering transakci? to se dela jednak (zejmena) kvuli deadlockum a jednak kvuli performance.

jde o to ze v pripade deadlocku se udela rollback, releasnou se zamky a druha transakce se komitne. pak se grabnou znovu zamky a zkusi udelat zmeny co transakce zamyslela a pokud se nedaji udelat tak se nahlasi deadlock aplikaci a transakce se definitivne zrusi.

i kdyz je to asi chyba aplikace mydlit X locky na zaznamy co nemodifikuje a pak se deadlockovat, ale dost aplikaci (ehm SAP) to rado dela.

Tak reordering transakci to neumi. A neni to ani v ToDo a v pg konferenci jsem o tom v zivote neslysel. Nikdo ale neprovozuje nad PostgreSQL uzasny SAP. Detekovany deadlock skonci vyjimkou. Kazda slusna aplikace musi byt schopna opakovat transakci. Ciste teoreticky, kazda transakce muze skoncit vyjimkou (z nejruznejsich duvodu).

Nojo SAP to je hezká ukázka mezi db teorii a praxi. Transakce jsou obecne pri tvorbe sestav hezká vec, ale kdyz databáze nema mvcc tak se holt všechny ro query jedou v uncommited read a to jeste nestaci k tomu aby se useri dostatecne netloukli o zamky. vetsinou se jeste zapina vypnuti X zamku u insert a delete radek (radky se ignoruji), snizenim X na U u updated radek.

Jak vas tady sleduji, musim reagovat. Pro accounty je java synonimum prusvihu. Drahy vyvoj, pomaly vyvoj, slaby vykon, ktery se musi preprat investicemi do harware. Nic podstaneho krome brandu "Java" do neprinasi. Java je neco jako americke nemovitosti. Nacpalo se do toho tolik penez, ze si nikdo nemuze dovolit rict ze je spatne, jinak se by se zhroutily akciove trhy. Jeden kolega (stary mazak) rika Java je pokus jak privest vyvoj software na zcesti, ktery se podaril. Bohuzel to nerika jako vtipny bonmot, ale jako konstatovani smutne skutecnosti.

A co tedy místo Javy? Cobol, C,objectiveC, VB, C#, Ruby, Lisp, Haskel? Zahrňte prosím do své úvahy robustnost (no malloc-free hell), čitelnost (žádné zběsilé přetížené operátory), udržovatelnost, aktuální dokumentaci a další věci potřebné pro dlouhodobou a velkou investici do software.

Tak zrovna Haskell je docela pěkný příklad... Dost dobře se poddává formální verifikaci programů, dá se v něm realizovat spousta lock-free datových struktur (STM, žádné deadlocky)... Už jsem zaznamenal, že se k němu obracejí i nějaké ty finanční instituce, snad Credit Suisse začíná převádět svůj analytický SW na Haskell. Největší průser s Javou je v tom, že její concurrency model je tak uboze primitivní, že si pak lidi myslí, že concurrency je složitá věc. No jo, ale když Java má tyhle prostředky na úrovni assembleru, tak se pak není co divit. Už i Microsoft se vrhnul na STM (http://research.microsoft.com/~tharris/papers/2005-ppopp-composable.pdf), jsem zvědavý, co jim z toho vyleze.

Jo a abych si nezkazil pověst závorkovýho rejpala, tak ještě doplním, že transakční paměť vzešla původně z lispovských počítačů. :]

My v Jave vyvyjime hodne a pokud muzu srovnat dobu vyvoje projektu s lidma co delaji vetsi veci v LAMPU (zakaznik vyzaduje) tak jsme mnohem rychlejsi (a tudiz levnejsi), vykon Javy je ok, vetsina dnesniho softu nedela zadnou tezkou matyku a vetsinou ceka na odpoved ze site nebo z db a pokud je treba nejaka tezsi matyka jako treba encodovani videa, tak normalka volame xvid kodek stejne jaky kdybychom to meli v C.

Kdyz pisete drahy vyvoj, pomaly vyvoj tak ve srovnani s cim? Python, Ruby, PHP, C, C++? Hardware je dneska strasne levny, kolik stoji 1GB RAM a kolik stoji quadcore procesor. Srovnejte cenu hw a cenu programatorske prace, no kolik bere j2ee programator ? Tady 6+ klacku, prumer tak 8 a porad jeste je jich nedostatek, vzdycky by nam bodli dalsi schopni lidi.

Koupit rychlejsi hw je nejlevnejsi mozne reseni ve srovnani stravit tydny nejakou uber optimalizaci pri ktery si zaprasite datovy model a aplikaci. Navic pro Javu existuji opravdu vyborne tooly od Rational SW, s tema je radost pracovat a generuji panu programatorovi prvni posledni. Asi nejslabsi casti rational baliku je ten clearcase vcs, celkova jeho koncepce je rozhodne netradicne pojata.

Je fakt ze nejsme vyvojarska firma, primarne kodime pro sebe ale pokud to obchod vyzaduje, tak v ramci dobrych obchodnich vztahu holt kodime i pro partnery. Tim myslim pro ty co s nima spolupracujeme uz na jinych projektech nebo spolupracovat chceme a tak je v nasem zajmu aby meli kvalitni soft.

Tim chci rict ze nemam sajn o tom jake IT technologie doporucuje ceska komercni vyvojarska firma svym zakaznikum, rad se necham poucit.

To nemusí být pravda. Poddotazy se naprosto běžně používají - kromě MySQL, která v jejich implementaci pokulhává. Problém je s korelovanými poddotazy, které generují větší zátěž než je na první pohled patrné. Interně většina RDBMS dokáže převést na JOIN, což se v některých případech i blokuje a vynucuje se materializace poddotazu.

Používají se. V každém druhém doporučení ohledně výkonu je uživatel před nimi varován: http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/
http://forge.mysql.com/wiki/6.0_Subquery_Optimization_Cheatsheet
http://www.walkingaround.org/2007/12/03/mysql-subquery-optimization-doesnt-exist/
http://www.mysqlperformanceblog.com/files/presentations/UC2007-MySQL-Performance-Cookbook.pdf

Většinu těch typických optimalizací poddotazů bude mít až šestková řada http://www.mysqlperformanceblog.com/2008/02/06/mysql-60-subquery-optimizations-are-published/

Jinak nemám žádný důvod začínat žabomyší války - toto jsou prostě fakta (a to ještě ze strany lidí, kteří píší MySQL).

S MySQL to není tak úplně pravda, i tam mohou poddotazy dost ušetřit práci. Například na vybrání 1000 záznamů od určitého ID (kde ID mohou být vynechána) nic lepšího pro MySQL není :) Zkusit SELECT * FROM zaznamy WHERE id > $start LIMIT 1000; je na tabulce s několika miliony záznamů s variabilní délkou pomalá a bolestivá vražda databáze, zatímco SELECT * FROM zaznamy WHERE id > $start AND id < (SELECT max(id) FROM (SELECT id FROM zaznamy WHERE id > $start LIMIT 1000) AS data ); doběhne poměrně rychle (id je samozřejmě primární klíč).

Ale máte pravdu, že spousta věcí se v MySQL chová dost zvláštně, takže je vždy výhodnější použít EXPLAIN, než se něco začne používat, jinak se může blbě odhadnout, jak se to bude chovat (třeba u uvedeného příkladu mě ten dost razantní rozdíl v rychlosti nenapadl, ale díky EXPLAIN se to objevilo).

ten dotaz je dobrá šílenost - ale pokud není jiné cesty .. (tímhle se obchází bug v planneru). Nikde jsem nenapsal, že poddotazy v MySQL jsou nepoužitelné. Psal jsem, že i vývojáři v MySQL je nedoporučují, preferují JOINy, a intenzivně pracují na vylepšení.

Nesouhlasím s tím, že by dotaz SELECT * FROM zaznamy WHERE id > $start ORDER BY id LIMIT 1000 mohl MySQL při existenci indexu nad sloupcem id způsobit jakékoliv problémy. Můžete dát někam vzorek dat nebo alespoň výsledek EXPLAIN?

Rád bych se optal, jak se "tváříte" na použití join s View obsahující v prvním příkladu maxima mezd pro každou profesi ?

Proč ne? Výkonnostně je to +/- stejné. Pokud mi pohled zapadá do schématu, tak je správné je vytvořit a používat.

Nejdřív jedna drobná výtka (k příkladu s Rankem): Píšete "Ekvivalentní SQL příkaz je...", ale výsledek dotazu ekvivalentní není.
Pak nevím, jestli je správně použít v ORDER BY jméno sloupce ID, které není uvedeno v na řádku SELECT.
Proč není použito "ORDER BY product, sale_price" ? Tím by se podle mě vyřešily obě mé výtky - nebo se mýlím?

Děkuji

Nemohu si pomoct, ale když porovnám obě výsledné tabulky, tak mi přijdou stejné - které řádky nejsou stejné (tabulek je tam víc, možná se každý díváme někam jinam)? Použití nezobrazeného sloupce v ORDER BY je korektní (v této pozici může být i libovolný výraz). ORDER BY product, sale_price jsem nikde nepoužil z toho důvodu, že jsem nikde nechtěl řadit podle produktu a ceny, tj. rank není podle ceny, ale podle data. tj. prvý nákup, druhý nákup, atd. Pokud bych chtel rank podle ceny, tj. nejdražší, .. pak bych řadil podle ceny.

Podle mě se liší poslední tři řádky:
v ukázce s funkcí report2() jsou to
2007-10-13 | pecivo | 3 | 1
2007-10-10 | pecivo | 3 | 2
2007-10-12 | pecivo | 4 | 3

a v ukázce s Selectem jsou to
2007-10-10 | pecivo | 3 | 1
2007-10-12 | pecivo | 4 | 2
2007-10-13 | pecivo | 3 | 3

což má k "ekvivalentnosti" (jak nás to učí ve škole) dost daleko.

Podle mě by z hlediska čitelnosti článku bylo lepší, kdyby oba výsledky byly skutečně ekvivalentní.

A k tomu ID v ODRER BY: nevíte prosím, jestli to, co píšete, je vlastnost SQL podle normy nebo jen nějakých SQL variant?

Jinak děkuji za článek i za reakci.

ju. Máte pravdu. V té uložené proceduře má být ORDER BY product, id nikoliv product, sale_date. Moje chyba.

Jinak, ohledně ORDER BY .. nemám po normu. Teď na compu jsem našel prezentaci k ANSI SQL 99:

* ORDER BY Extensions
ORDER BY on columns not in the select list
DECLARE CURSOR FOR
SELECT empno, name
FROM employee
ORDER BY salary DESC

* ORDER BY expressions
DECLARE C2 CURSOR FOR
SELECT empno, name
FROM employee
ORDER BY salary + bonus ASC

Mohu malý dotaz z praxe ? Jak načíst data z .dbf tabulky (standardní Dbase III) do PostgreSQL ?
Zatím to dělám "nouzově" přes COPY ... CSV, a ten .csv dělám importem .dbf souboru do OOo a pak uložením do .csv. Bohužel ta data jsou z externího zdroje a jiný než dbf formát neposkytují.
Díky moc. Milan

http://www.postgresql.org/docs/techdocs.3

To opravdu neexistuje ani nástroj pro konverzi .dbf souborů aspoň do .csv formátu ? Pod Windowsema je toho hafo, ale pod Linuxem ani ťuk. Na tom odkazu ten program je kombinace awk a ručně podle struktury dat upravovaného programu v C. To už je snad jednodušší zavolat Calc z OOo. Fakt nic není ????

http://freshmeat.net/projects/dbf/

minimálně existuje několik knihoven, které umožní číst dbfko, a pak si s ním člověk může dělat, co chce.

Treba univerzalni databazova konzole dbMan. http://freshmeat.net/projects/dbman

Neco jsem si hodne davno pro svoji potrebu naprogramoval. Prevadi to DBF do SQL (insertu), popr. do CSV. Jestli ti nevadi zbastleny ceckovy program (zato docela dobre okomentovany), muzu poskytnout, klidne pod GPL, to me je vcelku jedno.

# yum install xbase-utils
# dbfxtrct

Díky, tohle je to pravé ořechové :))

Děkuji za přínosný článek. V MySQL lze některé věci obejít. Např. pořadí lze získat pomocí proměnných:

SET @rank = 0, @product = NULL;
SELECT @rank := IF(@product = product, @rank + 1, 1) AS rank, @product := product FROM history ORDER BY product, id;

Více o pořadí v mém článku.

Zaujal mě název článku, ale nemohu souhlasit s tvrzením, že kumulativní součty uvedené na začátku nelze řešit neprocedurálně bez použití korelovaných poddotazů. Uvedený dotaz lze nahradit následujícím dotazem:

select t1.id, t1.sale_date, t1.product, t1.sale_price, sum(t2.sale_price) from 
history t1 inner join history t2 on t1.id >= t2.id and t1.product = t2.product
group by t1.id, t1.sale_date, t1.product, t1.sale_price
order by t1.id

Tento dotaz neobsahuje žádný korelovaný poddotaz a výsledek je stejný jako pro dotaz uvedený v článku (kromě toho, že navíc je ve výstupu sloupec ID). Tento dotaz může být v závislosti na datech efektivnější a typicky nebude potřebovat žádný index, protože tabulky se budou joinovat merge joinem nebo hash joinem.

Abych vyzkoušel efektivitu, udělal jsem jednoduchý test. Původní tabulku history jsem opakovaným spouštěním následujícího insertu rozduplikoval na 4096 řádků, vytvořil jsem v článku zmíněný index a spočítal jsem statistiky (vše testováno na PostgreSQL 8.0).

insert into history 
select id + (Select max(id) from history), sale_date, product, sale_price from history;

create index i on history(product, id);

analyze verbose history;

Explain pro původní dotaz nyní vypadá takto (z dotazu jsem odstranil funkci coalesce, která je v tomto případě zbytečná):

test=# explain select sale_date, product, sale_price,
test-#                       (SELECT SUM(sale_price)
test(#                                FROM history
test(#                               WHERE product = o.product
test(#                                 AND id <= o.id) AS total from history o
test-# ;
                               QUERY PLAN
-------------------------------------------------------------------------
 Seq Scan on history o  (cost=0.00..382218.52 rows=4096 width=21)
   SubPlan
     ->  Aggregate  (cost=93.30..93.30 rows=1 width=4)
           ->  Seq Scan on history  (cost=0.00..92.44 rows=342 width=4)
                 Filter: (((product)::text = ($0)::text) AND (id <= $1))

Všimněte si, že v dotazu se vůbec nepoužil index. To je správné chování v případě, že se tabulka a index nevejdou do cache a musí se přistupovat na disk. V tomto případě (tabulka i index jsou malé) by naopak index být použit měl.

Po spuštění dotazu je na mém notebooku vrácena první řádka za 20 sekund.

Explain pro mou variantu vypadá takto:

test=# explain select t1.id, t1.sale_date, t1.product, t1.sale_price, sum(t2.sale_price) from
test-# history t1 inner join history t2 on t1.id >= t2.id and t1.product = t2.product
test-# group by t1.id, t1.sale_date, t1.product, t1.sale_price
test-# order by t1.id
test-# ;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Sort  (cost=95469.97..95480.21 rows=4096 width=25)
   Sort Key: t1.id
   ->  HashAggregate  (cost=95213.97..95224.21 rows=4096 width=25)
         ->  Hash Join  (cost=82.20..75463.09 rows=1580070 width=25)
               Hash Cond: (("outer".product)::text = ("inner".product)::text)
               Join Filter: ("outer".id >= "inner".id)
               ->  Seq Scan on history t1  (cost=0.00..71.96 rows=4096 width=21)
               ->  Hash  (cost=71.96..71.96 rows=4096 width=17)
                     ->  Seq Scan on history t2  (cost=0.00..71.96 rows=4096 width=17)

Opět se nepoužil index (v tomto případě skutečně nemá smysl) a dotaz vrátil první řádku za 7 sekund, tedy téměř 3-krát rychleji než původní varianta. I tak je ale tento výsledek velice pomalý v porovnání s tím, čeho by bylo možné dosáhnout procedurálním kódem.

Pro srovnání nabízím to samé na Oracle 10.2. na stejném notebooku.

SQL> select sale_date, product, sale_price,
  2                        (SELECT SUM(sale_price)
  3                                 FROM history
  4                                WHERE product = o.product
  5                                  AND id <= o.id) AS total from history o
  6  ;

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |  4096 | 86016 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |         |     1 |    14 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| HISTORY |    51 |   714 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I       |     9 |       |     2   (0)| 00:00:01 |
|   4 |  TABLE ACCESS FULL           | HISTORY |  4096 | 86016 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("PRODUCT"=:B1 AND "ID"<=:B2)

První řádek je v tomto případě vrácen okamžitě (za neměřitelnou dobu), protože řádky se vyhodnocují postupně. Pokud upravím dotaz tak, aby se musely načíst všechny řádky, je dotaz vyhodnocen za 1,3 sekundy. Úprava vypadá takto:

select sum(total), sale_date from (								
select sale_date, product, sale_price, 
                      (SELECT SUM(sale_price) 
                               FROM history 
                              WHERE product = o.product 
                                AND id <= o.id) AS total from history o)
group by sale_date					

Druhá varianta dotazu vypadá takto:

SQL> select t1.id, t1.sale_date, t1.product, t1.sale_price, sum(t2.sale_price) total from
  2  history t1 inner join history t2 on t1.id >= t2.id and t1.product = t2.product
  3  group by t1.id, t1.sale_date, t1.product, t1.sale_price
  4  order by t1.id;

---------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |   139K|  4751K|       |  1735   (7)| 00:00:21 |
|   1 |  SORT GROUP BY      |         |   139K|  4751K|    17M|  1735   (7)| 00:00:21 |
|*  2 |   HASH JOIN         |         |   235K|  8064K|       |    94  (88)| 00:00:02 |
|   3 |    TABLE ACCESS FULL| HISTORY |  4096 | 57344 |       |     6   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| HISTORY |  4096 | 86016 |       |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."PRODUCT"="T2"."PRODUCT")
       filter("T1"."ID">="T2"."ID")

První řádek je v tomto případě vrácen za 2,5 sekundy. Tento čas se nezmění ani po úpravě uvedené u předchozího dotazu.

Na závěr ještě doplním třetí variantu dotazu, která využívá toho, že Oracle podporuje analytické funkce.

SQL> select t1.id, t1.sale_date, t1.product, t1.sale_price, sum(t1.sale_price) over (partition by product order by id) total
  2  from history t1
  3  order by t1.id
  4  ;

---------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |  4096 | 86016 |       |    69   (5)| 00:00:01 |
|   1 |  SORT ORDER BY      |         |  4096 | 86016 |   312K|    69   (5)| 00:00:01 |
|   2 |   WINDOW SORT       |         |  4096 | 86016 |   312K|    69   (5)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| HISTORY |  4096 | 86016 |       |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

První řádek je opět vrácen okamžitě. Okamžitě je ale vrácen výsledek i po stejné úpravě jako u prvního dotazu. To znamená, že v tomto případě dokáže Oracle spočítat kumulativní součty celé tabulky v řádu milisekund. Hlavním důvodem takového zrychlení je nízká časová složitost na vyhodnocení dotazu. Při použití analytické funkce je složitost nejhůře nlogn, ve všech ostatní případech je to minimálně n^2.

Hlavním smyslem tohoto příspěvku bylo ukázat i jinou možnost nahrazení korelovaných poddotazů (podobným způsobem by pravděpodobně bylo možné upravit i další dotazy použité v článku). Na konkrétních datech jsem naznačil, jaké jsou performance dopady takového nahrazení. Ale tato analýza v žádném případě není kompletní. Na použitých datech se kumulované součty počítají přes velký počet řádků (v průměru přes 1/8 všech řádků), protože počet různých produktů je řádově menší než počet řádků v tabulce. Výsledky by pravděpodobně vypadaly jinak, kdyby situace vypadala obráceně - tj. pokud by počet různých produktů byl řádově stejný jako počet řádků v tabulce a kumulované součty se tedy počítaly přes jednotky řádků. Pokud si najdu čas, zkusím takový test udělat a doplnit ho sem.

Dobrý den,

dobrá připomínka, na SELF JOIN jsem si nevzpomněl - a máte pravdu, že vůči korelovanému dotazu je to stále vynikající řešení (a to bez toho, že bych se uchýlil k uloženým procedurám).

--bez indexu
postgres=# explain analyze select t1.id, t1.sale_date, t1.product, t1.sale_price, sum(t2.sale_price) from 
history t1 inner join history t2 on t1.id >= t2.id and t1.product = t2.product
group by t1.id, t1.sale_date, t1.product, t1.sale_price
order by t1.id                                          
;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=8241.68..8254.18 rows=5001 width=19) (actual time=2073.968..2081.068 rows=5001 loops=1)
   Sort Key: t1.id
   Sort Method:  quicksort  Memory: 480kB
   ->  HashAggregate  (cost=7871.91..7934.42 rows=5001 width=19) (actual time=2049.964..2060.161 rows=5001 loops=1)
         ->  Merge Join  (cost=824.54..6521.46 rows=108036 width=19) (actual time=83.099..1632.142 rows=171001 loops=1)
               Merge Cond: ((t1.product)::text = (t2.product)::text)
               Join Filter: (t1.id >= t2.id)
               ->  Sort  (cost=412.27..424.77 rows=5001 width=15) (actual time=43.820..51.850 rows=5001 loops=1)
                     Sort Key: t1.product
                     Sort Method:  quicksort  Memory: 324kB
                     ->  Seq Scan on history t1  (cost=0.00..105.01 rows=5001 width=15) (actual time=0.124..10.570 rows=5001 loops=1)
               ->  Sort  (cost=412.27..424.77 rows=5001 width=11) (actual time=39.241..605.987 rows=337001 loops=1)
                     Sort Key: t2.product
                     Sort Method:  quicksort  Memory: 324kB
                     ->  Seq Scan on history t2  (cost=0.00..105.01 rows=5001 width=11) (actual time=0.064..8.448 rows=5001 loops=1)
 Total runtime: 2087.921 ms
(16 rows)

postgres=# explain analyze SELECT sale_date, product, sale_price, 
                  COALESCE((SELECT SUM(sale_price) 
                               FROM history 
                              WHERE product = o.product 
                                AND id <= o.id), 0) as total from history o; query plan ----------------------------------------------------------------------------------------------------------------------------------------- seq scan on o (cost=0.00..438222.42 rows=5001 width=15) (actual time=0.193..8727.369 loops=1) subplan ->  Aggregate  (cost=87.60..87.61 rows=1 width=4) (actual time=1.735..1.736 rows=1 loops=5001)
           ->  Index Scan using history_pkey on history  (cost=0.00..87.59 rows=1 width=4) (actual time=0.804..1.670 rows=34 loops=5001)
                 Index Cond: (id <= $1) filter: ((product)::text 
total runtime: 8746.833 ms (7 rows) 
postgres=# create index fxxx on history (product,id);
CREATE INDEX
Time: 59,664 ms
postgres=# explain analyze select t1.id, t1.sale_date, t1.product, t1.sale_price, sum(t2.sale_price) from 
history t1 inner join history t2 on t1.id >= t2.id and t1.product = t2.product
group by t1.id, t1.sale_date, t1.product, t1.sale_price
order by t1.id
;
                                                               QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=3541.92..3554.42 rows=5001 width=19) (actual time=1524.078..1531.054 rows=5001 loops=1)
   Sort Key: t1.id
   Sort Method:  quicksort  Memory: 480kB
   ->  HashAggregate  (cost=3172.15..3234.66 rows=5001 width=19) (actual time=1500.061..1511.894 rows=5001 loops=1)
         ->  Nested Loop  (cost=0.00..1821.70 rows=108036 width=19) (actual time=0.345..1072.113 rows=171001 loops=1)
               ->  Seq Scan on history t1  (cost=0.00..105.01 rows=5001 width=15) (actual time=0.118..7.966 rows=5001 loops=1)
               ->  Index Scan using fxxx on history t2  (cost=0.00..0.33 rows=1 width=11) (actual time=0.022..0.087 rows=34 loops=5001)
                     Index Cond: (((t2.product)::text = (t1.product)::text) AND (t1.id >= t2.id))
 Total runtime: 1537.842 ms
(9 rows)

Time: 1541,110 ms
postgres=# explain analyze select * from report1();
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Function Scan on report1  (cost=0.00..260.00 rows=1000 width=44) (actual time=227.831..234.816 rows=5001 loops=1)
 Total runtime: 241.232 ms
(2 rows)

Time: 241,836 ms

U mne je SELF JOIN cca 4x rychlejší než varianta používající korelovaný poddotaz. Nicméně uložená procedura běžela 0.27 ms, tj. minimálně 8x rychleji (JOIN), cca 24x rychleji než korelovaný dotaz - což je dost na to, abych preferoval uloženou proceduru nebo na Oraclu analytický dotaz.

díky za opravu a doplnění

A aby život nebyl tak fádní, po vytvoření indexu fxxx je korelovaný dotaz rychlejší než řešení s SELF JOINem (na PostgreSQL)

postgres=# explain analyze SELECT sale_date, product, sale_price, 
                  COALESCE((SELECT SUM(sale_price) 
                               FROM history 
                              WHERE product = o.product 
                                AND id <= o.id), 0) AS total
              FROM history o;
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on history o  (cost=0.00..41541.70 rows=5001 width=15) (actual time=0.135..784.580 rows=5001 loops=1)
   SubPlan
     ->  Aggregate  (cost=8.28..8.29 rows=1 width=4) (actual time=0.149..0.151 rows=1 loops=5001)
           ->  Index Scan using fxxx on history  (cost=0.00..8.27 rows=1 width=4) (actual time=0.021..0.088 rows=34 loops=5001)
                 Index Cond: (((product)::text = ($0)::text) AND (id <= $1))
 Total runtime: 792.701 ms
(6 rows)

Time: 793,588 ms
 

Přestože to má dost velkou cenu, tak je tento dotaz velice rychlý - nicméně stále tato tabulka (5K záznamů) je relativně malá, a bez problémově se vejde do paměti. Pro mne je to docela zajímavá zkušenost vidět dotaz s vyšším nákladem takhle rychle běžet. Pro 20K záznamů 1s uložená proc, 4sec korel podd, 20sec JOIN. Jeden test se nedá generalizovat - snad jen, že optimalizace dotazů zůstává tak trochu magií a s každou verzí mohou přestat platit určitá doporučení. Ale jinak ještě jednou díky za popíchnutí. To, že jsem u příkladu pro korelovaný poddotaz zapomněl index, je docela chyba, měl jsem uvést obě varianty

A aby to bylo ještě zajímavější, tak doplňuji i druhou část testu. Zároveň jsem upgradoval na PostgreSQL 8.3.

Pro druhou část testu jsem zvolil opět stejná základní data, nicméně tentokrát jsem je duplikoval tak, aby pro každý produkt existovaly v průměru 4 řádky. Výsledná tabulka má nyní 16384 řádků (kvůli tomu, že selecty teď budou výrazně rychlejší).

postgres=# select count(*) from history;
 count
-------
 16384
(1 row)

postgres=# select avg(c.c) from (select product, count(*) as c from history group by product) c;
        avg
--------------------
 4.0000000000000000
(1 row)

A nyní jak vycházejí výsledky.

postgres=# analyze verbose history;
INFO:  analyzing "public.history"
INFO:  "history": scanned 109 of 109 pages, containing 16384 live rows and 0 dead rows; 3000 rows in sample, 16384 estimated total rows
ANALYZE

-- bez indexu
postgres=# explain analyze select t1.id, t1.sale_date, t1.product, t1.sale_price, sum(t2.sale_price) from
postgres-# history t1 inner join history t2 on t1.id >= t2.id and t1.product = t2.product
postgres-# group by t1.id, t1.sale_date, t1.product, t1.sale_price
postgres-# order by t1.id;
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=4423.57..4464.53 rows=16384 width=27) (actual time=482.347..515.882 rows=16384 loops=1)
   Sort Key: t1.id
   Sort Method:  quicksort  Memory: 1665kB
   ->  HashAggregate  (cost=3071.89..3276.69 rows=16384 width=27) (actual time=392.198..434.451 rows=16384 loops=1)
         ->  Hash Join  (cost=477.64..2768.01 rows=24310 width=27) (actual time=73.525..270.096 rows=45056 loops=1)
               Hash Cond: ((t1.product)::text = (t2.product)::text)
               Join Filter: (t1.id >= t2.id)
               ->  Seq Scan on history t1  (cost=0.00..272.84 rows=16384 width=23) (actual time=0.011..34.134 rows=16384 loops=1)
               ->  Hash  (cost=272.84..272.84 rows=16384 width=19) (actual time=73.483..73.483 rows=16384 loops=1)
                     ->  Seq Scan on history t2  (cost=0.00..272.84 rows=16384 width=19) (actual time=0.007..35.534 rows=16384 loops=1)
 Total runtime: 548.951 ms
(11 rows)

postgres=# explain analyze SELECT sale_date, product, sale_price,
postgres-#                   COALESCE((SELECT SUM(sale_price)
postgres(#                                FROM history
postgres(#                               WHERE product = o.product
postgres(#                                 AND id <= o.id), 0) as total from history o;
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on history o  (cost=0.00..3944430.73 rows=16384 width=23) (actual time=0.075..54622.125 rows=16384 loops=1)
   SubPlan
     ->  Aggregate  (cost=240.72..240.73 rows=1 width=4) (actual time=3.319..3.321 rows=1 loops=16384)
           ->  Index Scan using history_pkey on history  (cost=0.00..240.72 rows=1 width=4) (actual time=3.293..3.302 rows=3 loops=16384)
                 Index Cond: (id <= $1)
                 Filter: ((product)::text = ($0)::text)
 Total runtime: 54657.508 ms
(7 rows)


postgres=# create index i on history(product, id);
CREATE INDEX
postgres=# analyze verbose history;
INFO:  analyzing "public.history"
INFO:  "history": scanned 109 of 109 pages, containing 16384 live rows and 0 dead rows; 3000 rows in sample, 16384 estimated total rows
ANALYZE
postgres=# explain analyze select t1.id, t1.sale_date, t1.product, t1.sale_price, sum(t2.sale_price) from
postgres-# history t1 inner join history t2 on t1.id >= t2.id and t1.product = t2.product
postgres-# group by t1.id, t1.sale_date, t1.product, t1.sale_price
postgres-# order by t1.id
postgres-# ;
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=4419.64..4460.60 rows=16384 width=27) (actual time=483.112..516.463 rows=16384 loops=1)
   Sort Key: t1.id
   Sort Method:  quicksort  Memory: 1665kB
   ->  HashAggregate  (cost=3067.96..3272.76 rows=16384 width=27) (actual time=392.702..435.252 rows=16384 loops=1)
         ->  Hash Join  (cost=477.64..2749.71 rows=25460 width=27) (actual time=74.035..270.572 rows=45056 loops=1)
               Hash Cond: ((t1.product)::text = (t2.product)::text)
               Join Filter: (t1.id >= t2.id)
               ->  Seq Scan on history t1  (cost=0.00..272.84 rows=16384 width=23) (actual time=0.010..34.247 rows=16384 loops=1)
               ->  Hash  (cost=272.84..272.84 rows=16384 width=19) (actual time=73.992..73.992 rows=16384 loops=1)
                     ->  Seq Scan on history t2  (cost=0.00..272.84 rows=16384 width=19) (actual time=0.007..35.954 rows=16384 loops=1)
 Total runtime: 549.562 ms
(11 rows)

postgres=# explain analyze SELECT sale_date, product, sale_price,
postgres-#                   COALESCE((SELECT SUM(sale_price)
postgres(#                                FROM history
postgres(#                               WHERE product = o.product
postgres(#                                 AND id <= o.id), 0) as total from history o;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on history o  (cost=0.00..136059.50 rows=16384 width=23) (actual time=0.227..854.532 rows=16384 loops=1)
   SubPlan
     ->  Aggregate  (cost=8.28..8.29 rows=1 width=4) (actual time=0.039..0.041 rows=1 loops=16384)
           ->  Index Scan using i on history  (cost=0.00..8.27 rows=1 width=4) (actual time=0.018..0.024 rows=3 loops=16384)
                 Index Cond: (((product)::text = ($0)::text) AND (id <= $1))
 Total runtime: 888.481 ms
(6 rows)

Je vidět, že selfjoin je rychlejší s indexem i bez indexu, protože index vůbec nepotřebuje. V tomto případě se prakticky nevyplatí dělat uloženou proceduru, protože selfjoin funguje téměř stejně a v analogickém čase. Korelovaný subdotaz bez indexu vychází časově hodně špatně, protože provádí opakovaně v podstatě full index scan. S indexem je výkon výrazně lepší, ale stále o trochu horší než selfjoin.

Jak správně uvádíte, korelovaný subdotaz má i při použití indexu velice vysoký cost, přesto běží rychle. Důvod je jednoduchý. Cost je z velké části závislý na počtu čtení diskových stránek, kterých je při použití korelovaného subdotazu hodně (dle costu tipuji 8 stránek na každou řádku tabulky history, tj. celkem 128K). Dotaz běží rychle jedině díky tomu, že se celá tabulka history i její index vejdou do paměti. Pokud by se tabulka nebo index do paměti nevešla, byl by čas běhu korelovaného poddotazu i při použití indexu výrazně větší a odpovídal by costu. Naproti tomu čas běhu selfjoinu by se zásadně nezměnil, protože selfjoin dělá pouze 2 full scany. To je stejný případ jako join 2 velkých tabulek. Pokud budete takový join dělat pomocí nested loops přes index, tak se nedočkáte a hlavičky disku vás nebudou mít moc rády :) V takovém případě je daleko efektivnější použití hash joinu (bez indexů), kterému v optimálním případě stačí pouze jeden full scan na každou z joinovaných tabulek.

A ještě pro zajímavost to samé na Oracle, opět verze 10.2, dotazy jsou opět poupravené.

SQL> select count(*) from history;

  COUNT(*)
----------
     16384

SQL> select avg(c.c) from (select product, count(*) as c from history group by product) c;

  AVG(C.C)
----------
         4

-- bez indexu

SQL> select avg(total) from (
  2  SELECT sale_date, product, sale_price,
  3                    COALESCE((SELECT SUM(sale_price) as total
  4                                 FROM history
  5                                WHERE product = o.product
  6                                  AND id <= o.id), 0) as total from history o
  7  );

AVG(TOTAL)
----------
    33,625

Uplynulo: 00:00:59.20

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |    17 |    22   (5)| 00:00:01 |
|   1 |  SORT AGGREGATE              |             |     1 |    20 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| HISTORY     |     1 |    20 |     8   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C005393 |   147 |       |     2   (0)| 00:00:01 |
|   4 |  SORT AGGREGATE              |             |     1 |    17 |            |          |
|   5 |   TABLE ACCESS FULL          | HISTORY     | 16384 |   272K|    22   (5)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("PRODUCT"=:B1)
   3 - access("ID"<=:B1)
   
   
SQL> select avg(total) from (
  2  select t1.id, t1.sale_date, t1.product, t1.sale_price, sum(t2.sale_price) total from
  3  history t1 inner join history t2 on t1.id >= t2.id and t1.product = t2.product
  4  group by t1.id, t1.sale_date, t1.product, t1.sale_price
  5  order by t1.id );

AVG(TOTAL)
----------
    33,625

Uplynulo: 00:00:00.09

-----------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |    13 |       |    88   (6)| 00:00:02 |
|   1 |  SORT AGGREGATE       |         |     1 |    13 |       |            |          |
|   2 |   VIEW                |         |  3277 | 42601 |       |    88   (6)| 00:00:02 |
|   3 |    HASH GROUP BY      |         |  3277 |   147K|   376K|    88   (6)| 00:00:02 |
|*  4 |     HASH JOIN         |         |  3277 |   147K|       |    45   (7)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| HISTORY | 16384 |   320K|       |    22   (5)| 00:00:01 |
|   6 |      TABLE ACCESS FULL| HISTORY | 16384 |   416K|       |    22   (5)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."PRODUCT"="T2"."PRODUCT")
       filter("T1"."ID">="T2"."ID")
       

SQL> create index i on history(product, id);

Index vytvo°en.

Uplynulo: 00:00:00.20
       
SQL> select avg(total) from (
  2  SELECT sale_date, product, sale_price,
  3                    COALESCE((SELECT SUM(sale_price) as total
  4                                 FROM history
  5                                WHERE product = o.product
  6                                  AND id <= o.id), 0) as total from history o
  7  );

AVG(TOTAL)
----------
    33,625

Uplynulo: 00:00:00.15

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    17 |    15   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |         |     1 |    20 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| HISTORY |     1 |    20 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I       |     1 |       |     2   (0)| 00:00:01 |
|   4 |  SORT AGGREGATE              |         |     1 |    17 |            |          |
|   5 |   INDEX FAST FULL SCAN       | I       | 16384 |   272K|    15   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("PRODUCT"=:B1 AND "ID"<=:B2)
       
SQL> select avg(total) from (
  2  select t1.id, t1.sale_date, t1.product, t1.sale_price, sum(t2.sale_price) total from
  3  history t1 inner join history t2 on t1.id >= t2.id and t1.product = t2.product
  4  group by t1.id, t1.sale_date, t1.product, t1.sale_price
  5  order by t1.id )  ;

AVG(TOTAL)
----------
    33,625

Uplynulo: 00:00:00.09

-----------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |    13 |       |    88   (6)| 00:00:02 |
|   1 |  SORT AGGREGATE       |         |     1 |    13 |       |            |          |
|   2 |   VIEW                |         |  3277 | 42601 |       |    88   (6)| 00:00:02 |
|   3 |    HASH GROUP BY      |         |  3277 |   147K|   376K|    88   (6)| 00:00:02 |
|*  4 |     HASH JOIN         |         |  3277 |   147K|       |    45   (7)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| HISTORY | 16384 |   320K|       |    22   (5)| 00:00:01 |
|   6 |      TABLE ACCESS FULL| HISTORY | 16384 |   416K|       |    22   (5)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."PRODUCT"="T2"."PRODUCT")
       filter("T1"."ID">="T2"."ID")
       
SQL> select avg(total) from (
  2  select t1.id, t1.sale_date, t1.product, t1.sale_price, sum(t1.sale_price) over (partition by product order by id) total
  3  from history t1
  4  order by t1.id);

AVG(TOTAL)
----------
    33,625

Uplynulo: 00:00:00.10


-----------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |    13 |       |   274   (3)| 00:00:04 |
|   1 |  SORT AGGREGATE       |         |     1 |    13 |       |            |          |
|   2 |   VIEW                |         | 16384 |   208K|       |   274   (3)| 00:00:04 |
|   3 |    SORT ORDER BY      |         | 16384 |   416K|  1304K|   274   (3)| 00:00:04 |
|   4 |     WINDOW SORT       |         | 16384 |   416K|  1304K|   274   (3)| 00:00:04 |
|   5 |      TABLE ACCESS FULL| HISTORY | 16384 |   416K|       |    22   (5)| 00:00:01 |
-----------------------------------------------------------------------------------------

Výsledky jsou analogické jako na PostgreSQL. Bez indexu je korelovaný subdotaz velice pomalý, s indexem je výkon o několik řádů lepší. Selfjoin je rychlý bez ohledu na index. Analytický dotaz je v tomto případě stejně rychlý jako selfjoin. To je dáno tím, že ke každému productu jsou historii pouze 4 řádky. Pokud se podíváme na costy, tak zjistíme, že v Oracle má korelovaný subdotaz cost odpovídající výslednému času. Zkusme ještě jednu variantu, zvětšíme tabulku tak, aby se nevešla do cache. Potom to s indexem vypadá takto.

SQL> select count(*) from history;

  COUNT(*)
----------
   1048576

SQL> select avg(c.c) from (select product, count(*) as c from history group by product) c;

  AVG(C.C)
----------
         4


SQL> select avg(total) from (
  2  SELECT sale_date, product, sale_price,
  3                    COALESCE((SELECT SUM(sale_price) as total
  4                                 FROM history
  5                                WHERE product = o.product
  6                                  AND id <= o.id), 0) as total from history o
  7  );

AVG(TOTAL)
----------
    33,625

Uplynulo: těžko říct, po 10 minutách koukání na chrochtající disk jsem to vzdal...

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    21 |  1032   (3)| 00:00:13 |
|   1 |  SORT AGGREGATE              |         |     1 |    24 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| HISTORY |     1 |    24 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I       |     1 |       |     3   (0)| 00:00:01 |
|   4 |  SORT AGGREGATE              |         |     1 |    21 |            |          |
|   5 |   INDEX FAST FULL SCAN       | I       |  1048K|    20M|  1032   (3)| 00:00:13 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("PRODUCT"=:B1 AND "ID"<=:B2)
   
   
SQL> select avg(total) from (
  2  select t1.id, t1.sale_date, t1.product, t1.sale_price, sum(t2.sale_price) total from
  3  history t1 inner join history t2 on t1.id >= t2.id and t1.product = t2.product
  4  group by t1.id, t1.sale_date, t1.product, t1.sale_price
  5  order by t1.id)
  6  ;

AVG(TOTAL)
----------
    33,625

Uplynulo: 00:00:05.85

-----------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |    13 |       |  9462   (3)| 00:01:54 |
|   1 |  SORT AGGREGATE       |         |     1 |    13 |       |            |          |
|   2 |   VIEW                |         |   230K|  2931K|       |  9462   (3)| 00:01:54 |
|   3 |    HASH GROUP BY      |         |   230K|    11M|    28M|  9462   (3)| 00:01:54 |
|*  4 |     HASH JOIN         |         |   230K|    11M|    36M|  6367   (4)| 00:01:17 |
|   5 |      TABLE ACCESS FULL| HISTORY |  1048K|    23M|       |  1185   (4)| 00:00:15 |
|   6 |      TABLE ACCESS FULL| HISTORY |  1048K|    29M|       |  1185   (4)| 00:00:15 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."PRODUCT"="T2"."PRODUCT")
       filter("T1"."ID">="T2"."ID")

SQL> select avg(total) from (
  2  select t1.id, t1.sale_date, t1.product, t1.sale_price, sum(t1.sale_price) over (partition by product order by id) total
  3    from history t1
  4    order by t1.id
  5  );

AVG(TOTAL)
----------
    33,625

Uplynulo: 00:00:06.42


-----------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |    13 |       | 18579   (3)| 00:03:43 |
|   1 |  SORT AGGREGATE       |         |     1 |    13 |       |            |          |
|   2 |   VIEW                |         |  1048K|    12M|       | 18579   (3)| 00:03:43 |
|   3 |    SORT ORDER BY      |         |  1048K|    29M|    88M| 18579   (3)| 00:03:43 |
|   4 |     WINDOW SORT       |         |  1048K|    29M|    88M| 18579   (3)| 00:03:43 |
|   5 |      TABLE ACCESS FULL| HISTORY |  1048K|    29M|       |  1185   (4)| 00:00:15 |
-----------------------------------------------------------------------------------------

Při této velikosti tabulky (milion řádek) se naplno projevil efekt popisovaný výše. Při použití selfjoinu je výsledek k dispozici během několika sekund, naopak korelovaný poddotaz nedoběhne ani za 10 minut a disk si může umlátit hlavičky. Cost pro korelovaný poddotaz je navíc úplně mimo. Analytický dotaz stejně jako v předchozím testu vychází obdobně jako selfjoin. Určitě by stálo za to udělat test se stejně velkou tabulkou i na PostgreSQL, ale vzhledem k aktuálnímu času snad někdy jindy. Předpokládám ale, že výsledky budou obdobné jako na Oracle.

V těchto případech hodně záleží na datech. Generoval jsem data funkcí a dostal jsem se k jiným výsledkům. Následující funkce generovaly větší počet produktů:

create or replace function fill(int) returns void as $$declare i integer = 0; d date = '2007-10-10';begin while i < $1 loop for j in 1..(random()*10)::int loop insert into history (sale_date, product, sale_price) values(d, rndstr(), (random()*100)::int); i := i + 1; end loop; d := d + 1; end loop; return; end; $$ language plpgsql;

create or replace function rndstr() returns text as $$select array_to_string(array(select substring( 'abcdefghijklmnopqrstuvw' from (random()*10+1)::int for 1) from generate_series(1, (random()* 5)::int)),'')$$ LANGUAGE SQL;

Pro 100K záznamů je na PostgreSQL nepoužitelný (čekám víc než 30sec) jak korelovaný dotaz, tak SELF JOIN :(. Přičemž uložená procedura běží pouze 4sec. Prostě pro větší tabulky v pg pouze SP a tam, kde jsou k dispozici, tak analytické dotazy.

Tak ještě jednou a lépe:

create or replace function fill(int) 
returns void as $$
declare i integer = 0; d date = '2007-10-10';
begin 
  while i < $1 loop 
    for j in 1..(random()*10)::int loop 
      insert into history (sale_date, product, sale_price) 
         values(d, rndstr(), (random()*100)::int); i := i + 1; 
    end loop; 
    d := d + 1; 
  end loop; 
  return; 
end; $$ language plpgsql;

create or replace function rndstr() 
returns text as $$
select array_to_string(array(select substring( 'abcdefghijklmnopqrstuvw' 
                                               from (random()*10+1)::int 
                                               for 1) 
                                from generate_series(1, 
                                                     (random()* 5)::int)),
                       '')$$ 
LANGUAGE SQL;