On Oct 7, 2012, at 6:56 PM, Michael Bayer wrote: > > On Oct 7, 2012, at 2:17 AM, me wrote: > >> using python 2.7, sqla 0.7.9, postgresql 9.1 >> >> i've setup joined table inheritance and wanted an eager loaded relationship >> from one of the derived objects to another derived object. when i do that i >> get what seems to be a really inefficient query. >> >> here is the minimal setup: >> >> http://pastebin.com/yS7gDfju >> >> and this is the output: >> >> SELECT >> ... >> FROM root >> JOIN type2 ON root.id = type2.id >> LEFT OUTER JOIN (SELECT root.id AS root_id, root.type AS root_type, type1.id >> AS type1_id, type1.field1 AS type1_field1 >> FROM root JOIN type1 ON root.id = type1.id) >> AS anon_1 ON type2.type1_id = anon_1.type1_id >> >> what i expected was: >> >> SELECT >> ... >> FROM root >> JOIN type2 ON root.id = type2.id >> LEFT OUTER JOIN root as root_1 ON root_1.id = type2.type1_id >> LEFT OUTER JOIN type1 ON type1.id = root_1.id >> >> which runs *much* faster for the data sets i've tried. >> >> is there a way to force the relationship to generate the 2nd form? > > there's not, and the best this would ever do if SQLA were to have a > particular set of improvements that are fairly involved, would be this form: > > select .. from root JOIN type2 ON <onclause> LEFT OUTER JOIN (root JOIN > type1 ON <onclause>) ON <onclause>
so check out ticket 2587: http://www.sqlalchemy.org/trac/ticket/2587 I've created a really simple redefinition of the idea of an "anonymous alias" of a join, to change from being this: (SELECT * FROM table1 JOIN table2 ON <onclause>) AS anon_1 to this: table1 AS anon_1 JOIN table2 AS anon_2 ON <adapted onclause> that is, instead of wrapping "A JOIN B" in "SELECT" and aliasing the SELECT, we make an alias of the individual tables and "adapt" the ON clause. By allowing a mapper configuration option to enable this, running on a DB that supports nesting of JOIN like PG it seems to work nicely for joined eagers as well as the target of query.join(). The targeting of the aliased columns by the mappers, what's often the really hard part of this kind of thing, seems to work fine in this case as I can get ORM-interpreted objects back from these rows without issue: SELECT c.id AS c_id, b_1.id AS b_1_id, a_1.id AS a_1_id, b_1.c_id AS b_1_c_id FROM c LEFT OUTER JOIN (a AS a_1 JOIN b AS b_1 ON a_1.id = b_1.id) ON c.id = b_1.c_id SELECT c.id AS c_id, b_1.id AS b_1_id, a_1.id AS a_1_id, b_1.c_id AS b_1_c_id FROM c JOIN (a AS a_2 JOIN b AS b_2 ON a_2.id = b_2.id) ON c.id = b_2.c_id LEFT OUTER JOIN (a AS a_1 JOIN b AS b_1 ON a_1.id = b_1.id) ON c.id = b_1.c_id WHERE b_2.id < %(id_1)s {'id_1': 3} SELECT b_1.id AS b_1_id, a_1.id AS a_1_id, b_1.c_id AS b_1_c_id, b_2.id AS b_2_id, a_2.id AS a_2_id, b_2.c_id AS b_2_c_id FROM a AS a_1 JOIN b AS b_1 ON a_1.id = b_1.id JOIN (a AS a_2 JOIN b AS b_2 ON a_2.id = b_2.id) ON b_1.id > b_2.id {} Running it through some of the "real" tests it works pretty well more often than not, though as expected it fails in some of the more complicated query/loading situations, which would have to be worked out. I have this as a 0.9 feature but because it's totally not in the way of anything, I could make this an "experimental" feature in 0.8, if the above system of joining produced better query plans for you. > > that is, the target eager join would be parenthesized without the SELECT > subquery part of it. Most databases with the exception of SQLite and older > MySQL versions support parenthesization of this form. > > The grouping is necessary as it is very difficult to "de-parenthsize" the > joins in the way you describe without changing the meaning of complex > queries. Just removing the SELECT has shown to be a complex task. > Supporting auto-rendering of a JOIN b LEFT OUTER JOIN (c JOIN d) within > joined eager loading is related to ticket #2369, so I've added this variant > as a use case. > > Normally, if this were just an explicit query.join(), the way to get exactly > the query you want would be to use the Table objects to construct exactly the > join you want, as introduced at > http://docs.sqlalchemy.org/en/rel_0_7/orm/inheritance.html#advanced-control-of-which-tables-are-queried > . This can be combined with contains_eager() to provide joined eager > loading against a specific query. But in this case the eager loading wouldn't > be automatic. > >> or am i doing something i shouldn't? > > I don't often use "lazy='joined'" as part of a relationship(). I'd only use > it on many-to-one, as is the case here, but also with many-to-ones I'll often > leave those as "lazy", if the case is such that I'm loading lots of Type2s > that refer to a small set of Type1s, I'd only get as many SELECT statements > as there are Type1 identities referred to in the whole set being loaded. > > Another trick that I haven't tried but might work is to make the relationship > to Root, and then modify the primaryjoin to also specify the polymorphic > identity of "type1": > > relationship("Root", primaryjoin="and_(Type2.type1_id == Root.id, > Root.type=='TYPE1')") > > I haven't tried that but it might work. > > Finally, if performance is critical, I'd be shying away from excessive use of > joined inheritance in the first place. I actually built a datamodel that > was PG and based a large and complex series of models on a shared > joined-inheritance base, and it was this kind of AjoinB->AjoinC thing all the > way, and eventually you could kind of feel how much work you were making the > database do just for a little bit of purity, it probably wasn't worth it. > The way it ended up working was that we made extensive usage of relationship > caching using the Beaker caching example in the docs (which will become > dogpile.cache in 0.8), and then turned off most eager loading. The > many-to-ones were typically pulling from a limited set of entities that was > just 100% cached within a few minutes of the app first starting up. > > Can't quite give you the answer you want, just a bunch of ways to almost get > there; hope one of them works out. > > > > > > > > > > > > > > >> >> thanks >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To view this discussion on the web visit >> https://groups.google.com/d/msg/sqlalchemy/-/0laXtaxdh8UJ. >> To post to this group, send email to [email protected]. >> To unsubscribe from this group, send email to >> [email protected]. >> For more options, visit this group at >> http://groups.google.com/group/sqlalchemy?hl=en. > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
