Message-ID: <774541.1117879450594.JavaMail.tomcat@iecsai19>
Date: Sat, 4 Jun 2005 11:04:10 +0100 (IST)
From: <philb@vodafone.ie>
Reply-To: <philb@vodafone.ie>
To: pgsql-performance@postgresql.org
Subject: Re: Query plan for very large number of joins
Cc: simon@2ndquadrant.com
Mime-Version: 1.0
Content-Type: text/plain;charset="UTF-8"
Content-Transfer-Encoding: 7bit

>> Despite being fairly restricted in scope,
>> the schema is highly denormalized hence the large number of tables.
>
>Do you mean normalized? Or do you mean you've pushed the superclass
>details down onto each of the leaf classes?

Sorry, I meant normalized, typing faster than I'm thinking here:) The schema 
was generated by hyperjaxb, a combination of Hibernate and JAXB. This allows 
one to go from XSD -> Object model -> Persistance in a single step. I'm 
just getting the hang of Hibernate so I don't know how flexible its' strategy 
is. Obviously though, the emphasis is on correctness first so while the 
same result could possibly be achieved more quickly with many smaller queries, 
it probably considers that it's up to the DBMS to handle optimisation (not 
unreasonably either I guess) 

Since the entire process from the XSD onwards is automated, there's no scope 
for tweaking either the OR mapping code or the DB schema itself except for 
isolated troubleshooting purposes. The XSD set in question is the UBL schema 
published by OASIS which has about 650 relations, I thought it would be 
nice to have this as a standard component in future development. 

Regards,
 -phil


>
>I guess I'm interested in what type of modelling led you to have so many
>tables in the first place?
>
>Gotta say, never seen 350 table join before in a real app.
>
>Wouldn't it be possible to smooth out the model and end up with less
>tables? Or simply break things up somewhere slightly down from the root
>of the class hierarchy?
>
>Best Regards, Simon Riggs


I'm using Vodafone Mail - to get your free mobile email account go to http://www.vodafone.ie
Use of Vodafone Mail is subject to Terms and Conditions  http://www.vodafone.ie/terms/website