Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement