Querying a composite key in SQLAlchemy-ORM

Question

Background

I have a composite index defined on a SQLAlchemy object, say:

class Shirt(Base):
    __tablename__ = 'shirt'
    id = Column(Integer, primary_key=True)
    size = Column(String(32))   # e.g. small, medium large
    color = Column(String(32))  # e.g. blue, red, white

Index('color_size', Shirt.size, Shirt.color)

---

Question

I'd now like to do a search for small and red shirts, taking advantage of the color_size composite index.

How do I write this query?

Would the use of and_() automatically take advantage of the index?
For example:

results = Shirt.query.filter( and_(Shirt.size=='small', Shirt.color=='red') ).all()

Answer 1

Yes, the index should be used. SQLAlchemy isn't using the index though, it only defines the index. It's up to the database to use it for a given query.

You can use EXPLAIN to prove that the index is being used. For example, PostgreSQL shows an Index Scan.

example => explain select id from shirt where color = 'red' and size = 'small';
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Index Scan using ix_shirt_size_color on shirt  (cost=0.15..8.17 rows=1 width=4)
   Index Cond: (((size)::text = 'small'::text) AND ((color)::text = 'red'::text))