Hi there, I guess I will try to change in the next following days. Do you think a change like this is worth it for SQLAlchemy? Jayson Reis
Em qui, 20 de set de 2018 às 16:31, Mike Bayer <mike...@zzzcomputing.com> escreveu: > for guidance on how the flag is added, look at the "innerjoin=True" > flag on joinedload() / relationship(). > On Thu, Sep 20, 2018 at 10:30 AM Mike Bayer <mike...@zzzcomputing.com> > wrote: > > > > you would need to change the code here: > > > > > https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/orm/strategies.py#L1914 > > > > however, you can't just change the query to unconditionally not > > include the parent table, because you don't know what the > > "primaryjoin" is. for this to be automatic, the primaryjoin would > > need to be traversed and tested to ensure it is a direct comparison of > > FK to PK col and that the FK values can be substituted directly. > > > > Here's a primaryjoin that *wont* work, for example: > > > > > https://github.com/zzzeek/sqlalchemy/blob/master/test/orm/test_relationships.py#L3017 > > > > to make it easy, your optimized query can be switched on a user-set > > flag, rather than attempting to guess. like selectinload(Foo.bar, > > omit_join=True) / relatonship(..., lazy="selectinload", > > omit_join=True). That would be vastly easier to test and release > > with no chance of surprising anyone. > > > > > > > > > > On Thu, Sep 20, 2018 at 9:08 AM Jayson Reis <santosdosr...@gmail.com> > wrote: > > > > > > 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 sqlalchemy+unsubscr...@googlegroups.com. > > > To post to this group, send email to sqlalchemy@googlegroups.com. > > > Visit this group at https://groups.google.com/group/sqlalchemy. > > > For more options, visit https://groups.google.com/d/optout. > > -- > 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 sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
