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.
