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:
h.literal as division,
j.literal as soatype,
o_division as a
join o_soa as b
join o_soainstance as c
join o_staff_rdl_soainstance_role_ as d
join o_electronic as e
join o_soatype as f
join o_meaning as g
join o_meaning_rndl_language_role_ as h
join o_meaning as i
join o_meaning_rndl_language_role_ as j
join o_staff as k
join o_externalcontributor as l
the structure behind it:
[the arrows are representing the foreign keys.]
a -> g <- h
b -> f -> i <- j
d -> k <- l
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 :)
pgsql-sql by date
|Next:||From: Jerome Raupach||Date: 2000-09-13 14:40:44|
|Subject: work on some tables in the same time.|
|Previous:||From: Oleg Bartunov||Date: 2000-09-13 03:07:00|
|Subject: Re: [SQL] Weighted Searching|