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:[EMAIL PROTECTED] Envoyé : 6 août 2007 21:31 À : Gregory Stark Cc : Heikki Linnakangas; Mouhamadou Dia; pgsql-bugs@postgresql.org Objet : Re: RE : RE : [BUGS] BUG #3519: Postgres takes the wrong query plan resulting in performance issues Gregory Stark <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
