Please try out 0.9 from the git master which fixes the issue of the nested SELECT on the right side of a join.
Sent from my iPhone On Jun 13, 2013, at 9:18 AM, Ladislav Lenart <lenart...@volny.cz> wrote: > Hello. > > I have a query that does a full scan of an inherited table with more than > million rows even though I need only 100 of them (on postgres 9.1). This is a > real bummer! Please help me rewrite the SA query or instruct postgres to not > do > this stupidity. > > > I have the following setup (only the interesting relations): > > Contact > contact_tags -> ContactTag (collection) > phones -> Phone (collection) > emails -> Email (collection) > > ContactTag > tag -> Tag (cannot be NULL) > > PersonalContact (Contact subclass) > partner -> Partner (can be NULL) > client -> PersonalClient (can be NULL) > > CorporateContact (Contact subclass) > client -> CorporateClient (can be NULL) > > Client > > PersonalClient (Client subclass) > data -> PersonalData (cannot be NULL) > > CorporateClient (Client subclass) > data -> CorporateData (cannot be NULL) > > > I have the following query that loads data of one window: > > # window (input argument) is a list of id values. > q = session.query(PersonalContact).filter(PersonalContact.id.in_(window) > q = q.options( > subqueryload_all(PersonalContact.contact_tags, ContactTag.tag), > subqueryload(PersonalContact.phones), > subqueryload(PersonalContact.emails), > joinedload_all( > PersonalContact.partner, > Partner.personal_data, > PersonalData.address, > ), > joinedload_all( > PersonalContact.client, > PersonalClient.data, > PersonalData.address > ), > ) > > > (Note that I have similar query for CorporateContact. Infact, I generate them > both in the same method.) > > It produces SQL like this (the problematic part is emphasized): > > SELECT * > FROM > contact > JOIN personal_contact ON contact.id = personal_contact.id > -- *************** > LEFT OUTER JOIN ( > SELECT * > FROM > client > JOIN personal_client ON client.id = personal_client.id > ) AS anon_1 ON personal_contact.client_id = anon_1.personal_client_id > -- *************** > LEFT OUTER JOIN partner AS partner_1 > ON personal_contact.partner_id = partner_1.id > LEFT OUTER JOIN personal_data AS personal_data_1 > ON partner_1.personal_data_id = personal_data_1.id > LEFT OUTER JOIN address AS address_1 > ON personal_data_1.address_id = address_1.id > LEFT OUTER JOIN personal_data AS personal_data_2 > ON anon_1.personal_client_data_id = personal_data_2.id > LEFT OUTER JOIN address AS address_2 > ON personal_data_2.address_id = address_2.id > WHERE personal_contact.id IN (...) > > > The inner select directly corresponds to joinedload of > PersonalContact.client, a > PersonalClient instance (and a Client subclass). > > The postgres does a full scan of tables Client and PersonalClient even though > I > will need at most 100 rows from each one. > > However, if I rewrite the problematic part by hand like this: > > LEFT OUTER JOIN client > ON personal_contact.client_id = client.id > LEFT OUTER JOIN personal_client > ON client.id = personal_client.id > > it works like a charm. > > Unfortunately I don't know how to write such a query in SA. I am really stuck > so > any help is much appreciated. > > > Thank you, > > Ladislav Lenart > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
