title: Performance issue with multiple table references to one
body: <p>Database model (simplified):</p>
  <pre><code>class Document(Base):
      __tablename__ = &quot;document&quot;
      id = Column(Integer, primary_key=True)
      ...
  
      @classmethod
      def link(cls):
          col = Column(Integer, ForeignKey(cls.id))
          rel = relationship(cls, uselist=False, lazy='selectin', foreign_keys=[col])
          return col, rel
  
  class File(Base):
      __tablename__ = &quot;document_file&quot;
      id = Column(Integer, primary_key=True)
      document_id = Column(Integer, ForeignKey(Document.id))
      document = relationship( Document, back_populates=&quot;files&quot; )
      ...
  
  class A(Base):
      __tablename__ = &quot;a&quot;
      id = Column(Integer, primary_key=True)
      ...
  
  class B(Base):
      __tablename__ = &quot;b&quot;
      id = Column(Integer, primary_key=True)
      a_id = Column(Integer, ForeignKey(A.id))
      a = relationship(A, back_populates=&quot;a&quot;)
      ...
      doc1_id, doc1 = Document.link()
      doc2_id, doc2 = Document.link()
      ....
  
  class C(Base):
      __tablename__ = &quot;c&quot;
      id = Column(Integer, primary_key=True)
      b_id = Column(Integer, ForeignKey(B.id))
      b = relationship(B, back_populates=&quot;a&quot;)
      ...
      doc_id, doc = Document.link()
      ...
  </code></pre>
  <p>There is entity <code>Document</code> used in domain-specific entities. It has complex hierarchical structure with several one-to-many layers. It's fetched as a single root object (class <code>A</code> in the example), then lazy-loading of SQLAlchemy does its magic.</p>
  <p>In a single one-to-many case SQLAlchemy detects it and loads whole list of objects in a single request to database. But in given case this optimization is not working: <code>Document</code> instances are fetched one-by-one. With 10k+ objects it becomes very slow.</p>
  <p>Two ways to solve this:</p>
  <ol>
  <li><p>Use <code>selectinload</code> policy for whole hierarchy. Drawback is I need a whole hierarchy after a check of several objects. Moreover, sometimes I can't use an additional query with <code>populate_existing</code> command because the objects are already modified.</p>
  </li>
  <li><p>Use SQLAlchemy mapper data to scan for references to <code>Document</code> and fetch it to the dictionary with a single request. But I can't just assign fetched objects to properties managed by SQLAlchemy, so this technique is not uniform with ordinary access to objects.</p>
  </li>
  </ol>
  <p>Two questions:</p>
  <ol>
  <li><p>Is there some another optimization technique?</p>
  </li>
  <li><p>Is there a more efficient solution in terms of database model architecture?</p>
  </li>
  </ol>