Skip site navigation (1) Skip section navigation (2)

Optimizing Multiply Joins ???

From: Meszaros Attila <tilla(at)draconis(dot)elte(dot)hu>
To: pgsql-sql(at)hub(dot)org
Subject: Optimizing Multiply Joins ???
Date: 2000-09-13 14:16:57
Message-ID: Pine.LNX.3.96.1000913113245.18199M-100000@draconis.csoma.elte.hu (view raw or flat)
Thread:
Lists: 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_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


Responses

pgsql-sql by date

Next:From: Jerome RaupachDate: 2000-09-13 14:40:44
Subject: work on some tables in the same time.
Previous:From: Oleg BartunovDate: 2000-09-13 03:07:00
Subject: Re: [SQL] Weighted Searching

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group