Re: [PERFORM] Optimize complex join to use where condition before
Greg Stark wrote: Sebastian Hennebrueder [EMAIL PROTECTED] writes: User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) ... Nested Loop (cost=1349.13..1435.29 rows=1 width=2541) (actual time=1640.000..3687.000 rows=62 loops=1) Join Filter: (inner.fid = outer.faufgaben_id) - Index Scan using idx_tauf_mit_mitid on taufgaben_mitarbeiter am (cost=0.00..80.65 rows=35 width=4) (actual time=0.000..0.000 rows=765 loops=1) Is it really Mozilla Thunderbird that's causing this new craptastic mangling of plans in people's mails? I was assuming it was some new idea of how to mess up people's mail coming out of Exchange or Lotus or some other such corporate messaging software that only handled SMTP mail as an afterthought. This is, uh, disappointing. Are you talking about the quotes, or just the fact that it is wrapped? I don't know where the quotes came from, but in Thunderbird if you are writing in text mode (not html) it defaults to wrapping the text at something like 78 characters. That includes copy/paste text. If you want it to *not* wrap, it turns out that Paste as quotation will not wrap, but then you have to remove the from the beginning of every line. In html mode, it also defaults to wrapping, but if you switch to PREFORMAT text first, it doesn't wrap. At least, those are the tricks that I've found. Safest bet is to just use an attachment, though. John =:- signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Optimize complex join to use where condition before
I found a solution to improve my query. I do not know why but the statistics for all column has been 0. I changed this to 10 for index columns and to 20 for all foreign key columns. and to 100 for foreign key columns. I set the random page cost to 2 and now the query runs as expected. Many thanks to all of the posts in my and in other threads which helped a lot. Sebastian Merge Join (cost=1325.06..1329.96 rows=6 width=2558) (actual time=344.000..344.000 rows=6 loops=1) Merge Cond: (outer.fid = inner.faufgaben_id) - Sort (cost=1269.57..1271.91 rows=934 width=2541) (actual time=344.000..344.000 rows=773 loops=1) Sort Key: taufgaben.fid - Merge Join (cost=1205.09..1223.49 rows=934 width=2541) (actual time=219.000..313.000 rows=936 loops=1) Merge Cond: (outer.fid = inner.fprojekt_id) - Sort (cost=302.08..304.27 rows=876 width=1494) (actual time=156.000..156.000 rows=876 loops=1) Sort Key: tprojekte.fid - Merge Join (cost=237.42..259.27 rows=876 width=1494) (actual time=109.000..141.000 rows=876 loops=1) Merge Cond: (outer.fid = inner.fprojektleiter_id) - Index Scan using pk_tuser on tuser (cost=0.00..9.13 rows=109 width=883) (actual time=0.000..0.000 rows=101 loops=1) - Sort (cost=237.42..239.61 rows=876 width=619) (actual time=109.000..109.000 rows=876 loops=1) Sort Key: tprojekte.fprojektleiter_id - Merge Join (cost=181.17..194.60 rows=876 width=619) (actual time=63.000..94.000 rows=876 loops=1) Merge Cond: (outer.fid = inner.fkunden_kst_id) - Sort (cost=9.51..9.66 rows=58 width=119) (actual time=0.000..0.000 rows=58 loops=1) Sort Key: tkunden_kst.fid - Merge Join (cost=6.74..7.81 rows=58 width=119) (actual time=0.000..0.000 rows=58 loops=1) Merge Cond: (outer.fid = inner.fkunden_id) - Sort (cost=3.46..3.56 rows=40 width=51) (actual time=0.000..0.000 rows=40 loops=1) Sort Key: tkunden.fid - Seq Scan on tkunden (cost=0.00..2.40 rows=40 width=51) (actual time=0.000..0.000 rows=40 loops=1) - Sort (cost=3.28..3.42 rows=58 width=80) (actual time=0.000..0.000 rows=58 loops=1) Sort Key: tkunden_kst.fkunden_id - Seq Scan on tkunden_kst (cost=0.00..1.58 rows=58 width=80) (actual time=0.000..0.000 rows=58 loops=1) - Sort (cost=171.66..173.85 rows=876 width=508) (actual time=63.000..63.000 rows=876 loops=1) Sort Key: tprojekte.fkunden_kst_id - Merge Join (cost=114.91..128.85 rows=876 width=508) (actual time=31.000..47.000 rows=876 loops=1) Merge Cond: (outer.fid = inner.fkostentraeger_id) - Sort (cost=19.20..19.60 rows=158 width=162) (actual time=0.000..0.000 rows=158 loops=1) Sort Key: tkostentraeger.fid - Merge Join (cost=3.49..13.43 rows=158 width=162) (actual time=0.000..0.000 rows=158 loops=1) Merge Cond: (outer.fkostenstellen_id = inner.fid) - Index Scan using idx_kostenstellen_id on tkostentraeger (cost=0.00..7.18 rows=158 width=55) (actual time=0.000..0.000 rows=158 loops=1) - Sort (cost=3.49..3.53 rows=19 width=119) (actual time=0.000..0.000 rows=158 loops=1) Sort Key: tkostenstellen.fid - Merge Join (cost=2.76..3.08 rows=19 width=119) (actual time=0.000..0.000 rows=19 loops=1) Merge Cond: (outer.fid = inner.fabteilungen_id) - Sort (cost=1.17..1.19 rows=7 width=76) (actual time=0.000..0.000 rows=7 loops=1) Sort Key: tabteilungen.fid - Seq Scan on tabteilungen (cost=0.00..1.07 rows=7 width=76) (actual time=0.000..0.000 rows=7 loops=1) - Sort (cost=1.59..1.64 rows=19 width=55) (actual time=0.000..0.000 rows=19 loops=1) Sort Key: tkostenstellen.fabteilungen_id - Seq Scan on tkostenstellen (cost=0.00..1.19 rows=19 width=55) (actual time=0.000..0.000 rows=19 loops=1) - Sort (cost=95.71..97.90 rows=878 width=354) (actual time=31.000..31.000 rows=877 loops=1) Sort Key: tprojekte.fkostentraeger_id - Seq Scan on tprojekte (cost=0.00..52.78 rows=878 width=354) (actual time=0.000..31.000 rows=878 loops=1) - Sort (cost=903.01..905.35 rows=936 width=1047) (actual time=63.000..63.000 rows=936 loops=1) Sort Key: taufgaben.fprojekt_id - Nested Loop Left Join (cost=0.28..856.82 rows=936 width=1047) (actual time=0.000..63.000 rows=936 loops=1) Join Filter: (outer.fid = inner.faufgaben_id) - Index Scan using idx_taufgaben_bstatus on taufgaben (cost=0.00..835.47 rows=936 width=1043) (actual time=0.000..0.000 rows=936 loops=1) Index Cond: (fbearbeitungsstatus 2) - Materialize (cost=0.28..0.29 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=936) - Subquery Scan patchdaten (cost=0.00..0.28 rows=1
Re: [PERFORM] Optimize complex join to use where condition before
Sebastian Hennebrueder wrote: I found a solution to improve my query. I do not know why but the statistics for all column has been 0. I changed this to 10 for index columns and to 20 for all foreign key columns. and to 100 for foreign key columns. I set the random page cost to 2 and now the query runs as expected. Many thanks to all of the posts in my and in other threads which helped a lot. Sebastian I think 0 = use default. But still, changing to 20 and 100 probably fixes your problems. John =:- signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Optimize complex join to use where condition before
Solution to my problem. I added indexes to each foreign_key (there had been some missing). I will try tomorrow by daylight what influence this had actually. Only the indexes did not change anything! Even with lower random_page_costs and higher shared mem. The big change was the following I created a view which holds a part of the query. The part is the nested join I am doing from rpojekt, tkunden_kst, See below Than I changed my query to include the view which improved the performance from 3000 to 450 ms which is quite good now. But I am having two more question a) ### I estimated the theoretical speed a little bit higher. The query without joining the view takes about 220 ms. A query to the view with a condition projekt_id in ( x,y,z), beeing x,y,z all the projekt I got with the first query, takes 32 ms. So my calculation is query a 220 query b to view with project in ... 32 = 252 ms + some time to add the adequate row from query b to one of the 62 rows from query a This sometime seems to be quite high with 200 ms or alternative query a 220 ms for each of the 62 rows a query to the view with project_id = x 220 62*2 ms = 344 ms + some time to assemble all this. = 100 ms for assembling. This is quite a lot or am I wrong b) ### My query does take about 200 ms. Most of the time is taken by the following part LEFT JOIN ( SELECT DISTINCT taufgaben_patches.faufgaben_id FROM taufgaben_patches ORDER BY taufgaben_patches.faufgaben_id ) patchdaten ON taufgaben.fid = patchdaten.faufgaben_id What I want to achieve is one column in my query beeing null or not null and indicating if there is a patch which includes the aufgabe (engl.: task) Is there a better way? -- Kind Regards / Viele Grüße Sebastian Hennebrueder - http://www.laliluna.de/tutorials.html Tutorials for Java, Struts, JavaServer Faces, JSP, Hibernate, EJB and more. ## Below you can find query solution I found explain analyze of the complete query (my solution) explain analyze of query a explain analyze of view with one project_id as condition explain analyze SELECT taufgaben.fid AS taufgaben_fid, taufgaben.fprojekt_id AStaufgaben_fprojekt_id, taufgaben.fnummer AS taufgaben_fnummer, taufgaben.fbudget AS taufgaben_fbudget, taufgaben.ftyp AS taufgaben_ftyp, taufgaben.fberechnungsart AS taufgaben_fberechnungsart, taufgaben.fverrechnung_extern AS taufgaben_fverrechnung_extern, taufgaben.fverrechnungsbasis AS taufgaben_fverrechnungsbasis, taufgaben.fstatus AS taufgaben_fstatus, taufgaben.fkurzbeschreibung AS taufgaben_fkurzbeschreibung, taufgaben.fansprechpartner AS taufgaben_fansprechpartner, taufgaben.fanforderer AS taufgaben_fanforderer, taufgaben.fstandort_id AS taufgaben_fstandort_id, taufgaben.fwunschtermin AS taufgaben_fwunschtermin, taufgaben.fstarttermin AS taufgaben_fstarttermin, taufgaben.fgesamtaufwand AS taufgaben_fgesamtaufwand, taufgaben.fistaufwand AS taufgaben_fistaufwand, taufgaben.fprio AS taufgaben_fprio, taufgaben.ftester AS taufgaben_ftester, taufgaben.ffaellig AS taufgaben_ffaellig, taufgaben.flevel AS taufgaben_flevel, taufgaben.fkategorie AS taufgaben_fkategorie, taufgaben.feintragbearbeitung AS taufgaben_feintragbearbeitung, taufgaben.fbearbeitungsstatus AS taufgaben_fbearbeitungsstatus, taufgaben.fsolllimit AS taufgaben_fsolllimit, taufgaben.fistlimit AS taufgaben_fistlimit, taufgaben.fpauschalbetrag AS taufgaben_fpauschalbetrag, taufgaben.frechnungslaeufe_id AS taufgaben_frechnungslaeufe_id, taufgaben.fzuberechnen AS taufgaben_fzuberechnen, taufgaben.floesungsbeschreibung AS taufgaben_floesungsbeschreibung, taufgaben.ffehlerbeschreibung AS taufgaben_ffehlerbeschreibung, taufgaben.faufgabenstellung AS taufgaben_faufgabenstellung, taufgaben.fkritischeaenderungen AS taufgaben_fkritischeaenderungen, taufgaben.fbdeaufgabenersteller_id AS taufgaben_fbdeaufgabenersteller_id, taufgaben.fzufaktorieren AS taufgaben_fzufaktorieren, taufgaben.fisdirty AS taufgaben_fisdirty, taufgaben.fnf_kunde_stunden AS taufgaben_fnf_kunde_stunden, taufgaben.fzf_kunde_stunden AS taufgaben_fzf_kunde_stunden, taufgaben.fbf_kunde_stunden AS taufgaben_fbf_kunde_stunden, taufgaben.fnf_kunde_betrag AS taufgaben_fnf_kunde_betrag, taufgaben.fzf_kunde_betrag AS taufgaben_fzf_kunde_betrag, taufgaben.fbf_kunde_betrag AS taufgaben_fbf_kunde_betrag, taufgaben.fgesamt_brutto_stunden AS taufgaben_fgesamt_brutto_stunden, taufgaben.fgesamt_brutto_betrag AS taufgaben_fgesamt_brutto_betrag, taufgaben.fhinweisgesendet AS taufgaben_fhinweisgesendet, taufgaben.fwarnunggesendet AS taufgaben_fwarnunggesendet, taufgaben.fnfgesamtaufwand AS taufgaben_fnfgesamtaufwand, taufgaben.fnf_netto_stunden AS taufgaben_fnf_netto_stunden, taufgaben.fnf_brutto_stunden AS taufgaben_fnf_brutto_stunden, taufgaben.fnfhinweisgesendet AS taufgaben_fnfhinweisgesendet, taufgaben.fnfwarnunggesendet AS
Re: [PERFORM] Optimize complex join to use where condition before
Solution not found as I thought. I integrated the query in a view and the query plan became very bad once again. The reason is that when I am using the view I have the joins in a differerent order. Does anyone have an idea to solve this. Sebastian a) bad order but the one I have in my application explain analyze SELECT taufgaben.fid AS taufgaben_fid, taufgaben.fprojekt_id AS taufgaben_fprojekt_id, taufgaben.fnummer AS taufgaben_fnummer, taufgaben.fbudget AS taufgaben_fbudget, taufgaben.ftyp AS taufgaben_ftyp, taufgaben.fberechnungsart AS taufgaben_fberechnungsart, taufgaben.fverrechnung_extern AS taufgaben_fverrechnung_extern, taufgaben.fverrechnungsbasis AS taufgaben_fverrechnungsbasis, taufgaben.fstatus AS taufgaben_fstatus, taufgaben.fkurzbeschreibung AS taufgaben_fkurzbeschreibung, taufgaben.fansprechpartner AS taufgaben_fansprechpartner, taufgaben.fanforderer AS taufgaben_fanforderer, taufgaben.fstandort_id AS taufgaben_fstandort_id, taufgaben.fwunschtermin AS taufgaben_fwunschtermin, taufgaben.fstarttermin AS taufgaben_fstarttermin, taufgaben.fgesamtaufwand AS taufgaben_fgesamtaufwand, taufgaben.fistaufwand AS taufgaben_fistaufwand, taufgaben.fprio AS taufgaben_fprio, taufgaben.ftester AS taufgaben_ftester, taufgaben.ffaellig AS taufgaben_ffaellig, taufgaben.flevel AS taufgaben_flevel, taufgaben.fkategorie AS taufgaben_fkategorie, taufgaben.feintragbearbeitung AS taufgaben_feintragbearbeitung, taufgaben.fbearbeitungsstatus AS taufgaben_fbearbeitungsstatus, taufgaben.fsolllimit AS taufgaben_fsolllimit, taufgaben.fistlimit AStaufgaben_fistlimit, taufgaben.fpauschalbetrag AS taufgaben_fpauschalbetrag, taufgaben.frechnungslaeufe_id AS taufgaben_frechnungslaeufe_id, taufgaben.fzuberechnen AS taufgaben_fzuberechnen, taufgaben.floesungsbeschreibung AS taufgaben_floesungsbeschreibung, taufgaben.ffehlerbeschreibung AStaufgaben_ffehlerbeschreibung, taufgaben.faufgabenstellung AS taufgaben_faufgabenstellung, taufgaben.fkritischeaenderungen AStaufgaben_fkritischeaenderungen, taufgaben.fbdeaufgabenersteller_id AS taufgaben_fbdeaufgabenersteller_id, taufgaben.fzufaktorieren AStaufgaben_fzufaktorieren, taufgaben.fisdirty AS taufgaben_fisdirty, taufgaben.fnf_kunde_stunden AS taufgaben_fnf_kunde_stunden, taufgaben.fzf_kunde_stunden AS taufgaben_fzf_kunde_stunden, taufgaben.fbf_kunde_stunden AS taufgaben_fbf_kunde_stunden, taufgaben.fnf_kunde_betrag AS taufgaben_fnf_kunde_betrag, taufgaben.fzf_kunde_betrag AS taufgaben_fzf_kunde_betrag, taufgaben.fbf_kunde_betrag AS taufgaben_fbf_kunde_betrag, taufgaben.fgesamt_brutto_stunden AS taufgaben_fgesamt_brutto_stunden, taufgaben.fgesamt_brutto_betrag AS taufgaben_fgesamt_brutto_betrag, taufgaben.fhinweisgesendet AS taufgaben_fhinweisgesendet, taufgaben.fwarnunggesendet AS taufgaben_fwarnunggesendet, taufgaben.fnfgesamtaufwand AS taufgaben_fnfgesamtaufwand, taufgaben.fnf_netto_stunden AStaufgaben_fnf_netto_stunden, taufgaben.fnf_brutto_stunden AS taufgaben_fnf_brutto_stunden, taufgaben.fnfhinweisgesendet AS taufgaben_fnfhinweisgesendet, taufgaben.fnfwarnunggesendet AStaufgaben_fnfwarnunggesendet, taufgaben.fhatzeiten AS taufgaben_fhatzeiten, taufgaben.fnichtpublicrechnungsfaehig AS taufgaben_fnichtpublicrechnungsfaehig, taufgaben.fnichtpublicrechnungsfaehigbetrag AS taufgaben_fnichtpublicrechnungsfaehigbetrag, taufgaben.fnichtberechenbar AStaufgaben_fnichtberechenbar, taufgaben.fnichtberechenbarbetrag AS taufgaben_fnichtberechenbarbetrag, taufgaben.finternertester AS taufgaben_finternertester, taufgaben.finterngetestet AS taufgaben_finterngetestet, taufgaben.fanzahlbearbeiter AS taufgaben_fanzahlbearbeiter, patchdaten.faufgaben_id AS pataid ,vprojekt.* FROM taufgaben LEFT JOIN ( SELECT DISTINCT taufgaben_patches.faufgaben_id FROM taufgaben_patches ) patchdaten ON taufgaben.fid = patchdaten.faufgaben_id JOIN vprojekt ON taufgaben.fprojekt_id = vprojekt.tprojekte_fid join taufgaben_mitarbeiter am on taufgaben.fid = am.faufgaben_id where am.fmitarbeiter_id = 54 and taufgaben.fbearbeitungsstatus 2 Nested Loop (cost=1349.13..1435.29 rows=1 width=2541) (actual time=1640.000..3687.000 rows=62 loops=1) Join Filter: (inner.fid = outer.faufgaben_id) - Index Scan using idx_tauf_mit_mitid on taufgaben_mitarbeiter am (cost=0.00..80.65 rows=35 width=4) (actual time=0.000..0.000 rows=765 loops=1) Index Cond: (fmitarbeiter_id = 54) - Materialize (cost=1349.13..1349.20 rows=7 width=2541) (actual time=0.531..1.570 rows=1120 loops=765) - Merge Join (cost=1343.42..1349.13 rows=7 width=2541) (actual time=406.000..515.000 rows=1120 loops=1) Merge Cond: (outer.fid = inner.fprojekt_id) - Sort (cost=130.89..130.90 rows=6 width=1494) (actual time=203.000..203.000 rows=876
Re: [PERFORM] Optimize complex join to use where condition before
Sebastian Hennebrueder [EMAIL PROTECTED] writes: User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) ... Nested Loop (cost=1349.13..1435.29 rows=1 width=2541) (actual time=1640.000..3687.000 rows=62 loops=1) Join Filter: (inner.fid = outer.faufgaben_id) - Index Scan using idx_tauf_mit_mitid on taufgaben_mitarbeiter am (cost=0.00..80.65 rows=35 width=4) (actual time=0.000..0.000 rows=765 loops=1) Is it really Mozilla Thunderbird that's causing this new craptastic mangling of plans in people's mails? I was assuming it was some new idea of how to mess up people's mail coming out of Exchange or Lotus or some other such corporate messaging software that only handled SMTP mail as an afterthought. This is, uh, disappointing. -- greg ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match