How to optimize SELECT query with multiple CASE statements?

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Good afternoon,

is it please posible to optimize the following SQL query with numerous CASE statements (on same condition!) without switching to PL/pgSQL?

    SELECT
        g.gid,
        EXTRACT(EPOCH FROM g.created)::int,
        EXTRACT(EPOCH FROM g.finished)::int,
        g.letters,
        g.values,
        g.bid,
        m.tiles,
        m.score,
        /* HOW TO OPTIMIZE THE FOLLOWING CASE STATEMENTS? */
        CASE WHEN g.player1 = in_uid THEN g.player1 ELSE g.player2 END,
        CASE WHEN g.player1 = in_uid THEN g.player2 ELSE g.player1 END,
        EXTRACT(EPOCH FROM CASE WHEN g.player1 = in_uid THEN g.played1 ELSE g.played2 END)::int,
        EXTRACT(EPOCH FROM CASE WHEN g.player1 = in_uid THEN g.played2 ELSE g.played1 END)::int,
        CASE WHEN g.player1 = in_uid THEN g.score1 ELSE g.score2 END,
        CASE WHEN g.player1 = in_uid THEN g.score2 ELSE g.score1 END,
        ARRAY_TO_STRING(CASE WHEN g.player1 = in_uid THEN g.hand1 ELSE g.hand2 END, ''),
        REGEXP_REPLACE(ARRAY_TO_STRING(CASE WHEN g.player1 = in_uid THEN g.hand2 ELSE g.hand1 END, ''), '.', '?', 'g'),
        CASE WHEN g.player1 = in_uid THEN s1.female ELSE s2.female END,
        CASE WHEN g.player1 = in_uid THEN s2.female ELSE s1.female END,
        CASE WHEN g.player1 = in_uid THEN s1.given ELSE s2.given END,
        CASE WHEN g.player1 = in_uid THEN s2.given ELSE s1.given END,
        CASE WHEN g.player1 = in_uid THEN s1.photo ELSE s2.photo END,
        CASE WHEN g.player1 = in_uid THEN s2.photo ELSE s1.photo END,
        CASE WHEN g.player1 = in_uid THEN s1.place ELSE s2.place END,
        CASE WHEN g.player1 = in_uid THEN s2.place ELSE s1.place END
    FROM words_games g
        LEFT JOIN words_moves m ON m.gid = g.gid
                -- find move record with the most recent timestamp
        AND NOT EXISTS (SELECT 1
            FROM words_moves m2 WHERE m2.gid = m.gid
            AND m2.played > m.played)
    LEFT JOIN words_social s1 ON s1.uid = g.player1
                -- find social record with the most recent timestamp
        AND NOT EXISTS (SELECT 1
            FROM words_social s WHERE s1.uid = s.uid
            AND s.stamp > s1.stamp)
    LEFT JOIN words_social s2 ON s2.uid = g.player2
                -- find social record with the most recent timestamp
        AND NOT EXISTS (SELECT 1
            FROM words_social s WHERE s2.uid = s.uid
            AND s.stamp > s2.stamp)
    WHERE in_uid IN (g.player1, g.player2)
    AND (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP - INTERVAL '1 day');

It is a two-player, PostgreSQL-based game and in the statement above I am using the CASE-statements to ensure that always player1, given1, score1 columns are returned for the player in question.

Here is a bit more context: http://stackoverflow.com/questions/40342426/how-to-optimize-select-query-with-multiple-case-statements

Thank you

Alex