Speed up slow postgres query with indices (~5 minutes) I have roughly 100 million rows in a table called 'bets' and a couple million in 'games'. The problem is that even with indices particular queries run very slow. The tables are structured as so:

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);


This is the query that runs slow:

-- 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;


This is how everything looks like:



Problematic index scan when running explain:

  {
    "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
  }
WHERE user_id = 1
ORDER BY game_id DESC



You probably need an index on bets (user_id, game_id).  That would let it tackle the above in a very efficient way, rather than the very inefficient it is doing it now:


"Filter": "(bets.user_id = 1)",
"Rows Removed by Filter": 106898896,



If that doesn't do it for you, please provide a complete text-formatted "explain (analyze, buffers)".  Neither of the ways you presented it is very effective.  Especially since we don't even know what tool generated the image one, and the two things seem to have come from different executions plans.