Re: How to optimize SELECT query with multiple CASE statements?

On 31 October 2016 at 12:53, Alexander Farber
<alexander(dot)farber(at)gmail(dot)com> wrote:
>
> Good afternoon,
>
> is it please posible to optimize the following SQL query with numerous CASE statements (on same condition!) without switching to PL/pgSQL?

You could break the game table apart into game and gameplayer.

That's more "normal" and fits much more nicely, IMO, and you could
then resolve the CASE by using joins between game and (twice)
gameplayer:

SELECT ...
FROM game INNER JOIN gameplayer AS myplayer ON
game.gameid=myplayer.gameid AND myplayer.uid=in_uid
INNER JOIN gameplayer AS otherplayer ON game.gameid=otherplayer.gameid
AND otherplayer.uid!=in_uid
...

Then all the other tables simply join to myplayer and otherplayer.

If you have to stick with the designed schema, you could probably do
something with arrays, but I doubt if that would get any less messy
and certainly no more readable.

FWIW you can resolve half of the CASEs by resolving it in the join to
s1 and s2 - so

LEFT JOIN words_social s1 ON s1.uid = in_uid
LEFT JOIN words_social s2 ON CASE WHEN g.player1 = in_uid THEN
g.player2 ELSE g.player1

etc

Geoff