Is this due to query optimization, or something else?

I’d love to see an answer from someone more experienced on this as well.

In my experience, the database is just faster than application code at most stuff and queries can be written in a way that similar logic can’t be optimized at the application level. Additionally, database code is usually (always?) going to be faster than something like Python/Ruby.

Code review is more difficult at the database level, however, and SQL talent is more rare. It may be more challenging to scale and maintain a team of engineers working on a codebase with a lot of application logic implemented in stored procs/SQL versus ORM code (such as active record or Django). Just my opinion, have worked on teams with ORM usage as well as separate DBA teams and all logic in SQL. SQL by hand is more powerful and flexible, surely, but for most use cases I would prefer to keep code in one place (application) and only reach for the tool (raw SQL) when needed for optimizations.

There's a meaningful difference to where a particular calculation happens. Even if the exact same steps are done, it's more efficient to look at 10 items in the database, pick 1 item, and transfer it out to the application rather than transfer 10 items to the application and then pick one item there; doing the calculation close to the source data enables ways to do it efficiently.

Optimizer (with the statistical knowledge about the data) and being closer to the data (important data may be always in memory) and most importantly supportive data structures like indexes and materialized views.

RMBDS systems are very good at pruning results. They can perform complex multi step operations without round trips. Etc