Optimizing Multiply Joins ??? - Mailing list pgsql-sql

Hi all,

We are building a sophisticated and flexible database structure and thus,
we have quite complicated and longish queries containing lots of joins.

Using only a few test records in our structure we have performed some
measures, and it is hard to interpret the results.

Until we join no more than 10 tables the response time is below 0.2 s.
joining the 11th table comes with a dramatic change: response time
usually grows up to 5-7 s, 

I'we read the related pages of the documentation, and found the
description of the default and the genetic optimizer too. And also found
the story about the german knowledge-based system project where longer
queries than 10 joins were also too slow.

But I think (hope) we could have a solution, because all of our
complex joins are following foreign keys. 

If we could give some hints to the planner about foreign keys, it
should not generate plenty of unusable plans for the optimizer.


Here I send an example:

the query:
select        h.literal as division,        j.literal as soatype,        e.username,        e.password,
c.objectid
 
from        o_division as a        join o_soa as b                on b.divisionobjectid=a.objectid        join
o_soainstanceas c                on c.soaobjectid=b.objectid        join o_staff_rdl_soainstance_role_ as d
  on d.soainstanceobjectid=c.objectid        join o_electronic as e                on e.pointerobjectid=d.objectid
 join o_soatype as f                on f.objectid=b.soatypeobjectid        join o_meaning as g                on
g.objectid=a.name       join o_meaning_rndl_language_role_ as h                on h.meaningobjectid=g.objectid
     and h.languageobjectid=100001        join o_meaning as i                on i.objectid=f.name        join
o_meaning_rndl_language_role_as j                on j.meaningobjectid=i.objectid                and
j.languageobjectid=100001      join o_staff as k                on k.objectid=d.staffobjectid       join
o_externalcontributoras l                on l.pointerobjectid=k.objectid
 


the structure behind it:
[the arrows are representing the foreign keys.]
a -> g <- h^|b -> f -> i <- j^|c^|d -> k <- l^|e

results of this query:join from a to j takes 0.2 s      a to k takes 4.8 s      a to l takes 5.2 s
I have examined the output of explain in all 3 cases, and I havethe feeling that the planner simply forgets the best
solutionsin2nd and 3rd case.
 

If this is not enough info for the answer I can send the tables, their
contents, the output of the optimizer..... or whatever you need for the
answer (including beer :)

Attila