This fits my experience as well. Tracing the queries SQLAlchemy builds based on the models of our Python application the DB is returning all of the columns for every model instead of just the columns that will then be returned via the API to the client. They’re not even just the queries to compute the relation but all the columns from every relation involved and then at return time only a small subset of columns is used. Its terrible for query perf as we almost never do just index scans because we also have to pull data from the other columns. It’s a waste. I’m not sure how to fix that just yet without breaking the ease of use. The idea of being able to say model.select_one_or_none()... or something similar I can easily tell is quick and painless and easy to train about but doesn’t do the Adan justice. And then I hear the DB is slow! It’s a battle I’m losing but I hope to help improve where I can.
This sounds exactly like my current project. The ORM is pulling all fields for the model objects and related objects instead of the two or three it actually needs and there’s no easy fix because too many unrelated features make use of the same query code, but use different fields. I’ll slowly change it to using multiple single purpose queries probably, but it’s going to be a tedious task.
As an experiment, I changed one to only fetch the fields that it needs and the query ran in approx. 15% of the time. That’s a pretty big improvement! Sigh.
