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_soainstance as 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_externalcontributor as 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 have
the feeling that the planner simply forgets the best solutions
in 2nd 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
