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

[jeleniste]

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

---

---

Odpovědi

[ghost]

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.

[jeleniste]

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.

[VM]

Byl by vypis toho EXPLAINu, a popis indexu nad temi tabulkami?

[jeleniste]

Dodám

[pht]

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".

[jeleniste]

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"

[okbob]

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?

[jeleniste]

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..

[okbob]

Zkoušel jste FULL VACUUM ?

[jeleniste]

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

[okbob]

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

[jeleniste]

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.

[jeleniste]

Skvělý, díky moc, tohle pomohlo.

[okbob]

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 :)

[jeleniste]

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.

[okbob]

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]

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.

[pht]

Nezapomeňte že jde o verzi 8.4, která navíc k tomuhle chaosu má ještě navíc dost chyb.

[okbob]

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.

[jeleniste]

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 ...
.
.
.

[jeleniste]

Podle mě pomohle ten hashjoin..

[jeleniste]

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.

[okbob]

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

[jeleniste]

O víkendu se na to podívám

[jeleniste]

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"

[okbob]

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 :)

[pht]

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ů?

[jeleniste]

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..

[pht]

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.

[jeleniste]

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.

[pht]

Kdybych ty budovy teda vyndal z toho pohledu v pohledu, mohlo by to pomoct???

Zkuste.

[jeleniste]

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"

[l0gik]

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ě...

[l0gik]

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...

 

[l0gik]

PS: dole je inner/left join, protože jsem nevěděl, jestli mají všechny budovy typ nutně, nebo ne....

[jeleniste]

V postgre jde použít CTE v pohledu??

[okbob]

V PostgreSQL pohledy nejsou limitovane - muze tam byt libovolny SELECT nebo CTE

[l0gik]

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.

[jeleniste]

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.

[l0gik]

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....

[l0gik]

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.