abclinuxu.cz AbcLinuxu.cz itbiz.cz ITBiz.cz HDmag.cz HDmag.cz abcprace.cz AbcPráce.cz
AbcLinuxu hledá autory!
Inzerujte na AbcPráce.cz od 950 Kč
Rozšířené hledání
×
eParkomat, startup z ČR, postoupil mezi finalisty evropského akcelerátoru ChallengeUp!
Robot na pivo mu otevřel dveře k opravdovému byznysu
Internet věcí: Propojený svět? Už se to blíží...
dnes 14:55 | Zajímavý software

Byla vydána verze Alpha 1.0 otevřeného operačního systému pro chytré hodinky AsteroidOS. Podporovány jsou hodinky LG G Watch, LG G Watch Urbane, Asus ZenWatch 2 a Sony Smartwatch 3. Ukázka ovládání hodinek na YouTube. Jaroslav Řezník přednášel o AsteroidOS na chytrých hodinkách (videozáznam) na letošní konferenci OpenAlt.

Ladislav Hagara | Komentářů: 0
dnes 13:30 | Zajímavý software

Byly uvolněny zdrojové kódy známé rogue-like hry DoomRL. Počátky hry jsou v roce 2002. Je napsána ve FreePascalu a zdrojový kód je nyní k dispozici na GitHubu pod licencí GNU GPL 2.0. Autor pracuje na nové hře Jupiter Hell, která je moderním nástupcem DoomRL a na jejíž vývoj shání peníze prostřednictvím Kickstarteru.

Blaazen | Komentářů: 0
dnes 13:15 | Pozvánky

Přijďte s námi oslavit vydání Fedory 25. Na programu budou přednášky o novinkách, diskuse, neřízený networking atd. Release Party se bude konat 16. prosince v prostorách společnosti Etnetera. Na party budou volně k dispozici také propagační materiály, nová DVD s Fedorou 25 a samozřejmě občerstvení. Přednášky budou probíhat v češtině. Pro více informací se můžete podívat na web MojeFedora.cz. Jen připomínám, že tentokrát jsme zavedli

… více »
frantisekz | Komentářů: 0
včera 16:38 | Komunita

Byly zveřejněny videozáznamy přednášek a workshopů z letošní konference OpenAlt konané 5. a 6. listopadu v Brně. K videozáznamům lze přistupovat ze stránky na SuperLectures nebo přes program konference, detaily o vybrané přednášce nebo workshopu a dále kliknutím na ikonku filmového pásu. Celkově bylo zpracováno 65 hodin z 89 přednášek a workshopů.

Ladislav Hagara | Komentářů: 0
včera 11:30 | Komunita

Bylo oznámeno, že bude proveden bezpečnostní audit zdrojových kódů open source softwaru pro implementaci virtuálních privátních sítí OpenVPN. Audit provede Matthew D. Green (blog), uznávaný kryptolog a profesor na Univerzitě Johnse Hopkinse. Auditována bude verze 2.4 (aktuálně RC 1, stabilní verze je 2.3.14). Audit bude financován společností Private Internet Access [reddit].

Ladislav Hagara | Komentářů: 4
včera 06:00 | Komunita

Na YouTube byl publikován Blender Institute Reel 2016, ani ne dvouminutový sestřih z filmů, které vznikly za posledních 10 let díky Blender Institutu. V institutu aktuálně pracují na novém filmu Agent 327. Dění kolem filmu lze sledovat na Blender Cloudu. Videoukázka Agenta 327 z června letošního roku na YouTube.

Ladislav Hagara | Komentářů: 0
včera 01:02 | Zajímavý článek

Minulý týden byly vydány verze 1.2.3 a 1.1.7 webového poštovního klienta Roundcube. V oznámení o vydání bylo zmíněno řešení bezpečnostního problému nalezeného společností RIPS a souvisejícího s voláním funkce mail() v PHP. Tento týden byly zveřejněny podrobnosti. Útočník mohl pomocí speciálně připraveného emailu spustit na serveru libovolný příkaz. Stejně, jak je popsáno v článku Exploit PHP’s mail() to get remote code execution z roku 2014.

Ladislav Hagara | Komentářů: 1
8.12. 16:00 | Nová verze

Byla vydána verze 0.98 svobodného nelineárního video editoru Pitivi. Z novinek lze zmínit například přizpůsobitelné klávesové zkratky. Videoukázka práce s nejnovější verzí Pitivi na YouTube.

Ladislav Hagara | Komentářů: 1
8.12. 15:00 | Zajímavý software

Stop motion je technika animace, při níž je reálný objekt mezi jednotlivými snímky ručně upravován a posouván o malé úseky, tak aby po spojení vyvolala animace dojem spojitosti. Jaký software lze pro stop motion použít na Linuxu? Článek na OMG! Ubuntu! představuje Heron Animation. Ten bohužel podporuje pouze webové kamery. Podpora digitálních zrcadlovek je začleněna například v programu qStopMotion.

Ladislav Hagara | Komentářů: 5
7.12. 21:21 | Nová verze Ladislav Hagara | Komentářů: 0
Kolik máte dat ve svém domovském adresáři na svém primárním osobním počítači?
 (32%)
 (24%)
 (29%)
 (7%)
 (5%)
 (3%)
Celkem 808 hlasů
 Komentářů: 50, poslední 29.11. 15:50
Rozcestník
Reklama

Dotaz: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4

22.4.2011 19:58 jeleniste | skóre: 13 | blog: Prokustovo lože
optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Přečteno: 764×
Mám následující problém, mám relativně složitej pohled, v něm (kromě jiného) propojuji následující tabulky:
budovy (id numeric(30), typbud_kod smallint, cislo_domovni (int)...) --řádově miliony záznamů, typbud je cizi klic na t_budov casti_budov (bud_id numeric(30), typbud_kod smallint, cislo_domovni (int)...) --řádově statisíce záznamů, bud_id je foregin key na budovy.id, jedna mudova muze mit 0 - n casti t_budov (kod smallint, typ varchar(30)...)
vystup ma bejt id budovy, domovni_cislo budovy nebo jeji casti, typ
přičemž, když má budova nějaké části, je to id budovy a všechny cisla domovni casti, plus jejich typy, pokud ne, tak to samy pro budovu
no, v tom dotazu je tech tabulek vic (cca sedm), kdyz sem tam pridal ty casti, tak se mi to vsechno zpomalilo o nekolik radu, vsechno mam indexovany..
vyzkousel sem nasledujici moznosti..

/**
  1
  **/
select 
  budovy.id,
  coalesce(casti_budov.cislo_domovni,budovy.cislo_domovni) cislo_domovni,
  t_budov.typ
from
  budovy
left join 
  casti_budov on casti_budov.bud_id = budovy.id
left join t_budov on t_budov.kod = coalesce(casti_budov.typbud_kod, budovy.typbud_kod)

------------------------------------------------
/**
  2
  **/

Select
  bud.id,
  bud.cislo_domovni,
  t_budov.typ
from
  (
     Select
       budovy.id,
       coalesce(casti_budov.cislo_domovni, budovy.cislo_domovni) cislo_domovni,
       coalesce(casti_budov.typbud_kod, budovy.typbud_kod) typbud_kod
     from
       budovy left join casti_budov on casti_budov.bud_id = budovy.id
    ) bud
left join t_budov
on bud.typbud_kod = t_budov.kod
--------------------------------------------------------

/**
  3
  **/
Select
budovy.id,
coalesce(casti_budov.cislo_domovni, budovy.cislo_domovni) cislo domovni,
coalesce(t_bud_ii.typ, t_budov.typ) typ_budovy
from budovy
left join casti_budov on casti_budov.bud_id = budovy.id
left join t_budov on budovy.typbud_kod = t_budov.kod
left join t_budov t_bud_ii on budovy.typbud_kod = t_bud_ii.kod

ale všechno je to zatraceně pomalý, už si nevím rady, jde to napsat nějak efektivnějc?? Mam vyjetý EXPLAIN ANALYZE, ale nějak mě neni jasný, co bych tam měl vidět..

P.S.: Neni v mejch možnostech měnit datovej model, ani verzi postgre..
Díky Jelen
Nejsem blbý, jen se hloupě ptám

Řešení dotazu:


Odpovědi

24.4.2011 10:59 ghost
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Tak me napada, co se zbavit tech left joinu? Jsou to zabijaci vykonu. Pouzivas je proto, ze chces mit ve vypisu i budovy, ktere zjoinovat nejdou ne? Tak co to zkusit rozepsat na to co sparovat jde, co jde s jednou tabulkou a co s obema a pak pouzit union na tyto jednotlive selecty?
Melo by to byt teoreticky rychlejsi - nebude tam zadny left join, pro kazdy select se pouziji spravne indexy. akorat ty selecty fakt omezuj jen na ty data co ma ten jeden ziskat.
Dale me napada, ac s postgresem zase tak casto nedelam a nevim jestli ten problem stale pretrvava. Postgres od urcite slozitosti dotazu pouzival pro vytvoreni exekucniho planu nejakou heuristiku, ktera nedavala zrovna nejlepsi plany. Takze zkusit jeste upravit tu hranici, kdy se vybira ta heuristika.
26.4.2011 07:35 jeleniste | skóre: 13 | blog: Prokustovo lože
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Ty left joiny sou pažravý, nicméně s použitím union v pohledu nemam moc dobrý zkušenosti, ale vyzkoušim díky.
Nejsem blbý, jen se hloupě ptám
24.4.2011 12:19 VM
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Byl by vypis toho EXPLAINu, a popis indexu nad temi tabulkami?
26.4.2011 07:36 jeleniste | skóre: 13 | blog: Prokustovo lože
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Dodám
Nejsem blbý, jen se hloupě ptám
25.4.2011 09:54 pht | skóre: 48 | blog: pht
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Ten explain by pomohl. Mělo by z něj být patrné, jestli se použil index nebo ne. Jinak moje zkušenosti s postgresem: 1) Postgres 8.4 není bůhvíjak chytrý, někdy stačí přeformulovat dotaz a rázem se použije index. 2) Někdy je potřeba přiohnout plánovač příkazem "set enable_seqscan false".
In Ada the typical infinite loop would normally be terminated by detonation.
26.4.2011 20:45 jeleniste | skóre: 13 | blog: Prokustovo lože
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Je to pohled, kterej využívá další pohled.. Je v tom docela dost tabulek
"Nested Loop Left Join  (cost=4039.55..12009.39 rows=1 width=261) (actual time=59041.378..59041.415 rows=1 loops=1)"
"  Join Filter: (bud.id = parcely.bud_id)"
"  ->  Nested Loop Left Join  (cost=0.00..19.49 rows=1 width=138) (actual time=4.920..4.954 rows=1 loops=1)"
"        ->  Nested Loop Left Join  (cost=0.00..11.19 rows=1 width=134) (actual time=4.915..4.948 rows=1 loops=1)"
"              Join Filter: (d_pozemku.kod = parcely.drupoz_kod)"
"              ->  Nested Loop Left Join  (cost=0.00..9.94 rows=1 width=129) (actual time=4.902..4.910 rows=1 loops=1)"
"                    Join Filter: (zp_vyuziti_poz.kod = parcely.zpvypa_kod)"
"                    ->  Index Scan using par_pk on parcely  (cost=0.00..8.31 rows=1 width=73) (actual time=4.838..4.845 rows=1 loops=1)"
"                          Index Cond: (id = 2990212209::numeric)"
"                    ->  Seq Scan on zp_vyuziti_poz  (cost=0.00..1.28 rows=28 width=70) (actual time=0.004..0.019 rows=28 loops=1)"
"              ->  Seq Scan on d_pozemku  (cost=0.00..1.11 rows=11 width=19) (actual time=0.002..0.019 rows=11 loops=1)"
"        ->  Index Scan using tel_pk on telesa  (cost=0.00..8.28 rows=1 width=15) (actual time=0.001..0.001 rows=0 loops=1)"
"              Index Cond: (parcely.tel_id = public.telesa.id)"
"  ->  Hash Left Join  (cost=4039.55..11027.73 rows=76968 width=134) (actual time=26464.128..58984.575 rows=77117 loops=1)"
"        Hash Cond: (COALESCE(cabu.typbud_kod, bud.typbud_kod) = t_budov.kod)"
"        ->  Hash Left Join  (cost=4038.41..10714.87 rows=76968 width=138) (actual time=26464.079..58856.645 rows=77117 loops=1)"
"              Hash Cond: (bud.id = cabu.bud_id)"
"              ->  Hash Left Join  (cost=4030.06..9742.04 rows=76968 width=128) (actual time=26463.614..58743.768 rows=76968 loops=1)"
"                    Hash Cond: (bud.caobce_kod = casti_obci.kod)"
"                    ->  Hash Left Join  (cost=4015.38..8848.03 rows=76968 width=33) (actual time=26462.677..58630.096 rows=76968 loops=1)"
"                          Hash Cond: (bud.tel_id = public.telesa.id)"
"                          ->  Seq Scan on budovy bud  (cost=0.00..1903.68 rows=76968 width=40) (actual time=0.018..85.580 rows=76968 loops=1)"
"                          ->  Hash  (cost=2214.17..2214.17 rows=103617 width=15) (actual time=26262.120..26262.120 rows=103617 loops=1)"
"                                ->  Seq Scan on telesa  (cost=0.00..2214.17 rows=103617 width=15) (actual time=0.017..98.825 rows=103617 loops=1)"
"                    ->  Hash  (cost=12.20..12.20 rows=198 width=103) (actual time=0.885..0.885 rows=198 loops=1)"
"                          ->  Hash Left Join  (cost=4.50..12.20 rows=198 width=103) (actual time=0.226..0.664 rows=198 loops=1)"
"                                Hash Cond: (casti_obci.obce_kod = obce.kod)"
"                                ->  Seq Scan on casti_obci  (cost=0.00..4.98 rows=198 width=58) (actual time=0.012..0.147 rows=198 loops=1)"
"                                ->  Hash  (cost=3.11..3.11 rows=111 width=53) (actual time=0.196..0.196 rows=111 loops=1)"
"                                      ->  Seq Scan on obce  (cost=0.00..3.11 rows=111 width=53) (actual time=0.005..0.099 rows=111 loops=1)"
"              ->  Hash  (cost=5.38..5.38 rows=238 width=25) (actual time=0.433..0.433 rows=238 loops=1)"
"                    ->  Seq Scan on casti_budov cabu  (cost=0.00..5.38 rows=238 width=25) (actual time=0.005..0.211 rows=238 loops=1)"
"        ->  Hash  (cost=1.06..1.06 rows=6 width=17) (actual time=0.022..0.022 rows=6 loops=1)"
"              ->  Seq Scan on t_budov  (cost=0.00..1.06 rows=6 width=17) (actual time=0.004..0.010 rows=6 loops=1)"
"Total runtime: 59041.642 ms"
Nejsem blbý, jen se hloupě ptám
okbob avatar 26.4.2011 21:04 okbob | skóre: 30 | blog: systemakuv_blog | Benešov
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Máte ty tabulky vacuuované?

Z nějakého důvodu tam máte dost pomalý hash left join. Neměnil jste parametry seq_page_cost nebo random_page_cost v configu? Jak máte velkou work_mem?

27.4.2011 07:23 jeleniste | skóre: 13 | blog: Prokustovo lože
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Vacuoval jsem všechno, všechno jsem přeanalyzoval a reindexnul. Navíc, s těma datama se nehejbalo, tudíž to, si myslim ani vacuovat nepotřebovalo. Left join je pomalej, když dam (v jinejch dotazech, kde to jde místo toho inner, tak se to řádově zrychlí, ale s tim se tak nějak počítal..) parametry zjistím a vypíšu, nic se, co vím neměnilo, vše je default..
Nejsem blbý, jen se hloupě ptám
okbob avatar 27.4.2011 07:55 okbob | skóre: 30 | blog: systemakuv_blog | Benešov
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Zkoušel jste FULL VACUUM ?

27.4.2011 07:40 jeleniste | skóre: 13 | blog: Prokustovo lože
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
work_mem = 1 MB
maintenance_work_mem = 16 MB
máme dvě giga ram, pokud to chápu, tak bych tohle měl navýšit.. o kolik?? Díky
Nejsem blbý, jen se hloupě ptám
okbob avatar 27.4.2011 07:53 okbob | skóre: 30 | blog: systemakuv_blog | Benešov
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
work_mem můžete nastavovat per session. Zkuste jestli se zrychlí dotaz
set work_mem to '10MB';
případně jestli pomůže zablokovat hash_join
set enable_hashjoin to off;
pro work_mem musí platit

work_mem * max_connection + shared_buffers + pamet pro zbytek systemu < RAM
27.4.2011 08:32 jeleniste | skóre: 13 | blog: Prokustovo lože
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Vyzkouším to zítra večer, kdybych něco pohnojil, abych měl čas to dostat do původního stavu. V každém případě díky moc, napíšu, jak jsem pochodil. Je.
Nejsem blbý, jen se hloupě ptám
28.4.2011 19:21 jeleniste | skóre: 13 | blog: Prokustovo lože
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Skvělý, díky moc, tohle pomohlo.
Nejsem blbý, jen se hloupě ptám
okbob avatar 28.4.2011 19:34 okbob | skóre: 30 | blog: systemakuv_blog | Benešov
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Můžete být konkrétnější - pro mne i pro ostatní, tak aby se diskuze byla kompletní - co jste udělal, a jak teď dlouho trvá dotaz. Díky :)
28.4.2011 21:01 jeleniste | skóre: 13 | blog: Prokustovo lože
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Jasně, takže nejdříve jsem vyzkoušel, to co radíte vy. Tj.
set work_mem to '10MB';

set enable_hashjoin to off;
Čímž jsem dosáhl zrychlení z necelé minuty na sekundu a malej kousek
"Nested Loop Left Join  (cost=32884.48..34848.96 rows=1 width=291) (actual time=1364.306..1364.348 rows=1 loops=1)"
"  Join Filter: (budovy.id = parcely.bud_id)"
"  ->  Nested Loop Left Join  (cost=0.00..19.49 rows=1 width=138) (actual time=0.089..0.130 rows=1 loops=1)"
"        ->  Nested Loop Left Join  (cost=0.00..11.19 rows=1 width=134) (actual time=0.075..0.104 rows=1 loops=1)"
"              Join Filter: (d_pozemku.kod = parcely.drupoz_kod)"
"              ->  Nested Loop Left Join  (cost=0.00..9.94 rows=1 width=129) (actual time=0.068..0.072 rows=1 loops=1)"
"                    Join Filter: (zp_vyuziti_poz.kod = parcely.zpvypa_kod)"
"                    ->  Index Scan using par_pk on parcely  (cost=0.00..8.31 rows=1 width=73) (actual time=0.015..0.018 rows=1 loops=1)"
"                          Index Cond: (id = 1397038206::numeric)"
"                    ->  Seq Scan on zp_vyuziti_poz  (cost=0.00..1.28 rows=28 width=70) (actual time=0.004..0.022 rows=28 loops=1)"
"              ->  Seq Scan on d_pozemku  (cost=0.00..1.11 rows=11 width=19) (actual time=0.003..0.015 rows=11 loops=1)"
"        ->  Index Scan using tel_pk on telesa  (cost=0.00..8.28 rows=1 width=15) (actual time=0.009..0.017 rows=1 loops=1)"
"              Index Cond: (parcely.tel_id = public.telesa.id)"
"  ->  Merge Right Join  (cost=32884.48..33867.30 rows=76968 width=164) (actual time=1147.807..1315.173 rows=77117 loops=1)"
"        Merge Cond: (casti_obci.kod = budovy.caobce_kod)"
"        ->  Sort  (cost=30.49..30.99 rows=198 width=103) (actual time=1.468..1.614 rows=198 loops=1)"
"              Sort Key: casti_obci.kod"
"              Sort Method:  quicksort  Memory: 53kB"
"              ->  Merge Right Join  (cost=19.41..22.94 rows=198 width=103) (actual time=0.583..1.163 rows=198 loops=1)"
"                    Merge Cond: (obce.kod = casti_obci.obce_kod)"
"                    ->  Sort  (cost=6.88..7.16 rows=111 width=53) (actual time=0.186..0.259 rows=111 loops=1)"
"                          Sort Key: obce.kod"
"                          Sort Method:  quicksort  Memory: 40kB"
"                          ->  Seq Scan on obce  (cost=0.00..3.11 rows=111 width=53) (actual time=0.003..0.086 rows=111 loops=1)"
"                    ->  Sort  (cost=12.53..13.03 rows=198 width=58) (actual time=0.392..0.531 rows=198 loops=1)"
"                          Sort Key: casti_obci.obce_kod"
"                          Sort Method:  quicksort  Memory: 52kB"
"                          ->  Seq Scan on casti_obci  (cost=0.00..4.98 rows=198 width=58) (actual time=0.006..0.184 rows=198 loops=1)"
"        ->  Sort  (cost=32853.99..33046.41 rows=76968 width=69) (actual time=1146.323..1207.363 rows=77117 loops=1)"
"              Sort Key: budovy.caobce_kod"
"              Sort Method:  quicksort  Memory: 9097kB"
"              ->  Merge Right Join  (cost=17404.01..26607.28 rows=76968 width=69) (actual time=675.313..1058.389 rows=77117 loops=1)"
"                    Merge Cond: (public.telesa.id = budovy.tel_id)"
"                    ->  Index Scan using tel_pk on telesa  (cost=0.00..7819.74 rows=103617 width=15) (actual time=0.027..109.818 rows=103608 loops=1)"
"                    ->  Sort  (cost=17403.23..17595.65 rows=76968 width=76) (actual time=675.268..730.793 rows=77117 loops=1)"
"                          Sort Key: budovy.tel_id"
"                          Sort Method:  quicksort  Memory: 9097kB"
"                          ->  Merge Right Join  (cost=10001.97..11156.52 rows=76968 width=76) (actual time=307.027..473.833 rows=77117 loops=1)"
"                                Merge Cond: (t_budov.kod = budovy.typbud_kod)"
"                                ->  Sort  (cost=1.14..1.15 rows=6 width=17) (actual time=0.043..0.051 rows=6 loops=1)"
"                                      Sort Key: t_budov.kod"
"                                      Sort Method:  quicksort  Memory: 25kB"
"                                      ->  Seq Scan on t_budov  (cost=0.00..1.06 rows=6 width=17) (actual time=0.007..0.012 rows=6 loops=1)"
"                                ->  Sort  (cost=10000.83..10193.25 rows=76968 width=53) (actual time=306.967..358.000 rows=77117 loops=1)"
"                                      Sort Key: budovy.typbud_kod"
"                                      Sort Method:  quicksort  Memory: 9112kB"
"                                      ->  Merge Left Join  (cost=1.07..3754.12 rows=76968 width=53) (actual time=0.216..211.649 rows=77117 loops=1)"
"                                            Merge Cond: (budovy.id = casti_budov.bud_id)"
"                                            ->  Index Scan using bud_pk on budovy  (cost=0.00..3500.36 rows=76968 width=40) (actual time=0.130..82.669 rows=76968 loops=1)"
"                                            ->  Materialize  (cost=1.07..58.37 rows=238 width=28) (actual time=0.073..3.458 rows=238 loops=1)"
"                                                  ->  Nested Loop Left Join  (cost=1.07..55.99 rows=238 width=28) (actual time=0.068..3.113 rows=238 loops=1)"
"                                                        Join Filter: (t_bud_ii.kod = casti_budov.typbud_kod)"
"                                                        ->  Index Scan using i_casti_budov_budid on casti_budov  (cost=0.00..22.79 rows=238 width=25) (actual time=0.051..0.323 rows=238 loops=1)"
"                                                        ->  Materialize  (cost=1.07..1.13 rows=6 width=17) (actual time=0.001..0.004 rows=6 loops=238)"
"                                                              ->  Seq Scan on t_budov t_bud_ii  (cost=0.00..1.06 rows=6 width=17) (actual time=0.003..0.013 rows=6 loops=1)"
"Total runtime: 1372.410 ms"
což bych pokládal za dostatečné, nicméně jsem chtěl vyzkoušet i optimalizaci (nerad bych svůj nepořádek zametal pod koberec neustálým navyšováním výkonu) dotazu, jak radí kolega níže. V pohledu je použitej jinej pohled, což jak jsem pochopil z vyjádření dalšího pana kolegy by mohlo dělat paseku, takže jsem oba pohledy přepsal do jednoho. Tím jsem se dostal na půl sekundy bez ohledu na nastavení work_mem a hash_join..
"Nested Loop Left Join  (cost=0.00..42.69 rows=1 width=275) (actual time=0.302..0.334 rows=1 loops=1)"
"  Join Filter: (casti_obci.obce_kod = obce.kod)"
"  ->  Nested Loop Left Join  (cost=0.00..38.11 rows=1 width=230) (actual time=0.131..0.162 rows=1 loops=1)"
"        ->  Nested Loop Left Join  (cost=0.00..29.81 rows=1 width=237) (actual time=0.127..0.156 rows=1 loops=1)"
"              Join Filter: (t_bud_ii.kod = casti_budov.typbud_kod)"
"              ->  Nested Loop Left Join  (cost=0.00..28.68 rows=1 width=234) (actual time=0.113..0.141 rows=1 loops=1)"
"                    ->  Nested Loop Left Join  (cost=0.00..20.38 rows=1 width=230) (actual time=0.099..0.124 rows=1 loops=1)"
"                          ->  Nested Loop Left Join  (cost=0.00..20.04 rows=1 width=216) (actual time=0.096..0.119 rows=1 loops=1)"
"                                ->  Nested Loop Left Join  (cost=0.00..19.76 rows=1 width=213) (actual time=0.094..0.116 rows=1 loops=1)"
"                                      ->  Nested Loop Left Join  (cost=0.00..19.48 rows=1 width=163) (actual time=0.091..0.112 rows=1 loops=1)"
"                                            ->  Nested Loop Left Join  (cost=0.00..11.19 rows=1 width=134) (actual time=0.087..0.107 rows=1 loops=1)"
"                                                  Join Filter: (d_pozemku.kod = parcely.drupoz_kod)"
"                                                  ->  Nested Loop Left Join  (cost=0.00..9.94 rows=1 width=129) (actual time=0.080..0.082 rows=1 loops=1)"
"                                                        Join Filter: (zp_vyuziti_poz.kod = parcely.zpvypa_kod)"
"                                                        ->  Index Scan using par_pk on parcely  (cost=0.00..8.31 rows=1 width=73) (actual time=0.028..0.029 rows=1 loops=1)"
"                                                              Index Cond: (id = 1397038206::numeric)"
"                                                        ->  Seq Scan on zp_vyuziti_poz  (cost=0.00..1.28 rows=28 width=70) (actual time=0.004..0.021 rows=28 loops=1)"
"                                                  ->  Seq Scan on d_pozemku  (cost=0.00..1.11 rows=11 width=19) (actual time=0.003..0.010 rows=11 loops=1)"
"                                            ->  Index Scan using bud_pk on budovy  (cost=0.00..8.28 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=1)"
"                                                  Index Cond: (budovy.id = parcely.bud_id)"
"                                      ->  Index Scan using caob_pk on casti_obci  (cost=0.00..0.27 rows=1 width=58) (actual time=0.000..0.000 rows=0 loops=1)"
"                                            Index Cond: (casti_obci.kod = budovy.caobce_kod)"
"                                ->  Index Scan using tbud_pk on t_budov  (cost=0.00..0.27 rows=1 width=17) (actual time=0.000..0.000 rows=0 loops=1)"
"                                      Index Cond: (t_budov.kod = budovy.typbud_kod)"
"                          ->  Index Scan using i_casti_budov_budid on casti_budov  (cost=0.00..0.30 rows=3 width=25) (actual time=0.001..0.001 rows=0 loops=1)"
"                                Index Cond: (casti_budov.bud_id = budovy.id)"
"                    ->  Index Scan using tel_pk on telesa  (cost=0.00..8.28 rows=1 width=15) (actual time=0.011..0.013 rows=1 loops=1)"
"                          Index Cond: (parcely.tel_id = telesa.id)"
"              ->  Seq Scan on t_budov t_bud_ii  (cost=0.00..1.06 rows=6 width=17) (actual time=0.002..0.006 rows=6 loops=1)"
"        ->  Index Scan using tel_pk on telesa tel_bud  (cost=0.00..8.28 rows=1 width=15) (actual time=0.001..0.001 rows=0 loops=1)"
"              Index Cond: (budovy.tel_id = tel_bud.id)"
"  ->  Seq Scan on obce  (cost=0.00..3.11 rows=111 width=53) (actual time=0.002..0.070 rows=111 loops=1)"
"Total runtime: 0.527 ms"
Tudíž mám dvě dobrá řešení, která hodlám zkombinovat, změna nastavení jistě prospěje i dalším dotazům a píšu si za uši, že při volání pohledu v pohledu nefungujou indexy jak by měly. Všem děkuju za ochotu a dobré rady. Omlouvám se, že jsem nepřiložil i SQL pohledu, ale nejsou to moje data, pokud by ho někdo moc chtěl, tak ho nějak zobecním..
Je.
Nejsem blbý, jen se hloupě ptám
okbob avatar 28.4.2011 21:09 okbob | skóre: 30 | blog: systemakuv_blog | Benešov
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Tak v tom bude nějaký zádrhel :)

Případně víc zádrhelů

a) radil jsem vám set work_mem nebo set hashjoin off - jedno nebo druhé.

b) V PostgreSQL pohled by neměl mít vliv na výkon - můžete ještě jednou ukázat oba SELECTy, tj. s pohledem a bez pohledu - tipoval bych si, že nebudou ekvivalentní.
okbob avatar 28.4.2011 21:19 okbob | skóre: 30 | blog: systemakuv_blog | Benešov
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
možná budou ekvivalentní - je tam ještě jedna možnost - počínaje hodnotou from_collapse_limit, join_collapse_limit a geqo_threshold PostgreSQL skrečuje optimalizace (po dosažení určitého počtu JOINů) a nastupují heuristiky - u Vás bude asi default 8 - a v plánu je zachyceno určitě víc jak 8 JOINů. Možná, že dalším správným řešením je zvýšení těchto systémových proměnných třeba na 12. Tím pádem planner projde víc cest - sic bude o něco pomalejší, ale je nalezený plán bude optimální - oddálíte nástup heuristik.
29.4.2011 07:03 pht | skóre: 48 | blog: pht
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Nezapomeňte že jde o verzi 8.4, která navíc k tomuhle chaosu má ještě navíc dost chyb.
In Ada the typical infinite loop would normally be terminated by detonation.
okbob avatar 29.4.2011 08:08 okbob | skóre: 30 | blog: systemakuv_blog | Benešov
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
V 8.4 se překopával planner - přidával se SEMIJOIN a ANTIJOIN. A s tím problémy byly - nicméně snad všechny známé jsou opravené - většinou se to týkalo špatných odhadů u limitních případů s NULL nebo u subselectů - dotazů s IN nebo EXISTS pokud byla data extrémně nehomogenní. 8.3 měla jednodušší algoritmus pro výpočet, takže se třeba moc netrefila ale také o moc neustřelila. Samozřejmě, že je možnost, že se jedná o problém v optimalizaci - ale osobně bych to spíš tipoval na nízký collapse_limit.
29.4.2011 08:40 jeleniste | skóre: 13 | blog: Prokustovo lože
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Počet joinů zůstal stejnej, akorát jsem vzal jeden pohled a zkopčil ho do druhýho. Původně to bylo něco jako:
create viev v_parcely as
Select 
 ...
from 
parcely
left join typy on ..
left join telesa on ..
.
.
left join v_budovy on ..
-------------------------
create viev v_bud
as
select
......
from
budovy
left join casti_budov on ..
left join typy budov on ...
.
.
.
a já z toho udelal
create viev v_parcely as
Select 
 ...
from 
parcely
left join typy on ..
left join telesa on ..
.
.
left join budovy
left join casti_budov on ..
left join typy budov on ...
.
.
.
Nejsem blbý, jen se hloupě ptám
29.4.2011 08:29 jeleniste | skóre: 13 | blog: Prokustovo lože
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Podle mě pomohle ten hashjoin..
Nejsem blbý, jen se hloupě ptám
29.4.2011 08:34 jeleniste | skóre: 13 | blog: Prokustovo lože
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Já jsem to možná blbě napsal. Oba selekty jsou z pohledů, ale v jednom případě je v tom pohledu joinovanej další pohled a to byl podle mě kámen úrazu. Je.
Nejsem blbý, jen se hloupě ptám
okbob avatar 29.4.2011 09:11 okbob | skóre: 30 | blog: systemakuv_blog | Benešov
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
to je prave divne. Muzete jeste vyzkouset, prosim, zvysit JOIN_COLLAPSE_LIMIT cca na 12 a geqo_threshold na 12 a znovu vyzkouset ten puvodni dotaz s pohledem? + povoleny hashjoin a jeste k tomu presne verzi PostgreSQL tj. 8.4.x

a potom bych potreboval vysledek z EXPLAIN ANALYZE z obou variant, vcetne dotazu. Pokud to nechcete posilat sem, tak na moji adresu pavel.stehule@gmail.com.

Na ukladani provadecich planu je sikovny tahle utilitka http://explain.depesz.com/

Pavel

29.4.2011 15:36 jeleniste | skóre: 13 | blog: Prokustovo lože
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
O víkendu se na to podívám
Nejsem blbý, jen se hloupě ptám
30.4.2011 10:40 jeleniste | skóre: 13 | blog: Prokustovo lože
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
máte pravdu, navýšený colapse limit to urychlil na zlomek sekundy. Podrobnosti Vám pošlu mejlem. Firma pro kterou to dělám to považuje za svoje obchodní tajemství a mohli by se vztekat.
set enable_hashjoin to on;
set work_mem to '1MB';
set JOIN_COLLAPSE_LIMIT to 12;
set geqo_threshold to 12;
explain analyze select * from v_vypis_parcel_puvodni where par_id = 1396907206
"Nested Loop Left Join  (cost=13.90..4930.90 rows=1 width=415) (actual time=298.456..365.421 rows=1 loops=1)"
"  Join Filter: (katastr_uzemi.kod = parcely.katuze_kod)"
"  ->  Nested Loop Left Join  (cost=13.90..4923.96 rows=1 width=365) (actual time=298.408..365.142 rows=1 loops=1)"
"        ->  Nested Loop Left Join  (cost=13.90..4923.68 rows=1 width=320) (actual time=298.402..365.134 rows=1 loops=1)"
"              ->  Nested Loop Left Join  (cost=13.90..4923.40 rows=1 width=270) (actual time=298.396..365.127 rows=1 loops=1)"
"                    ->  Nested Loop Left Join  (cost=13.90..4923.02 rows=1 width=277) (actual time=298.364..365.091 rows=1 loops=1)"
"                          Join Filter: (d_pozemku.kod = parcely.drupoz_kod)"
"                          ->  Nested Loop Left Join  (cost=13.90..4921.77 rows=1 width=272) (actual time=298.341..365.063 rows=1 loops=1)"
"                                Join Filter: (zp_vyuziti_poz.kod = parcely.zpvypa_kod)"
"                                ->  Nested Loop Left Join  (cost=13.90..4920.14 rows=1 width=216) (actual time=298.291..365.011 rows=1 loops=1)"
"                                      ->  Nested Loop Left Join  (cost=13.90..4911.85 rows=1 width=212) (actual time=298.260..364.977 rows=1 loops=1)"
"                                            Join Filter: (parcely.zdpaze_kod = zdroje_parcel_ze.kod)"
"                                            ->  Nested Loop Left Join  (cost=13.90..4910.78 rows=1 width=149) (actual time=298.236..364.953 rows=1 loops=1)"
"                                                  Join Filter: (budovy.id = parcely.bud_id)"
"                                                  ->  Index Scan using par_pk on parcely  (cost=0.00..8.31 rows=1 width=84) (actual time=0.027..0.031 rows=1 loops=1)"
"                                                        Index Cond: (id = 1396907206::numeric)"
"                                                  ->  Hash Left Join  (cost=13.90..3940.37 rows=76968 width=76) (actual time=0.873..307.146 rows=77117 loops=1)"
"                                                        Hash Cond: (budovy.typbud_kod = t_budov.kod)"
"                                                        ->  Hash Left Join  (cost=12.76..2880.92 rows=76968 width=53) (actual time=0.852..183.112 rows=77117 loops=1)"
"                                                              Hash Cond: (budovy.id = casti_budov.bud_id)"
"                                                              ->  Seq Scan on budovy  (cost=0.00..1903.68 rows=76968 width=40) (actual time=0.033..53.484 rows=76968 loops=1)"
"                                                              ->  Hash  (cost=9.79..9.79 rows=238 width=28) (actual time=0.806..0.806 rows=238 loops=1)"
"                                                                    ->  Hash Left Join  (cost=1.14..9.79 rows=238 width=28) (actual time=0.036..0.612 rows=238 loops=1)"
"                                                                          Hash Cond: (casti_budov.typbud_kod = t_bud_ii.kod)"
"                                                                          ->  Seq Scan on casti_budov  (cost=0.00..5.38 rows=238 width=25) (actual time=0.002..0.159 rows=238 loops=1)"
"                                                                          ->  Hash  (cost=1.06..1.06 rows=6 width=17) (actual time=0.020..0.020 rows=6 loops=1)"
"                                                                                ->  Seq Scan on t_budov t_bud_ii  (cost=0.00..1.06 rows=6 width=17) (actual time=0.004..0.010 rows=6 loops=1)"
"                                                        ->  Hash  (cost=1.06..1.06 rows=6 width=17) (actual time=0.013..0.013 rows=6 loops=1)"
"                                                              ->  Seq Scan on t_budov  (cost=0.00..1.06 rows=6 width=17) (actual time=0.001..0.005 rows=6 loops=1)"
"                                            ->  Seq Scan on zdroje_parcel_ze  (cost=0.00..1.03 rows=3 width=70) (actual time=0.002..0.004 rows=3 loops=1)"
"                                      ->  Index Scan using tel_pk on telesa  (cost=0.00..8.28 rows=1 width=15) (actual time=0.021..0.022 rows=1 loops=1)"
"                                            Index Cond: (parcely.tel_id = public.telesa.id)"
"                                ->  Seq Scan on zp_vyuziti_poz  (cost=0.00..1.28 rows=28 width=70) (actual time=0.003..0.020 rows=28 loops=1)"
"                          ->  Seq Scan on d_pozemku  (cost=0.00..1.11 rows=11 width=19) (actual time=0.002..0.007 rows=11 loops=1)"
"                    ->  Index Scan using tel_pk on telesa  (cost=0.00..0.37 rows=1 width=15) (actual time=0.026..0.028 rows=1 loops=1)"
"                          Index Cond: (budovy.tel_id = public.telesa.id)"
"              ->  Index Scan using caob_pk on casti_obci  (cost=0.00..0.27 rows=1 width=58) (actual time=0.002..0.002 rows=0 loops=1)"
"                    Index Cond: (casti_obci.kod = budovy.caobce_kod)"
"        ->  Index Scan using ob_pk on obce  (cost=0.00..0.27 rows=1 width=53) (actual time=0.002..0.002 rows=0 loops=1)"
"              Index Cond: (casti_obci.obce_kod = obce.kod)"
"  ->  Seq Scan on katastr_uzemi  (cost=0.00..4.72 rows=172 width=54) (actual time=0.003..0.104 rows=172 loops=1)"
"Total runtime: 365.709 ms"
Nejsem blbý, jen se hloupě ptám
okbob avatar 30.4.2011 17:27 okbob | skóre: 30 | blog: systemakuv_blog | Benešov
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Hodnota 8 je pro dnešní PC zbytečně nízká - heuristiky sice nějaké řešení najdou - ale někdy to má k optimu daleko. Pohledy jsou v tomhle docela zrádný - člověk napíše jednoduchý SELECT ale ve skutečnosti z toho vyleze hrozná mrcha. Jinak, v PostgreSQL nemá pohled žádnou režii a pokud nepřesáhnete počet tabulek v dotazu, kdy se zapíná heuristika, tak by se jeho použití nemělo projevit na výkonu - ani pozitivně nebo negativně.

Tak už alespoň víte, jak se optimalizuje :)
26.4.2011 21:17 pht | skóre: 48 | blog: pht
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Největší nárůst času jsou asi tyto dva seq. scany pro join.
"                          Hash Cond: (bud.tel_id = public.telesa.id)"
"                          ->  Seq Scan on budovy bud  (cost=0.00..1903.68 rows=76968 width=40) (actual time=0.018..85.580 rows=76968 loops=1)"
"                          ->  Hash  (cost=2214.17..2214.17 rows=103617 width=15) (actual time=26262.120..26262.120 rows=103617 loops=1)"
"                                ->  Seq Scan on telesa  (cost=0.00..2214.17 rows=103617 width=15) (actual time=0.017..98.825 rows=103617 loops=1)"
Vypadá to ale že ten scan je tam na místě protože celou cestu až do toho nejvnějšího joinu putuje cca 70K řádků což je celý obsah tabulky budovy (plus přijoinované sloupce). Možná zkuste přeformulovat dotaz tak, aby se z budovy nejprve vybralo méně řádků?
In Ada the typical infinite loop would normally be terminated by detonation.
27.4.2011 07:38 jeleniste | skóre: 13 | blog: Prokustovo lože
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
To nepude, já ty budovy potřebuju všechny, je to výpis parcel a každá budova leží na nějaký parcele.. Nebo jsem to možná blbě pochopil..
Nejsem blbý, jen se hloupě ptám
27.4.2011 16:29 pht | skóre: 48 | blog: pht
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Z toho explainu plyne že na nejvyšší úrovni (ten největší join) je mezi něčím co má 1 řádek a něčím co má 77k řádků (a výsledek celého dotazu je 1 řádek).
"Nested Loop Left Join  (cost=4039.55..12009.39 rows=1 width=261) (actual time=59041.378..59041.415 rows=1 loops=1)"
"  Join Filter: (bud.id = parcely.bud_id)"
"  ->  Nested Loop Left Join  (cost=0.00..19.49 rows=1 width=138) (actual time=4.920..4.954 rows=1 loops=1)"
   [...]
"  ->  Hash Left Join  (cost=4039.55..11027.73 rows=76968 width=134) (actual time=26464.128..58984.575 rows=77117 loops=1)"
A těch 77k se v druhé větvi táhne poměrně dlouho přes několik dalších joinů. Tak je prostě otázka jestli nechcete dotaz přeformulovat tak, aby se těch 77k řádků omezilo již v nějakém pod-dotazu dříve a neputovaly celou cestu až k tomu největšímu joinu.

Když sem dáte formulaci toho dotazu který odpovídá přesně tomu explainu tak to můžeme zkusit nějak vymyslet.
In Ada the typical infinite loop would normally be terminated by detonation.
28.4.2011 08:39 jeleniste | skóre: 13 | blog: Prokustovo lože
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
No, ten dotaz je select * from pohled where id = neco, proto ten jeden radek. Ten pohled joinuje parcely s budovama, ktery sou ve zvlastnim pohledu, proto tech 77k radku. Kdybych ty budovy teda vyndal z toho pohledu v pohledu, mohlo by to pomoct???
Dik Je.
Nejsem blbý, jen se hloupě ptám
28.4.2011 20:16 pht | skóre: 48 | blog: pht
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Kdybych ty budovy teda vyndal z toho pohledu v pohledu, mohlo by to pomoct???
Zkuste.
In Ada the typical infinite loop would normally be terminated by detonation.
28.4.2011 20:46 jeleniste | skóre: 13 | blog: Prokustovo lože
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Hm, tak ten vnořenej pohled taky dělal pěknou paseku, přepsal sem to a je to řádově rychlejší.. Díky za radu. Přidávám explain přepsanýho dotazu, kdyby někdo řešil v budoucnu podobnej prblm. Všem Díky.
"Nested Loop Left Join  (cost=0.00..42.69 rows=1 width=275) (actual time=0.308..0.340 rows=1 loops=1)"
"  Join Filter: (casti_obci.obce_kod = obce.kod)"
"  ->  Nested Loop Left Join  (cost=0.00..38.11 rows=1 width=230) (actual time=0.124..0.155 rows=1 loops=1)"
"        ->  Nested Loop Left Join  (cost=0.00..29.81 rows=1 width=237) (actual time=0.121..0.150 rows=1 loops=1)"
"              Join Filter: (t_bud_ii.kod = casti_budov.typbud_kod)"
"              ->  Nested Loop Left Join  (cost=0.00..28.68 rows=1 width=234) (actual time=0.107..0.136 rows=1 loops=1)"
"                    ->  Nested Loop Left Join  (cost=0.00..20.38 rows=1 width=230) (actual time=0.095..0.120 rows=1 loops=1)"
"                          ->  Nested Loop Left Join  (cost=0.00..20.04 rows=1 width=216) (actual time=0.091..0.114 rows=1 loops=1)"
"                                ->  Nested Loop Left Join  (cost=0.00..19.76 rows=1 width=213) (actual time=0.089..0.111 rows=1 loops=1)"
"                                      ->  Nested Loop Left Join  (cost=0.00..19.48 rows=1 width=163) (actual time=0.086..0.107 rows=1 loops=1)"
"                                            ->  Nested Loop Left Join  (cost=0.00..11.19 rows=1 width=134) (actual time=0.082..0.102 rows=1 loops=1)"
"                                                  Join Filter: (d_pozemku.kod = parcely.drupoz_kod)"
"                                                  ->  Nested Loop Left Join  (cost=0.00..9.94 rows=1 width=129) (actual time=0.075..0.077 rows=1 loops=1)"
"                                                        Join Filter: (zp_vyuziti_poz.kod = parcely.zpvypa_kod)"
"                                                        ->  Index Scan using par_pk on parcely  (cost=0.00..8.31 rows=1 width=73) (actual time=0.025..0.025 rows=1 loops=1)"
"                                                              Index Cond: (id = 1397038206::numeric)"
"                                                        ->  Seq Scan on zp_vyuziti_poz  (cost=0.00..1.28 rows=28 width=70) (actual time=0.004..0.022 rows=28 loops=1)"
"                                                  ->  Seq Scan on d_pozemku  (cost=0.00..1.11 rows=11 width=19) (actual time=0.002..0.008 rows=11 loops=1)"
"                                            ->  Index Scan using bud_pk on budovy  (cost=0.00..8.28 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=1)"
"                                                  Index Cond: (budovy.id = parcely.bud_id)"
"                                      ->  Index Scan using caob_pk on casti_obci  (cost=0.00..0.27 rows=1 width=58) (actual time=0.000..0.000 rows=0 loops=1)"
"                                            Index Cond: (casti_obci.kod = budovy.caobce_kod)"
"                                ->  Index Scan using tbud_pk on t_budov  (cost=0.00..0.27 rows=1 width=17) (actual time=0.001..0.001 rows=0 loops=1)"
"                                      Index Cond: (t_budov.kod = budovy.typbud_kod)"
"                          ->  Index Scan using i_casti_budov_budid on casti_budov  (cost=0.00..0.30 rows=3 width=25) (actual time=0.001..0.001 rows=0 loops=1)"
"                                Index Cond: (casti_budov.bud_id = budovy.id)"
"                    ->  Index Scan using tel_pk on telesa  (cost=0.00..8.28 rows=1 width=15) (actual time=0.010..0.012 rows=1 loops=1)"
"                          Index Cond: (parcely.tel_id = telesa.id)"
"              ->  Seq Scan on t_budov t_bud_ii  (cost=0.00..1.06 rows=6 width=17) (actual time=0.002..0.005 rows=6 loops=1)"
"        ->  Index Scan using tel_pk on telesa tel_bud  (cost=0.00..8.28 rows=1 width=15) (actual time=0.001..0.001 rows=0 loops=1)"
"              Index Cond: (budovy.tel_id = tel_bud.id)"
"  ->  Seq Scan on obce  (cost=0.00..3.11 rows=111 width=53) (actual time=0.002..0.071 rows=111 loops=1)"
"Total runtime: 0.559 ms"

Nejsem blbý, jen se hloupě ptám
26.4.2011 10:58 l0gik | skóre: 22
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
A nebylo by lepší, kdyby každá budova měla alespoň jednu část. Tím by ses zbavil všech těch coalesce a leftjoinů a navíc by to imho více odpovídalo realitě...
27.4.2011 14:18 l0gik | skóre: 22
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4

Nebo pokud nechceš hejbat se strukturou databáze, tak mě napadlo todle. Nevím, jestli to bude rychlejší, ale za pokus nic nedáš....

WITH vsebudovy(id, cislo_domovni) (
     SELECT bud_id, cislo_domovni, kod, 1  FROM casti_budov 
     UNION
     SELECT id, cislo_domovni, kod, 2 FROM budovy WHERE id NOT in (select bud_id FROM casti_budov)
)
SELECT 
   vsebudovy.*, t_budov.typ 
FROM 
   vsebudovy INNER/LEFT JOIN typ_budov ON (vsebudovy.kod = typ%budov.kod) 

Ale imho nejlepší by opravdu mít budovy a části budov v jedný tabulce: v podstatě to, co počítám jako vsebudovy. Nemusíš kvůli tomu překopávat db, stačí si udělat materializovanej view a dotaz tím urychlíš hodně. Pomocí rules to je pár řádek SQL a vyřešíš si tim problém a navíc zpřehledníš kód...

 

27.4.2011 14:19 l0gik | skóre: 22
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
PS: dole je inner/left join, protože jsem nevěděl, jestli mají všechny budovy typ nutně, nebo ne....
27.4.2011 14:48 jeleniste | skóre: 13 | blog: Prokustovo lože
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
V postgre jde použít CTE v pohledu??
Nejsem blbý, jen se hloupě ptám
okbob avatar 27.4.2011 15:05 okbob | skóre: 30 | blog: systemakuv_blog | Benešov
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
V PostgreSQL pohledy nejsou limitovane - muze tam byt libovolny SELECT nebo CTE
27.4.2011 16:11 l0gik | skóre: 22
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Ale view je v postgresql čistě jen stabilní "shortcut" za dotaz. Pokud chceš, aby měl optimalizační efekt, musíš ho udělat materializovanej, což v případě postgresu znamená udělat to ručně. Tzn. udělat místo create view tabulku:

CREATE TABLE asdasd AS SELECT blablbabla

a doplnit triggery nebo rules zajišťující update tý tabulky.
28.4.2011 08:41 jeleniste | skóre: 13 | blog: Prokustovo lože
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
No tomu bych se rád vyhnul, mam obavu, že bych v tom nebyl schopnej pak udržet pořádek. Ale popřemejšlím o tom. Dík Je.
Nejsem blbý, jen se hloupě ptám
28.4.2011 11:26 l0gik | skóre: 22
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Udržet pořádek? V čem je problém. Prostě k tabulkám budovy a části budov doplníš následující rules:

CREATE RULE budovy insert AS ON INSERT TO budovy DO ALSO 
INSERT TO nazev_pohledu (cast_budovy, id, kod, ...) 
VALUES (false, new.id, new.kod, ...)

on update to budovy update view (když tam záznam nebude, neoupdatuje se nic, dotaz už vymyslíš sám...)

on delete to budovy delete view (část budovy nemůže existovat bez budovy)

on insert to casti insert to view, jestli je tam cela budova vymaz

CREATE RULE budovy insert AS ON INSERT TO budovy DO ALSO 
(
INSERT TO nazev_pohledu (cast_budovy, id, kod, ...)  VALUES (true, new.id, new.kod, ...);
DELETE FROM nazev_pohledu WHERE id = new.budova_id AND cast_budovy = false;
)
on update to casti update view

on delete to casti delete view

a pořádek bude udržovat sama postgresql....
28.4.2011 11:30 l0gik | skóre: 22
Rozbalit Rozbalit vše Re: optimalizace sloziteho joinu, pouziti coalesce v klauzuli on vs indexy, Postgresql 8.4
Ještě mě napadlo - celý by se to ještě zjednodušilo, kdybys bral IDčka pro budovy i části budov z jedný sequence, pak bys sis ani nepotřeboval pamatovat, jestli je to budova nebo cast budovy (i když ta informace může bejt stejně užitečná) a díky tomu máš jednoduchej primární klíč. Ale to má smysl jen pokud tabulku nově plníš, přepisovat kvůli tomu IDčka asi moc smysl nemá. I když pokud máš dobře definovanou referenční integritu (nebo se na jednu tabulku nikdo neodkazuje) tak by ani to nebyl problém - přičíst ke všem záznamům z jedný tabulky max(id) z druhý tabulky a patřičně nastavit společnou sequenci.

Založit nové vláknoNahoru

Tiskni Sdílej: Linkuj Jaggni to Vybrali.sme.sk Google Del.icio.us Facebook

ISSN 1214-1267   www.czech-server.cz
© 1999-2015 Nitemedia s. r. o. Všechna práva vyhrazena.