Обсуждение: Query length restriction in v3 protocol?
-----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-----
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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 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... ;-) > 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). thanks already for the great 8.2 announcement, it lifts my hope... :-) - -- 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 iD8DBQFEYTxwfGgGu8y7ypARAgkdAJ0eZ7UeiVJtk+coZ+L95GK8Ti39UgCgs9vU HMcO7yHXi/SFFPGLo0XalyE= =JjFA -----END PGP SIGNATURE-----
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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi Mark, Mark Lewis schrieb am 11.05.2006 00:33: > Patric, > >> Hi Mark, >> > 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. > from the mail: " CVS HEAD can re-order left joins in common cases, but no existing release will touch the ordering of outer joins at all. " That does not sound like there's anything done with the outer joins...? I want to be sure there's a realistic chance of enhancing things really.. maybe you have a link onto the CVS commit info? So far, I even disabled the nestedloop on all 8.1 servers and increased effective_cache_size to push the optimizer to do index lookups more often in that case. So far, it helps... but all in all this wasn't necessary in 8.0... But nevertheless, the union mapping seems *far* better performance-wise atm. Pity. No constraints. No unique id index. ;-( > 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. Yep. That's why it works for you... We have the common superclass pattern in our ORM, which I like most, and our inheritance trees are partly very deep (8-10 levels is quite common) and make heavy use of polymorphic relations and queries. Which is wonderful and elegant, object-wise seen. Which is a great pita on the SQL side... :-\ - -- 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 iD8DBQFEYqDwfGgGu8y7ypARAhLfAKCvG8bVSyy12R8jT9EInLVbgeMDpACeO3Yl VjgWeaR5WMyI0gJ8tryfgWY= =pHBZ -----END PGP SIGNATURE-----
On Thu, 2006-05-11 at 10:26 +0800, Patric Bechtel wrote: > > 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. > > > from the mail: " > CVS HEAD can re-order left joins in common cases, but no existing > release will touch the ordering of outer joins at all. > " A "left join" is an abbreviation for "left outer join", which is the kind of outer join that Hibernate uses, and the left join re-ordering fixes are definitely in CVS HEAD right now so I would anticipate you would see a performance boost. There's more discussion of the specific changes in the archives for the performance mailing list, if you're interested. -- Mark