On Thu, Sep 20, 2018 at 11:16 AM Jayson Reis <santosdosr...@gmail.com> wrote: > > 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?
big time, if you are seeing the join be the difference between 8 seconds and 100ms. at first I wasnt sure how to do it but then I realized duh, we just add a flag so that it's opt-in, it's a total win. > 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. -- 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.
