Обсуждение: Re: RE : RE : BUG #3519: Postgres takes the wrong query plan resulting in performance issues
Re: RE : RE : BUG #3519: Postgres takes the wrong query plan resulting in performance issues
От
Heikki Linnakangas
Дата:
Hmm. I don't see anything terribly wrong in the planner's estimates. The only estimate that's off is the # of rows in pror_org matching the qual orgt_cd = 'CHAIN', 27 estimated vs 1 actual. You could try increasing the statistics target for that column to get that estimate right. That might tip the planner to choose a plan with nested loop joins instead of hash joins. Have you played with enable_seqscan=off or enable_hashjoin=off? That's not a good long term solution, but it would be interesting to see what happens. Mouhamadou Dia wrote: > Sorry, > This output is coming from PG 8.1.19 > I'm attaching the one that is coming from 8.2.4 > Thanks and sorry for the confusion > > > -----Message d'origine----- > De : Heikki Linnakangas [mailto:hlinnaka@gmail.com] De la part de Heikki Linnakangas > Envoyé : 6 août 2007 15:32 > À : Mouhamadou Dia > Cc : pgsql-bugs@postgresql.org > Objet : Re: RE : [BUGS] BUG #3519: Postgres takes the wrong query plan resulting in performance issues > > Mouhamadou Dia wrote: >> I'm sending in attachment the output of the explain analyze command and the create table statements of tables involvedin the query. > > Wait, you said that the query takes 20 seconds on 8.2, but the explain > analyze output says that it actually took 50 seconds. Is this the output > from 8.2.4? > -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Re: RE : RE : BUG #3519: Postgres takes the wrong query plan resulting in performance issues
От
Gregory Stark
Дата:
"Heikki Linnakangas" <heikki@enterprisedb.com> writes: > Have you played with enable_seqscan=off or enable_hashjoin=off? That's > not a good long term solution, but it would be interesting to see what > happens. I think this is a case where Postgres just doesn't know it can re-order near an outer join. Outer joins often can't be re-ordered and Postgres isn't a general theorem prover, it can't always figure out whether it's safe to re-order them. 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. The only hope you have of a reasonable plan here is if Postgres can figure out that it can do the inner join first so that it only has to perform the outer join on the resulting records. I think it could actually re-order the inner query to happen first in this case. But I'm not certain, it's tricky to tell. But the fact that Oracle finds a way to execute it quickly gives me some confidence that it ought to be possible since I think Oracle does get join orderings reasonably right. I'm not so sure about Informix. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Gregory Stark <stark@enterprisedb.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
Re: RE : RE : BUG #3519: Postgres takes the wrong query plan resulting in performance issues
От
Gregory Stark
Дата:
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > Gregory Stark <stark@enterprisedb.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. The three other loose ones are attached to a table inside outer joins though. Doesn't that prevent any possibility of them being done earlier? But the first one looks like it ought to be driving the join. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
RE : RE : RE : BUG #3519: Postgres takes the wrong query plan resulting in performance issues
От
"Mouhamadou Dia"
Дата:
I've played with both parameters but it doesn't help in this case Thanks -----Message d'origine----- De=A0: Heikki Linnakangas [mailto:hlinnaka@gmail.com] De la part de Heikki = Linnakangas Envoy=E9=A0: 6 ao=FBt 2007 16:58 =C0=A0: Mouhamadou Dia Cc=A0: pgsql-bugs@postgresql.org Objet=A0: Re: RE : RE : [BUGS] BUG #3519: Postgres takes the wrong query pl= an resulting in performance issues Hmm. I don't see anything terribly wrong in the planner's estimates. The only estimate that's off is the # of rows in pror_org matching the qual orgt_cd =3D 'CHAIN', 27 estimated vs 1 actual. You could try increasing the statistics target for that column to get that estimate right. That might tip the planner to choose a plan with nested loop joins instead of hash joins. Have you played with enable_seqscan=3Doff or enable_hashjoin=3Doff? That's not a good long term solution, but it would be interesting to see what happens. Mouhamadou Dia wrote: > Sorry, > This output is coming from PG 8.1.19 > I'm attaching the one that is coming from 8.2.4 > Thanks and sorry for the confusion >=20 >=20 > -----Message d'origine----- > De : Heikki Linnakangas [mailto:hlinnaka@gmail.com] De la part de Heikki = Linnakangas > Envoy=E9 : 6 ao=FBt 2007 15:32 > =C0 : Mouhamadou Dia > Cc : pgsql-bugs@postgresql.org > Objet : Re: RE : [BUGS] BUG #3519: Postgres takes the wrong query plan re= sulting in performance issues >=20 > Mouhamadou Dia wrote: >> I'm sending in attachment the output of the explain analyze command and = the create table statements of tables involved in the query. >=20 > Wait, you said that the query takes 20 seconds on 8.2, but the explain > analyze output says that it actually took 50 seconds. Is this the output > from 8.2.4? >=20 --=20 Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
RE : RE : RE : BUG #3519: Postgres takes the wrong query plan resulting in performance issues
От
"Mouhamadou Dia"
Дата:
Thanks Tom, By setting from_collapse_limit to more than 10, the query takes 133ms inste= ad of 20s. My question is: why even if from_collapse_limit is set to 8 (it's default v= alue), the same query takes 30ms just by changing the order of PRPT_PRT and= PROR_ORG tables in the query? -----Message d'origine----- De=A0: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 Envoy=E9=A0: 6 ao=FBt 2007 21:31 =C0=A0: Gregory Stark Cc=A0: Heikki Linnakangas; Mouhamadou Dia; pgsql-bugs@postgresql.org Objet=A0: Re: RE : RE : [BUGS] BUG #3519: Postgres takes the wrong query pl= an resulting in performance issues=20 Gregory Stark <stark@enterprisedb.com> writes: > The structure of your query is a whole series of left outer joins, the re= sult > of which is then (inner) joined with one more table. The outer joins retu= rn a > whole lot of records but the inner join is only going to match a few of t= hem. 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