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

Re: Query length restriction in v3 protocol?

From: Mark Lewis <mark(dot)lewis(at)mir3(dot)com>
To: Patric Bechtel <bechtel(at)ipcon(dot)de>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Query length restriction in v3 protocol?
Date: 2006-05-10 16:33:11
Message-ID: 1147278792.32369.86.camel@archimedes (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

In response to

Responses

pgsql-jdbc by date

Next:From: Dan ArmbrustDate: 2006-05-10 17:36:27
Subject: Re: Problem dropping a table
Previous:From: Csaba NagyDate: 2006-05-10 16:27:46
Subject: backwards compatibility problem

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