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

On Mon, Oct 31, 2016 at 5:53 AM, 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?
>
> 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,
>
​[...]

When seeing the above repetition I consider implementing a composite type
and passing that around in the main portion of the queries and then
(composite_type).* at the presentation layer.

As Geoff​

​indicated normalization makes this a bit easier​; but you can still
normalize "on-the-fly" via standalone composite types.

David J.