Hi there everyone.
I would like to ask if there is a way to change how selectinload run its
query.
I put an example [1] that will show you sort of how my data is structured
so you can have an idea, but the gist is:
I have a main table (like customers) and another table (like order) which
has the detailed data and then I created a view where summarizes customer’s
orders.
The problem is that I am using selectinload and it runs the query on the
view joining customer’s table and PostgreSQL won’t be able to use the
proper indexes and will run it slowly. Is there a way to make SQLAlchemy
just query the view (without the join) as it already has the foreign key?
Here [2] you can see the output of the example with explain analyze of both
possible queries.
When I run query(User).options(selectinload(SummarizedOrder)).all() it will
run the query like this:
SELECT “fields”
FROM "user" AS "user_1"
JOIN "view_summarized_order" ON "user_1"."id" =
"view_summarized_order"."user_id"
WHERE "user_1"."id" IN (%(primary_keys_1) s, %(primary_keys_2) s)
ORDER BY "user_1"."id"
But to make it faster, it could be this:
SELECT "fields"
FROM "view_summarized_order"
WHERE "view_summarized_order"."user_id" IN (%(primary_keys_1) s,
%(primary_keys_2) s)
ORDER BY "view_summarized_order"."user_id"
In my production database, the first one will take around 8 seconds to run
and the second one around 100ms.
If it possible to do but a PR is needed, could you point out the places
that would need to be changed so I try to come up with a test and send a
patch afterwards?
Thank you in advance,
Jayson
[1] https://gist.github.com/jaysonsantos/e19af47ac5d57aa5e2e2a7ed2a950994
[2]
https://gist.github.com/jaysonsantos/e19af47ac5d57aa5e2e2a7ed2a950994#file-2_output-txt
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
