About point 5, correlated subquery: by my experience it might be a great performance improvement to move fields which are not part of filter conditions to the correlated subqueries when you are relaying on paging (limit/offset). The idea is to fetch limited number of rows and then run all the subqueries which are supposed to be more efficient than to join multiple tables. Also, think of database cache. Modern dbms aren’t so stupid to fetch data if they have it already in cache.

About other advises on top of my mind:

• Particular query plans depend on your db vendor. Make sure you know your dbms well before optimization.
• For COST based optimizator multiple simple queries are better then one complex. Consider to retrieve data and join them on your app.
• Gold rule: In all your queries you should have either a selective predicate or limit/offset.
• Use cascade WITH clause if your DBMS supports it.
• Be carefull with using views. Not all DBMSes push predicates. As a result you we get full fetch from view and then filter applied on view’s result. Most likely that’s not smth you expect.

Actually, there are much more… Good luck.