From: | "Mouhamadou Dia" <MDia(at)accovia(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Gregory Stark" <stark(at)enterprisedb(dot)com> |
Cc: | "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>, <pgsql-bugs(at)postgresql(dot)org> |
Subject: | RE : RE : RE : BUG #3519: Postgres takes the wrong query plan resulting in performance issues |
Date: | 2007-08-07 14:31:14 |
Message-ID: | BB6605E56C79CB4FA6CA491B706FBB210112E1@cpt127.magrit.int |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Thanks Tom,
By setting from_collapse_limit to more than 10, the query takes 133ms instead of 20s.
My question is: why even if from_collapse_limit is set to 8 (it's default value), the same query takes 30ms just by changing the order of PRPT_PRT and PROR_ORG tables in the query?
-----Message d'origine-----
De : Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Envoyé : 6 août 2007 21:31
À : Gregory Stark
Cc : Heikki Linnakangas; Mouhamadou Dia; pgsql-bugs(at)postgresql(dot)org
Objet : Re: RE : RE : [BUGS] BUG #3519: Postgres takes the wrong query plan resulting in performance issues
Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> The structure of your query is a whole series of left outer joins, the result
> of which is then (inner) joined with one more table. The outer joins return a
> whole lot of records but the inner join is only going to match a few of them.
Hmmm ... actually I see 6 tables inside the join-tree and four more
loose in the FROM-clause, ten relations altogether. Which means the OP
is falling foul of from_collapse_limit, and it's not investigating every
possible join order. Try setting from_collapse_limit to more than 10.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Magnus Hagander | 2007-08-07 18:32:38 | Re: BUG #3520: insert causing error "invalid memory alloc request size 2147483648" |
Previous Message | Brodie Thiesfield | 2007-08-07 14:28:17 | Re: BUG #3520: insert causing error "invalid memory alloc request size 2147483648" |