How to optimize SELECT query with multiple CASE statements?

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: How to optimize SELECT query with multiple CASE statements?
Date: 2016-10-31 12:53:48
Message-ID: CAADeyWg1fezpVYPfvqM678bfJgp=5+t0VuR-MTbmRWAo-ndV_w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

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

Thank you
Alex

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John DeSoi 2016-10-31 12:54:36 Re: Way to quickly detect if database tables/columns/etc. were modified?
Previous Message Kiran 2016-10-31 09:06:50 Re: Rows are repeating by the trigger function