[Sqlalchemy-tickets] Issue #4340: Allow selectinload to skip join and filter directly on the target table (zzzeek/sqlalchemy)

[Jayson R. <iss...@bi...>]

New issue 4340: Allow selectinload to skip join and filter directly on the target table
https://bitbucket.org/zzzeek/sqlalchemy/issues/4340/allow-selectinload-to-skip-join-and-filter

Jayson Reis:

This is a proposal to change how selectinload runs the query for 1xN queries.

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. 

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:

```sql
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:

```sql
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.

Talking on the mailing list Michael Bayer suggests having a flag called omit_join on selectinload to deal with this edge case without breaking compatibility.
Here [3] is a working in progress pull request.

[1] https://gist.github.com/jaysonsantos/e19af47ac5d57aa5e2e2a7ed2a950994

[2] https://gist.github.com/jaysonsantos/e19af47ac5d57aa5e2e2a7ed2a950994#file-2_output-txt

[3] https://bitbucket.org/zzzeek/sqlalchemy/pull-requests/7/selectinload-omit-join/diff#comment-76901579