Patric,
> Hi Mark,
>
> Mark Lewis schrieb am 10.05.2006 01:19:
> > This doesn't address your immediate problem, but the primary reason that
> > Hibernate joined-subclass performance stinks on PG is because the query
> > planner does a very poor job of optimizing outer joins, which Hibernate
> > uses extensively in its polymorphic queries for joined-subclass.
> >
> > One of the features coming in the 8.2 PG release is greatly improved
> > outer join planning, so you should be able to go back to joined-subclass
> > polymorphism, which is the easiest to work with in code anyway.
>
> Hm. "Easy" depends on the viewpoint. Granted, the table per concrete
> class is *very* easy to understand and insert/update performance is
> great. So far for the advantages. Drawback are the huge SQL queries in
> case of implicit polymorphism and the lack of constraints, which would
> be the reason to go back to normalized mapping after PG8.2.
> The outer join planning enhancements is in CVS already? If, I would give
> it a try... ;-)
The outer join improvements are already in CVS HEAD:
http://archives.postgresql.org/pgsql-performance/2006-02/msg00342.php
If you test with CVS HEAD could you please post your results back here?
I know I'd be really interested in the results.
> > There might also be a workaround you could use to get acceptable
> > performance out of joined-subclass using current versions of PG (Caveat:
> > I haven't tried this). When current versions of PG give up on
> > optimizing outer joins, they fall back to processing the joins in the
> > order listed in the SQL query. Depending on the order the joins are
> > listed, this could result in really bad performance.
>
> Hibernate always starts with the superclass down to the subclasses, so I
> think I cannot do something against it... (except for the ordering
> inside one level of inheritance inside one superclass).
Ah, that's too bad. The one place where we've needed to care about
Hibernate's join order was at a place where our inheritance tree is
flat, which explains why this trick worked for us.
-- Mark Lewis