Re: Query length restriction in v3 protocol? - Mailing list pgsql-jdbc

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.

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 on the other hand will always add outer joins in the order
that the subclass elements are defined in hibernate.cfg.xml.

So you may find that playing with the order in which elements are listed
in hibernate.cfg.xml will have a dramatic impact on query performance
using joined-subclass.

-- Mark Lewis

On Tue, 2006-05-09 at 23:47 +0800, Patric Bechtel wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hi,
>
> I'm struggling with hibernate and postgresql, because after some
> performance analysis, it seems that it's wise to use table per concrete
> class mapping, which means that all polymorphic queries result in huge
> (I mean, very huge) union queries.
> The question that I have is, if there's any length restriction regarding
> on commands sent to the backend. I've seen queries 1.5 MB long, is that
> really a problem or is there another solution for that (could be that
> it's only possible with views in between or such)?
> Normally I wouldn't even dare to send such beasts to the backend because
> I would worry about parsing times and such, but I've seen the
> performance of outer join queries over 5-9 tables (=inheritance depth in
> O/R model), which is even more scary...
>
> Thanks in advance,
>
> - --
> Mit freundlichen Gruessen / Regards
> Patric Bechtel, IPCON Informationssysteme OHG
> Kontakt: http://www.ipcon.de/kontakt.php
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.2.2 (MingW32)
> Comment: GnuPT 2.5.2
>
> iD8DBQFEYLl2fGgGu8y7ypARAktCAKDMOBCM/1DviG0xGJ+TWHM2By6ZSACgjPxC
> GdcrQsQkZeO5+hO9yF0jdKU=
> =cFRc
> -----END PGP SIGNATURE-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq