From 452063b01662bf421362fdd3efa3dfa9c8994996 Mon Sep 17 00:00:00 2001 From: Ant Zucaro Date: Tue, 16 Mar 2021 17:51:18 -0400 Subject: [PATCH] Add a script to create/renew recent game stats for performance. --- scripts/refresh_recent_game_stats_mv.sql | 32 ++++++++++++++++++++++++ 1 file changed, 32 insertions(+) create mode 100644 scripts/refresh_recent_game_stats_mv.sql diff --git a/scripts/refresh_recent_game_stats_mv.sql b/scripts/refresh_recent_game_stats_mv.sql new file mode 100644 index 0000000..8ab8211 --- /dev/null +++ b/scripts/refresh_recent_game_stats_mv.sql @@ -0,0 +1,32 @@ +BEGIN; + -- Populate the table using a different name. + CREATE TABLE recent_game_stats_mv_new AS + + SELECT + pgs.player_game_stat_id, g.game_id, g.server_id, g.map_id, p.player_id, p.nick, pgs.score, pgs.alivetime + + FROM player_game_stats pgs + INNER JOIN players p USING (player_id) + INNER JOIN games g USING (game_id) + + WHERE + p.player_id > 2 + AND p.active_ind = true + AND pgs.create_dt BETWEEN (now() at time zone 'UTC' - interval '30 days') AND (now() at time zone 'UTC' + interval '1 day') + AND g.create_dt BETWEEN (now() at time zone 'UTC' - interval '30 days') AND (now() at time zone 'UTC' + interval '1 day'); + + -- Index it + CREATE INDEX recent_game_stats_mv_new_ix001 on recent_game_stats(server_id); + CREATE INDEX recent_game_stats_mv_new_ix002 on recent_game_stats(map_id); + + -- Drop the old stuff, rename the stuff + DROP TABLE IF EXISTS recent_game_stats_mv CASCADE; + ALTER TABLE recent_game_stats_mv_new RENAME TO recent_game_stats_mv; + + DROP INDEX IF EXISTS recent_game_stats_mv_ix001; + ALTER INDEX recent_game_stats_mv_new_ix001 RENAME to recent_game_stats_mv_ix001; + + DROP INDEX IF EXISTS recent_game_stats_mv_ix002; + ALTER INDEX recent_game_stats_mv_new_ix002 RENAME to recent_game_stats_mv_ix002; + +COMMIT; \ No newline at end of file -- 2.39.2