[marginalia_nu]

Depends a lot on the size of the tables, but that is true for anything to do with databases. It's probably hard to give one-size-fits-all advice about database management, since you're ultimately balancing conflicting interests (query performance, maintainability, disk space).

With a sufficiently small database, even non-indexed joins may appear fast.

If your database has billions of rows or more, then even indexed joins will need to be used judiciously, especially if you have a lot of indexes. The indexes will probably also become very large on disk (possibly 100s of Gb), and they'll also degrade the performance since more than likely the system will struggle to keep them in RAM.