Vlákno názorů k článku Korelované vnořené dotazy: proč nepoužívat a čím nahradit od pavel - Zaujal mě název článku, ale nemohu souhlasit s...
-
pavel (neregistrovaný)
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_dateDruhá 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 msU 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 msPř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
-
pavel (neregistrovaný)
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;
