The optimization process and recommendations:

1. 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.
2. Prefer Sorting/Grouping By The First Table In Join Order (modified query below): The database can use indexes more efficiently when sorting and grouping using columns from the first table in the join order. The first table is determined based on the prediction of the the optimal first table, and is not necessarily the first table shown in the FROM clause.

Optimal indexes for this query:

CREATE INDEX performance_points_idx_date ON "performance_points" ("date");
CREATE INDEX performance_points_idx_id_user_id ON "performance_points" ("id","user_id");
CREATE INDEX team_memberships_idx_team_id_user_id ON "team_memberships" ("team_id","user_id");
CREATE INDEX users_idx_id ON "users" ("id");

The optimized query:

SELECT
        u.id,
        u.slug,
        SUM(pp.points) AS total 
    FROM
        users u 
    JOIN
        performance_points pp 
            ON pp.user_id = u.id 
    JOIN
        team_memberships tm 
            ON tm.team_id = pp.team_id 
            AND tm.user_id = pp.user_id 
    WHERE
        (
            pp.date > '2015-08-02 13:57:14.042221'
        ) 
    GROUP BY
        pp.id,
        pp.user_id 
    ORDER BY
        total DESC LIMIT 50