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
