Good afternoon, for each visitor of my website I generate a JSON list of 30 top players ( https://slova.de/words/top.php ), who played in the past week, with their average scores and average time between moves.
With 5 seconds this query is taking quite a bit of time: https://explain.depesz.com/s/wMMV I have noticed the Seq Scan on words_moves (cost=0.00..81,448.79 rows=1,747 width=4) (actual time=0.443..161.673 rows=15,009 loops=30) Filter: (uid = u.uid) Rows Removed by Filter: 1494728 and added a: CREATE INDEX ON words_moves(uid); which has improved the query time to 800ms: https://explain.depesz.com/s/xgv1 However now I am not sure anymore, what else could be improved in my query: WITH last_week_moves AS ( SELECT m.gid, m.uid, m.played - LAG(m.played) OVER(PARTITION BY m.gid ORDER BY played) AS diff FROM words_moves m JOIN words_games g ON (m.gid = g.gid AND m.uid in (g.player1, g.player2)) -- only show players who where active in the last week WHERE m.played > CURRENT_TIMESTAMP - interval '1 week' ) SELECT u.uid, u.elo, (SELECT TO_CHAR(AVG(diff), 'HH24:MI') FROM last_week_moves WHERE uid = u.uid) AS avg_time, (SELECT ROUND(AVG(score), 1) FROM words_moves WHERE uid = u.uid) AS avg_score, s.given, s.photo FROM words_users u JOIN words_social s USING (uid) WHERE u.elo > 1500 -- take the most recent record from words_social AND NOT EXISTS (SELECT 1 FROM words_social x WHERE s.uid = x.uid AND x.stamp > s.stamp) -- only show players who where active in the last week AND EXISTS (SELECT 1 FROM last_week_moves m WHERE u.uid = m.uid AND m.diff IS NOT NULL) ORDER BY u.elo DESC LIMIT 30; I have tried changing to LEFT JOIN LATERAL as in - WITH last_week_moves AS ( SELECT m.gid, m.uid, m.played - LAG(m.played) OVER(PARTITION BY m.gid ORDER BY played) AS diff FROM words_moves m JOIN words_games g ON (m.gid = g.gid AND m.uid in (g.player1, g.player2)) -- only show players who where active in the last week WHERE m.played > CURRENT_TIMESTAMP - interval '1 week' ) SELECT u.uid, u.elo, (SELECT TO_CHAR(AVG(diff), 'HH24:MI') FROM last_week_moves WHERE uid = u.uid) AS avg_time, (SELECT ROUND(AVG(score), 1) FROM words_moves WHERE uid = u.uid) AS avg_score, s.given, s.photo FROM words_users u -- take the most recent record from words_social LEFT JOIN LATERAL (SELECT * FROM words_social AS s WHERE s.uid = u.uid ORDER BY s.stamp DESC LIMIT 1) AS s ON TRUE WHERE u.elo > 1500 -- only show players who where active in the last week AND EXISTS (SELECT 1 FROM last_week_moves m WHERE u.uid = m.uid AND m.diff IS NOT NULL) ORDER BY u.elo DESC LIMIT 30; But this has not helped much (please see https://explain.depesz.com/s/PrF or the same output below) : QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=89502.34..272686.22 rows=30 width=160) (actual time=215.796..842.446 rows=30 loops=1) CTE last_week_moves -> WindowAgg (cost=87308.08..87316.34 rows=367 width=32) (actual time=183.075..193.613 rows=33221 loops=1) -> Sort (cost=87308.08..87309.00 rows=367 width=16) (actual time=183.069..184.359 rows=33221 loops=1) Sort Key: m_1.gid, m_1.played Sort Method: quicksort Memory: 4132kB -> Gather (cost=13632.94..87292.45 rows=367 width=16) (actual time=37.204..172.827 rows=33221 loops=1) Workers Planned: 2 Workers Launched: 2 -> Hash Join (cost=12632.94..86255.75 rows=153 width=16) (actual time=36.183..168.676 rows=11074 loops=3) Hash Cond: (m_1.gid = g.gid) Join Filter: ((m_1.uid = g.player1) OR (m_1.uid = g.player2)) -> Parallel Seq Scan on words_moves m_1 (cost=0.00..73600.05 rows=8666 width=16) (actual time=0.761..130.844 rows=11074 loops=3) Filter: (played > (CURRENT_TIMESTAMP - '7 days'::interval)) Rows Removed by Filter: 492241 -> Hash (cost=12007.42..12007.42 rows=50042 width=12) (actual time=35.236..35.236 rows=50044 loops=3) Buckets: 65536 Batches: 1 Memory Usage: 2663kB -> Seq Scan on words_games g (cost=0.00..12007.42 rows=50042 width=12) (actual time=0.007..28.981 rows=50044 loops=3) -> Result (cost=2186.01..1278367.01 rows=209 width=160) (actual time=215.795..842.425 rows=30 loops=1) -> Sort (cost=2186.01..2186.53 rows=209 width=96) (actual time=205.069..205.087 rows=30 loops=1) Sort Key: u.elo DESC Sort Method: top-N heapsort Memory: 32kB -> Nested Loop Left Join (cost=12.19..2179.83 rows=209 width=96) (actual time=203.009..205.040 rows=110 loops=1) -> Hash Semi Join (cost=11.90..440.43 rows=209 width=8) (actual time=202.984..204.451 rows=110 loops=1) Hash Cond: (u.uid = m.uid) -> Seq Scan on words_users u (cost=0.00..415.96 rows=418 width=8) (actual time=0.005..1.422 rows=418 loops=1) Filter: (elo > 1500) Rows Removed by Filter: 10139 -> Hash (cost=7.34..7.34 rows=365 width=4) (actual time=202.975..202.975 rows=31549 loops=1) Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1366kB -> CTE Scan on last_week_moves m (cost=0.00..7.34 rows=365 width=4) (actual time=183.080..200.197 rows=31549 loops=1) Filter: (diff IS NOT NULL) Rows Removed by Filter: 1672 -> Limit (cost=0.29..8.30 rows=1 width=180) (actual time=0.005..0.005 rows=1 loops=110) -> Index Scan using words_social_uid_stamp_idx on words_social s (cost=0.29..8.30 rows=1 width=180) (actual time=0.005..0.005 rows=1 loops=110) Index Cond: (uid = u.uid) SubPlan 2 -> Aggregate (cost=8.27..8.28 rows=1 width=32) (actual time=1.838..1.838 rows=1 loops=30) -> CTE Scan on last_week_moves (cost=0.00..8.26 rows=2 width=16) (actual time=0.176..1.775 rows=403 loops=30) Filter: (uid = u.uid) Rows Removed by Filter: 32818 SubPlan 3 -> Aggregate (cost=6097.83..6097.84 rows=1 width=32) (actual time=19.401..19.401 rows=1 loops=30) -> Bitmap Heap Scan on words_moves (cost=33.97..6093.45 rows=1748 width=4) (actual time=1.680..18.153 rows=15011 loops=30) Recheck Cond: (uid = u.uid) Heap Blocks: exact=216312 -> Bitmap Index Scan on words_moves_uid_idx (cost=0.00..33.54 rows=1748 width=0) (actual time=0.979..0.979 rows=15011 loops=30) Index Cond: (uid = u.uid) Planning time: 0.508 ms Execution time: 843.322 ms (50 rows) Does anybody please have a suggestion, what else could be improved? Below are the tables from the above query and I use PostgreSQL 10.6 - words=> \d words_moves Table "public.words_moves" Column | Type | Collation | Nullable | Default ---------+--------------------------+-----------+----------+------------------------------------------ mid | bigint | | not null | nextval('words_moves_mid_seq'::regclass) action | text | | not null | gid | integer | | not null | uid | integer | | not null | played | timestamp with time zone | | not null | tiles | jsonb | | | score | integer | | | letters | text | | | hand | text | | | puzzle | boolean | | not null | false Indexes: "words_moves_pkey" PRIMARY KEY, btree (mid) "words_moves_gid_played_idx" btree (gid, played DESC) "words_moves_uid_idx" btree (uid) Check constraints: "words_moves_score_check" CHECK (score >= 0) Foreign-key constraints: "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE Referenced by: TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid) ON DELETE CASCADE words=> \d words_users Table "public.words_users" Column | Type | Collation | Nullable | Default ---------------+--------------------------+-----------+----------+------------------------------------------ uid | integer | | not null | nextval('words_users_uid_seq'::regclass) created | timestamp with time zone | | not null | visited | timestamp with time zone | | not null | ip | inet | | not null | fcm | text | | | apns | text | | | adm | text | | | motto | text | | | vip_until | timestamp with time zone | | | grand_until | timestamp with time zone | | | banned_until | timestamp with time zone | | | banned_reason | text | | | elo | integer | | not null | medals | integer | | not null | coins | integer | | not null | Indexes: "words_users_pkey" PRIMARY KEY, btree (uid) Check constraints: "words_users_banned_reason_check" CHECK (length(banned_reason) > 0) "words_users_elo_check" CHECK (elo >= 0) "words_users_medals_check" CHECK (medals >= 0) Referenced by: TABLE "words_chat" CONSTRAINT "words_chat_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_games" CONSTRAINT "words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_games" CONSTRAINT "words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_moves" CONSTRAINT "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_reviews" CONSTRAINT "words_reviews_author_fkey" FOREIGN KEY (author) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_reviews" CONSTRAINT "words_reviews_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_scores" CONSTRAINT "words_scores_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_social" CONSTRAINT "words_social_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_stats" CONSTRAINT "words_stats_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE words=> \d words_games Table "public.words_games" Column | Type | Collation | Nullable | Default ----------+--------------------------+-----------+----------+------------------------------------------ gid | integer | | not null | nextval('words_games_gid_seq'::regclass) created | timestamp with time zone | | not null | finished | timestamp with time zone | | | player1 | integer | | not null | player2 | integer | | | played1 | timestamp with time zone | | | played2 | timestamp with time zone | | | state1 | text | | | state2 | text | | | reason | text | | | hint1 | text | | | hint2 | text | | | score1 | integer | | not null | score2 | integer | | not null | chat1 | integer | | not null | chat2 | integer | | not null | hand1 | character(1)[] | | not null | hand2 | character(1)[] | | not null | pile | character(1)[] | | not null | letters | character(1)[] | | not null | values | integer[] | | not null | bid | integer | | not null | friendly | boolean | | | Indexes: "words_games_pkey" PRIMARY KEY, btree (gid) "words_games_player1_coalesce_idx" btree (player1, COALESCE(finished, 'infinity'::timestamp with time zone)) "words_games_player2_coalesce_idx" btree (player2, COALESCE(finished, 'infinity'::timestamp with time zone)) Check constraints: "words_games_chat1_check" CHECK (chat1 >= 0) "words_games_chat2_check" CHECK (chat2 >= 0) "words_games_check" CHECK (player1 <> player2) "words_games_score1_check" CHECK (score1 >= 0) "words_games_score2_check" CHECK (score2 >= 0) Foreign-key constraints: "words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid) ON DELETE CASCADE "words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES words_users(uid) ON DELETE CASCADE "words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES words_users(uid) ON DELETE CASCADE Referenced by: TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE Thank you Alex
