Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 31 October 2016 at 12:53, Alexander Farber
<alexander.farber@xxxxxxxxx> 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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux