I ended up manually overloading get_extra_restriction on a custom ForeignKey class (we couldn't use FilteredRelation b/c we were still on django 1.11), which ensured that the JOIN ON ... clause limited the tables being joined to their correct partition/schema while being accessed through a view
The view thing is a long story — it was a legacy PAC codebase from the '90s which used 13+ schemas in a mysql db that was then being synced to our postgres db through Amazon DMS. All of the tables on each view contain identical source_schema/CompanyID columns, hence the
'%(remote_alias)s. "source_schema" = %(fk_alias)s. "source_schema" AND '
'%(remote_alias)s. "CompanyID" = %(fk_alias)s. "CompanyID"
before/after query plan in depesz: https://imgur.com/a/HQbNSIL
As an aside why not use postgres's mysql foreign data wrapper instead of syncing with the mysql database?
To directly answer your question, though, without going into too much detail, there were pci compliance issues with some tables/columns in the mysql db, so if I had to guess, perhaps DMS had the capability to assuage that concern more elegantly than mysql_fdw[0], though the mysql_fwd EXCEPT parameter seems to also do the same thing, so honestly, I have no idea.
By default, all views and tables existing in a particular database on the MySQL host are imported. Using this option, you can exclude specified foreign tables from the import.[1]
[1]: https://www.enterprisedb.com/docs/mysql_data_adapter/latest/...
With CTEs you can offload sophisticated joins and constraints in such a way that less experienced developers can follow behind more easily.
Once you find multiple queries using the same WITH clauses, you can create more permanent views that further centralize and optimize these concerns.