RE : RE : RE : BUG #3519: Postgres takes the wrong query plan resulting in performance issues

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