Thread: Not sure which part of the query needs optimization
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 youfor 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
>>>>> "Alexander" == Alexander Farber <alexander.farber@gmail.com> writes: Alexander> Good afternoon, Alexander> for each visitor of my website I generate a JSON list of 30 Alexander> top players ( https://slova.de/words/top.php ), who played Alexander> in the past week, with their average scores and average time Alexander> between moves. -> 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 This is telling you that an index on words_moves(played) would likely help. But the real hot point in the query is here (reformatted for clarity): -> 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) (the explain.depesz.com view points this out with an orange highlight) This corresponds to this subquery: (SELECT ROUND(AVG(score), 1) FROM words_moves WHERE uid = u.uid) AS avg_score, The basic issue here is that you're calculating an average over around 15k rows per user, for each user in the query result (so 30 times, making 450k rows). You can see from the "Heap Blocks" stat that this is having to scan a lot of data; it's taking on average 19.4ms per user, but multiply that by 30 users and you get ~580ms total, or about 70% of the total execution time. The obvious thing to do is to keep a computed average score for each user - either in a separate table which you update based on changes to words_moves, which you could do with a trigger, or using a materialized view which you refresh at suitable intervals (this has the drawback that the data will not be immediately up-to-date). Combining these two changes should get you to under 100ms, maybe. -- Andrew (irc:RhodiumToad)
Thank you for the comments, Andrew -
On Mon, Jan 7, 2019 at 8:40 PM Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
The obvious thing to do is to keep a computed average score for each
user - either in a separate table which you update based on changes to
words_moves, which you could do with a trigger, or using a materialized
view which you refresh at suitable intervals (this has the drawback that
the data will not be immediately up-to-date).
Combining these two changes should get you to under 100ms, maybe.
I was considering creating a cronjob, but now I will better create a trigger as you have suggested
Regards
Alex