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.
