Re: [PERFORM] Optimize complex join to use where condition before

2005-05-13 Thread John A Meinel
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

2005-05-13 Thread Sebastian Hennebrueder
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

2005-05-13 Thread John Arbash Meinel
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

2005-05-12 Thread Sebastian Hennebrueder
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

2005-05-12 Thread Sebastian Hennebrueder
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

2005-05-12 Thread Greg Stark

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