0

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:

enter image description here

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
  }
  • 2
    I suspect that you need to ANALYZE public.bets, but it's hard to tell unless you post EXPLAIN (ANALYZE, BUFFERS) output instead of the cute, but pretty useless image. – Laurenz Albe May 21 at 20:55
  • 1
    I think the LIMIT 50 in the subquery is redundant and misleading. The subquery cannot return more than 1 row, can it? The whole join can be converted to a simple LEFT JOIN games ON games.id = bets.game_id, with the same results – ypercubeᵀᴹ May 21 at 22:07
  • @ybercube, should not an inner join be sufficient (since there cannot exist a bet without a game)? – Lennart May 22 at 7:08
  • @Lennart, yes INNER and LEFT joins would be equivalent here. Postgres has an optimization (that may apply here) that works only with LEFT join though, so I'd prefer to use LEFT join. – ypercubeᵀᴹ May 22 at 8:48
1
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.