How to optimize SELECT query with multiple CASE statements?

Поиск
Список
Период
Сортировка
От Alexander Farber
Тема How to optimize SELECT query with multiple CASE statements?
Дата
Msg-id CAADeyWg1fezpVYPfvqM678bfJgp=5+t0VuR-MTbmRWAo-ndV_w@mail.gmail.com
обсуждение исходный текст
Ответы Re: How to optimize SELECT query with multiple CASE statements?  (Adrian Klaver <adrian.klaver@aklaver.com>)
Re: How to optimize SELECT query with multiple CASE statements?  (Geoff Winkless <pgsqladmin@geoff.dj>)
Re: How to optimize SELECT query with multiple CASE statements?  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список pgsql-general
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?