Reducing number of records in CTE table

Question

In PostgreSQL 9.5.4 I keep player infos from various social networks:

# TABLE words_social;

  sid  | social | female |  given  | family | photo | place |   stamp    | uid
-------+--------+--------+---------+--------+-------+-------+------------+-----
 aaaaa |      1 |      0 | Abcde1  |        |       |       | 1470237061 |   1
 aaaaa |      2 |      0 | Abcde2  |        |       |       | 1477053188 |   1
 aaaaa |      3 |      0 | Abcde3  |        |       |       | 1477053330 |   1
 kkkkk |      3 |      0 | Klmnop3 |        |       |       | 1477053810 |   2
 kkkkk |      4 |      0 | Klmnop4 |        |       |       | 1477053857 |   2
 ggggg |      2 |      0 | Ghijk2  |        |       |       | 1477053456 |   3
 ggggg |      3 |      0 | Ghijk3  |        |       |       | 1477053645 |   3
 ggggg |      4 |      0 | Ghijk4  |        |       |       | 1477053670 |   3
 xxxxx |      4 |      0 | Xyzok   |        |       |       | 1470237393 |   4
(9 rows)

The 1, 2, 3, 4 values in column social mean "Facebook", "Twitter", etc.

For a player I can always select her most recent info by:

# select * from words_social s1 WHERE stamp = 
      (SELECT max(stamp) FROM words_social s2 WHERE s1.uid = s2.uid);

  sid  | social | female |  given  | family | photo | place |   stamp    | uid
-------+--------+--------+---------+--------+-------+-------+------------+-----
 aaaaa |      3 |      0 | Abcde3  |        |       |       | 1477053330 |   1
 kkkkk |      4 |      0 | Klmnop4 |        |       |       | 1477053857 |   2
 ggggg |      4 |      0 | Ghijk4  |        |       |       | 1477053670 |   3
 xxxxx |      4 |      0 | Xyzok   |        |       |       | 1470237393 |   4
(4 rows)

Then there is another table storing current games (I have omitted some columns below):

# select gid, created, finished, player1, player2 from words_games;

 gid |            created            | finished | player1 | player2
-----+-------------------------------+----------+---------+---------
   1 | 2016-10-21 14:51:12.624507+02 |          |       4 |       1
   2 | 2016-10-21 14:51:22.631507+02 |          |       3 |
(2 rows)

Whenever a user (for example with uid 1) connects to the server, I send her the games she is taking part in:

# select       gid, created, finished, player1, player2 from words_games where player1 = 1
  union select gid, created, finished, player2, player1 from words_games where player2 = 1;

 gid |            created            | finished | player1 | player2
-----+-------------------------------+----------+---------+---------
   1 | 2016-10-21 14:51:12.624507+02 |          |       4 |       1
(1 row)

My problem: to the above UNION SELECT statement I need to add user infos from words_social table - so that I can display user photos and names above the game board in my 2-player game.

So I try this with CTE (and add the i.given column with the user first name):

# with user_infos AS (select * from words_social s1 WHERE stamp =
      (SELECT max(stamp) FROM words_social s2 WHERE s1.uid = s2.uid))
 select       g.gid, g.created, g.finished, g.player1, g.player2, i.given from words_games g join user_infos i on (g.player1=i.uid) where g.player1 = 1
 union select g.gid, g.created, g.finished, g.player2, g.player1, i.given from words_games g join user_infos i on (g.player2=i.uid) where g.player2 = 1;

 gid |            created            | finished | player1 | player2 | given
-----+-------------------------------+----------+---------+---------+--------
   1 | 2016-10-21 14:51:12.624507+02 |          |       1 |       4 | Abcde3
(1 row)

This works well, but I still have the following problem -

I am worried that the CTE-table user_infos will get very large, once my game has many players.

How to rewrite the query, so that user_infos only holds relevant records?

I can not just perform

# with user_infos AS (select * from words_social s1 WHERE stamp =
      (SELECT max(stamp) FROM words_social s2 WHERE s1.uid = s2.uid))
      AND s1.uid = 1
      ...

because I also need the infos (given and family names, photo) of the game opponents.

Answer 1

user_infos query can be rewritten and used as following:

with user_infos as (
  select row_number() over (partition by uid order by stamp desc), * from words_social
)
select g.gid, g.created, g.finished, g.player1, g.player2, i.given from words_games g 
join user_infos i on g.player1=i.uid and i.row_number = 1 and g.player1 = 1 
union select g.gid, g.created, g.finished, g.player2, g.player1, i.given from words_games g
join user_infos i on g.player2=i.uid and i.row_number =1 and g.player2 = 1;

Answer 2

You should wrap it the other way.

Start from the word_games, then make your join with the words_social table.

Also you could use dinstinct on (postgres specific) function to avoid a second table lookup.

So in the end:

with game_finder as (
 select g.gid, g.player1, g.player2
 from words_games g where g.player1 = 1
 union 
 select g.gid,g.player2, g.player1
 from words_games g where g.player2 = 1),
player1_infos as (
 select distinct on (uid) 
  gf.gid,
  uid,
  social,
  given
 from words_social ws
 inner join game_finder gf on gf.player1 = ws.uid 
 ORDER BY uid, stamp DESC
),
player2_infos as (
 select gf.gid,
  uid,
  social,
  given
 from words_social ws
 inner join game_finder gf on gf.player2 = ws.uid 
 ORDER BY uid, stamp DESC
)
select *
from game_finder gf
left outer join player1_infos p1 on gf.gid = p1.gid
left outer join player2_infos p2 on gf.gid = p2.gid;