Re: Query plan for very large number of joins - Mailing list pgsql-performance

From
Subject Re: Query plan for very large number of joins
Date
Msg-id 774541.1117879450594.JavaMail.tomcat@iecsai19
Whole thread Raw
In response to Query plan for very large number of joins  (<philb@vodafone.ie>)
List pgsql-performance
>> 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



pgsql-performance by date:

Previous
From: hubert lubaczewski
Date:
Subject: strategies for optimizing read on rather large tables
Next
From: Christopher Kings-Lynne
Date:
Subject: Re: strategies for optimizing read on rather large tables