The optimization process and recommendations:

1. Avoid Correlated Subqueries (query line: 7): A correlated subquery is a subquery that contains a reference (column: id) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
2. Avoid Correlated Subqueries (query line: 16): A correlated subquery is a subquery that contains a reference (column: id) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
3. Avoid Correlated Subqueries (query line: 24): A correlated subquery is a subquery that contains a reference (column: id) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
4. Avoid Selecting Unnecessary Columns (query line: 32): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
5. Avoid Subqueries (query line: 31): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
6. Avoid Subqueries (query line: 51): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
7. Avoid Subqueries (query line: 69): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
8. Avoid Subqueries (query line: 88): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
9. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
10. Prefer Direct Join Over Joined Subquery (query line: 49): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, we recommend to replace subqueries with JOIN clauses.
11. Prefer Direct Join Over Joined Subquery (query line: 38): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, we recommend to replace subqueries with JOIN clauses.
12. Replace Left Join With Subquery (modified query below): The pattern of inflating the amount of data (using joins) and deflating (using GROUP BY) usually slows down queries. In this case, it can be avoided by moving some of the logic to the SELECT clause, and therefore removing some of the LEFT JOINs. In some cases, this transformation can lead to an obsolete GROUP BY clause, which can also be removed.

Optimal indexes for this query:

CREATE INDEX endorsements_idx_portfolio_id ON "endorsements" ("portfolio_id");
CREATE INDEX favorites_idx_subject_id ON "favorites" ("subject_id");
CREATE INDEX portfolios_idx_user_id ON "portfolios" ("user_id");
CREATE INDEX views_idx_subject_id ON "views" ("subject_id");

The optimized query:

SELECT
        t4.id,
        t4.username,
        t4.avatar_url,
        p_count * 50 + ue_count * 2 + fav_count * 10 + ep_count * 2 + COUNT(vp.id) * 2 AS point 
    FROM
        (SELECT
            t3.id,
            t3.username,
            t3.avatar_url,
            p_count,
            ue_count,
            fav_count,
            COUNT(ep.id) AS ep_count 
        FROM
            (SELECT
                t2.id,
                t2.username,
                t2.avatar_url,
                p_count,
                ue_count,
                COUNT(fav_p.id) AS fav_count 
            FROM
                (SELECT
                    t1.id,
                    t1.username,
                    t1.avatar_url,
                    p_count,
                    COUNT(e.user_id) AS ue_count 
                FROM
                    (SELECT
                        u.*,
                        (SELECT
                            COUNT(p.user_id) AS p_count 
                        FROM
                            portfolios p 
                        WHERE
                            u.id = p.user_id LIMIT 1) AS p_count 
                    FROM
                        users u) t1 
                    LEFT OUTER JOIN
                        endorsements e 
                            ON e.user_id = t1.id 
                    GROUP BY
                        t1.id,
                        t1.username,
                        t1.avatar_url,
                        p_count) t2 
                LEFT OUTER JOIN
                    (
                        SELECT
                            p.id,
                            p.user_id 
                        FROM
                            portfolios p 
                        INNER JOIN
                            favorites 
                                ON favorites.subject_id = p.id
                        ) fav_p 
                            ON fav_p.user_id = t2.id 
                    GROUP BY
                        t2.id,
                        t2.username,
                        t2.avatar_url,
                        p_count,
                        ue_count) t3 
                    LEFT OUTER JOIN
                        (
                            SELECT
                                p.id,
                                p.user_id 
                            FROM
                                portfolios p 
                            INNER JOIN
                                endorsements 
                                    ON endorsements.portfolio_id = p.id
                            ) ep 
                                ON ep.user_id = t3.id 
                        GROUP BY
                            t3.id,
                            t3.username,
                            t3.avatar_url,
                            p_count,
                            ue_count,
                            fav_count) t4 
                        LEFT OUTER JOIN
                            (
                                SELECT
                                    p.id,
                                    p.user_id 
                                FROM
                                    portfolios p 
                                INNER JOIN
                                    views 
                                        ON views.subject_id = p.id
                                ) vp 
                                    ON vp.user_id = t4.id 
                            GROUP BY
                                t4.id,
                                t4.username,
                                t4.avatar_url,
                                p_count,
                                ue_count,
                                fav_count,
                                ep_count 
                            ORDER BY
                                point DESC LIMIT 8