CREATE TABLE users(
  id     bigserial  PRIMARY KEY,
  uname  text       NOT NULL
);

-- a couple million rows
CREATE TABLE games(
  id     bigserial  PRIMARY KEY,
  ended  boolean    NOT NULL DEFAULT FALSE
);

-- around a hundred million rows
CREATE TABLE bets(
  id       bigserial  PRIMARY KEY,
  game_id  bigint     NOT NULL REFERENCES games(id),
  user_id  bigint     NOT NULL REFERENCES users(id),
  wager    float      NOT NULL
);
CREATE INDEX bets_user_id_idx ON bets(user_id, id);
CREATE INDEX bets_game_id_idx ON bets(game_id);

-- find the 50 most recent bets
SELECT bets.wager, g.ended
FROM bets
JOIN LATERAL (
    SELECT * FROM games
    WHERE id = bets.game_id
    ORDER BY id DESC
    LIMIT 50
) as g ON TRUE
WHERE user_id = 1
ORDER BY game_id DESC
LIMIT 50;

{
    "Node Type": "Index Scan",
    "Parent Relationship": "Outer",
    "Parallel Aware": false,
    "Scan Direction": "Backward",
    "Index Name": "bets_game_id_idx",
    "Relation Name": "bets",
    "Schema": "public",
    "Alias": "bets",
    "Startup Cost": 0.57,
    "Total Cost": 5077159.99,
    "Plan Rows": 12302,
    "Plan Width": 32,
    "Actual Startup Time": 5200.174,
    "Actual Total Time": 265417.040,
    "Actual Rows": 50,
    "Actual Loops": 1,
    "Output": ["bets.wager", "games.ended"],
    "Filter": "(bets.user_id = 1)",
    "Rows Removed by Filter": 106898896,
    "Shared Hit Blocks": 44257825,
    "Shared Read Blocks": 1566663,
    "Shared Dirtied Blocks": 1,
    "Shared Written Blocks": 117,
    "Local Hit Blocks": 0,
    "Local Read Blocks": 0,
    "Local Dirtied Blocks": 0,
    "Local Written Blocks": 0,
    "Temp Read Blocks": 0,
    "Temp Written Blocks": 0
  }