SHARE
TWEET

Untitled

a guest Jun 18th, 2019 53 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE users(
  2.   id     bigserial  PRIMARY KEY,
  3.   uname  text       NOT NULL
  4. );
  5.  
  6. -- a couple million rows
  7. CREATE TABLE games(
  8.   id     bigserial  PRIMARY KEY,
  9.   ended  boolean    NOT NULL DEFAULT FALSE
  10. );
  11.  
  12. -- around a hundred million rows
  13. CREATE TABLE bets(
  14.   id       bigserial  PRIMARY KEY,
  15.   game_id  bigint     NOT NULL REFERENCES games(id),
  16.   user_id  bigint     NOT NULL REFERENCES users(id),
  17.   wager    float      NOT NULL
  18. );
  19. CREATE INDEX bets_user_id_idx ON bets(user_id, id);
  20. CREATE INDEX bets_game_id_idx ON bets(game_id);
  21.      
  22. -- find the 50 most recent bets
  23. SELECT bets.wager, g.ended
  24. FROM bets
  25. JOIN LATERAL (
  26.     SELECT * FROM games
  27.     WHERE id = bets.game_id
  28.     ORDER BY id DESC
  29.     LIMIT 50
  30. ) as g ON TRUE
  31. WHERE user_id = 1
  32. ORDER BY game_id DESC
  33. LIMIT 50;
  34.      
  35. {
  36.     "Node Type": "Index Scan",
  37.     "Parent Relationship": "Outer",
  38.     "Parallel Aware": false,
  39.     "Scan Direction": "Backward",
  40.     "Index Name": "bets_game_id_idx",
  41.     "Relation Name": "bets",
  42.     "Schema": "public",
  43.     "Alias": "bets",
  44.     "Startup Cost": 0.57,
  45.     "Total Cost": 5077159.99,
  46.     "Plan Rows": 12302,
  47.     "Plan Width": 32,
  48.     "Actual Startup Time": 5200.174,
  49.     "Actual Total Time": 265417.040,
  50.     "Actual Rows": 50,
  51.     "Actual Loops": 1,
  52.     "Output": ["bets.wager", "games.ended"],
  53.     "Filter": "(bets.user_id = 1)",
  54.     "Rows Removed by Filter": 106898896,
  55.     "Shared Hit Blocks": 44257825,
  56.     "Shared Read Blocks": 1566663,
  57.     "Shared Dirtied Blocks": 1,
  58.     "Shared Written Blocks": 117,
  59.     "Local Hit Blocks": 0,
  60.     "Local Read Blocks": 0,
  61.     "Local Dirtied Blocks": 0,
  62.     "Local Written Blocks": 0,
  63.     "Temp Read Blocks": 0,
  64.     "Temp Written Blocks": 0
  65.   }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top