On Wed, 2012-12-05 at 17:16:01 -0800, Hong Minhee wrote: > But for work in works: work.like_query.count() causes inefficient 1+N > queries even if we have the right indices for it. Of course I could query > like session.query(Work, count()).join(Work.like_set).group_by(Work) but > it’s somewhat complicated to read and write for me (is it only me?). I > want to reject such uses in ORM… > > Anyway I use PostgreSQL.
But using JOINs is how relational DBs are meant to be used. I had similar mindset like yours not that long ago. I didn't really understand how the database was working, it was kind of blackbox to me. What helped me immensely was writing pure SQL queries for Postgres in one of my jobs. Not only that, but also reading EXPLAIN query plans, seeing how and when the indices were used, etc. There are still many things to learn, but I have much better understanding of relational DBs now. This also helped me to see why SQLAlchemy is as it is and appreciate it even more. Its exposure of SQL is a good thing, contrary to other ORMs, which try to hide it as much as possible, limiting their flexibility and power in result. So I strongly encourage you to learn more about database you are using and SQL in general. This will help you to write more efficient queries and better leverage the power of your RDBMS. In case of your JOIN query, even if you have the right indices, it could still be slow, if it's not selective enough (it selects big part of the table). In that case index simply won't be used by the query planner. -- Audrius Kažukauskas http://neutrino.lt/
pgpEoDeVdzBKx.pgp
Description: PGP signature
