How to optimize SELECT query with multiple CASE statements?

Поиск
Список
Период
Сортировка
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.


Thank you
Alex

В списке pgsql-general по дате отправления:

Предыдущее
От: Kiran
Дата:
Сообщение: Re: Rows are repeating by the trigger function
Следующее
От: John DeSoi
Дата:
Сообщение: Re: Way to quickly detect if database tables/columns/etc. were modified?