Good evening, thank you for the useful hints!
With the further help of the IRC folks the query has been optimized (7-10 seconds -> 0.3 second) by adding the following indices:
CREATE INDEX ON words_games (player1, COALESCE(finished, 'INFINITY'));
CREATE INDEX ON words_games (player2, COALESCE(finished, 'INFINITY'));
CREATE INDEX ON words_moves (gid, played DESC);
CREATE INDEX ON words_social (uid, stamp DESC);
CREATE INDEX ON words_geoip USING SPGIST (block);
and switching to LEFT JOIN LATERAL for finding the most recent records in words_moves and words_social tables:
SELECT
...
-- find move record with the most recent timestamp
LEFT JOIN LATERAL (SELECT * FROM words_moves m WHERE m.gid = g.gid ORDER BY m.played DESC LIMIT 1) AS m ON TRUE
LEFT JOIN words_geoip i2 ON (CASE WHEN g.player1 = in_uid THEN u2.ip ELSE u1.ip END) <<= i2.block
LEFT JOIN LATERAL (SELECT * FROM words_social AS s1 WHERE s1.uid = u1.uid ORDER BY s1.stamp DESC LIMIT 1) AS s1 ON TRUE
LEFT JOIN LATERAL (SELECT * FROM words_social AS s2 WHERE s2.uid = u2.uid ORDER BY s2.stamp DESC LIMIT 1) AS s2 ON TRUE
...
WHERE in_uid IN (g.player1, g.player2)
AND COALESCE(g.finished, 'INFINITY') > (CURRENT_TIMESTAMP - INTERVAL '1 day')
Also I have increased the following parameters in postgresql.conf -
from_collapse_limit = 24
join_collapse_limit = 24
Now the whole query looks as following and the EXPLAIN output pasted is below -
Query - http://dpaste.com/1AQM800
Explain - https://explain.depesz.com/s/pq79
EXPLAIN ANALYZE SELECT
g.gid,
EXTRACT(EPOCH FROM g.created)::int AS created,
EXTRACT(EPOCH FROM g.finished)::int AS finished,
g.letters AS letters,
g.values AS values,
g.bid AS bid,
CARDINALITY(g.pile) AS pilelen,
m.tiles AS tiles,
m.score AS score,
CASE WHEN g.player1 = 5 THEN g.player1 ELSE g.player2 END AS player1,
CASE WHEN g.player1 = 5 THEN g.player2 ELSE g.player1 END AS player2,
CASE WHEN g.player1 = 5 THEN g.score1 ELSE g.score2 END AS score1,
CASE WHEN g.player1 = 5 THEN g.score2 ELSE g.score1 END AS score2,
CASE WHEN g.player1 = 5 THEN g.state1 ELSE g.state2 END AS state1,
CASE WHEN g.player1 = 5 THEN g.hint1 ELSE g.hint2 END AS hint1,
CASE WHEN g.player1 = 5 THEN g.chat1 ELSE g.chat2 END AS chat1,
u1.elo AS elo1,
u2.elo AS elo2,
i2.lat AS lat2,
i2.lng AS lng2,
s1.given AS given1,
s2.given AS given2,
s1.photo AS photo1,
s2.photo AS photo2,
EXTRACT(EPOCH FROM CASE WHEN g.player1 = 5 THEN g.played1 ELSE g.played2 END)::int AS played1,
EXTRACT(EPOCH FROM CASE WHEN g.player1 = 5 THEN g.played2 ELSE g.played1 END)::int AS played2,
ARRAY_TO_STRING(CASE WHEN g.player1 = 5 THEN g.hand1 ELSE g.hand2 END, '') AS hand1,
CASE
WHEN g.finished IS NOT NULL THEN NULL
WHEN g.player2 IS NULL THEN NULL
WHEN g.player1 = 5 AND g.played1 < g.played2
THEN EXTRACT(EPOCH FROM g.played2 + interval '24 hour' - CURRENT_TIMESTAMP)::int
WHEN g.player2 = 5 AND (g.played2 IS NULL OR g.played2 < g.played1)
THEN EXTRACT(EPOCH FROM g.played1 + interval '24 hour' - CURRENT_TIMESTAMP)::int
ELSE NULL
END AS left1,
CASE
WHEN g.finished IS NOT NULL THEN NULL
WHEN g.player2 IS NULL THEN NULL
WHEN g.player1 = 5 AND (g.played2 IS NULL OR g.played2 < g.played1)
THEN EXTRACT(EPOCH FROM g.played1 + interval '24 hour' - CURRENT_TIMESTAMP)::int
WHEN g.player2 = 5 AND g.played1 < g.played2
THEN EXTRACT(EPOCH FROM g.played2 + interval '24 hour' - CURRENT_TIMESTAMP)::int
ELSE NULL
END AS left2
FROM words_games g
LEFT JOIN LATERAL (select tiles, score from words_moves m where m.gid = g.gid order by m.played desc limit 1) as m on true
LEFT JOIN words_users u1 ON u1.uid = 5
LEFT JOIN words_users u2 ON u2.uid = (CASE WHEN g.player1 = 5 THEN g.player2 ELSE g.player1 END)
LEFT JOIN words_geoip i2 ON (CASE WHEN g.player1 = 5 THEN u2.ip ELSE u1.ip END) <<= i2.block
LEFT JOIN LATERAL (select * from words_social as s1 where s1.uid = u1.uid order by s1.stamp desc limit 1) as s1 on true
LEFT JOIN LATERAL (select * from words_social as s2 where s2.uid = u2.uid order by s2.stamp desc limit 1) as s2 on true
WHERE 5 IN (g.player1, g.player2)
AND COALESCE(g.finished,'infinity') > (CURRENT_TIMESTAMP - INTERVAL '1 day')
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=514.37..18613546.07 rows=1029592 width=1430) (actual time=0.095..0.287 rows=8 loops=1)
-> Nested Loop Left Join (cost=514.09..9921215.61 rows=1029592 width=1780) (actual time=0.086..0.247 rows=8 loops=1)
-> Nested Loop Left Join (cost=513.80..1352436.19 rows=1029592 width=1696) (actual time=0.081..0.229 rows=8 loops=1)
-> Nested Loop Left Join (cost=10.29..925.20 rows=67 width=1694) (actual time=0.038..0.111 rows=8 loops=1)
-> Nested Loop Left Join (cost=10.01..476.76 rows=67 width=1679) (actual time=0.035..0.090 rows=8 loops=1)
-> Nested Loop Left Join (cost=9.72..467.62 rows=67 width=1664) (actual time=0.030..0.081 rows=8 loops=1)
-> Bitmap Heap Scan on words_games g (cost=9.29..263.40 rows=67 width=1456) (actual time=0.020..0.038 rows=8 loops=1)
Recheck Cond: (((5 = player1) AND (COALESCE(finished, 'infinity'::timestamp with time zone) > (CURRENT_TIMESTAMP - '1 day'::interval))) OR ((5 = player2) AND (COALESCE(finished, 'infinity'::timestamp with time zone) > (CURRENT_TIMESTAMP - '1 day'::interval))))
Filter: (COALESCE(finished, 'infinity'::timestamp with time zone) > (CURRENT_TIMESTAMP - '1 day'::interval))
Heap Blocks: exact=8
-> BitmapOr (cost=9.29..9.29 rows=67 width=0) (actual time=0.014..0.014 rows=0 loops=1)
-> Bitmap Index Scan on words_games_player1_coalesce_idx (cost=0.00..4.33 rows=4 width=0) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: ((5 = player1) AND (COALESCE(finished, 'infinity'::timestamp with time zone) > (CURRENT_TIMESTAMP - '1 day'::interval)))
-> Bitmap Index Scan on words_games_player2_coalesce_idx (cost=0.00..4.92 rows=63 width=0) (actual time=0.006..0.006 rows=7 loops=1)
Index Cond: ((5 = player2) AND (COALESCE(finished, 'infinity'::timestamp with time zone) > (CURRENT_TIMESTAMP - '1 day'::interval)))
-> Limit (cost=0.43..3.03 rows=1 width=216) (actual time=0.004..0.005 rows=1 loops=8)
-> Index Scan using words_moves_gid_played_idx on words_moves m (cost=0.43..107.05 rows=41 width=216) (actual time=0.004..0.004 rows=1 loops=8)
Index Cond: (gid = g.gid)
-> Materialize (cost=0.29..8.31 rows=1 width=15) (actual time=0.001..0.001 rows=1 loops=8)
-> Index Scan using words_users_pkey on words_users u1 (cost=0.29..8.30 rows=1 width=15) (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: (uid = 5)
-> Index Scan using words_users_pkey on words_users u2 (cost=0.29..6.69 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=8)
Index Cond: (uid = CASE WHEN (g.player1 = 5) THEN g.player2 ELSE g.player1 END)
-> Bitmap Heap Scan on words_geoip i2 (cost=503.51..20018.14 rows=15367 width=23) (actual time=0.014..0.014 rows=1 loops=8)
Recheck Cond: (CASE WHEN (g.player1 = 5) THEN u2.ip ELSE u1.ip END <<= block)
Heap Blocks: exact=7
-> Bitmap Index Scan on words_geoip_block_idx (cost=0.00..499.67 rows=15367 width=0) (actual time=0.013..0.013 rows=1 loops=8)
Index Cond: (CASE WHEN (g.player1 = 5) THEN u2.ip ELSE u1.ip END <<= block)
-> Limit (cost=0.29..8.30 rows=1 width=180) (actual time=0.002..0.002 rows=1 loops=8)
-> Index Scan using words_social_uid_stamp_idx on words_social s1 (cost=0.29..8.30 rows=1 width=180) (actual time=0.002..0.002 rows=1 loops=8)
Index Cond: (uid = u1.uid)
-> Limit (cost=0.29..8.30 rows=1 width=180) (actual time=0.002..0.002 rows=1 loops=8)
-> Index Scan using words_social_uid_stamp_idx on words_social s2 (cost=0.29..8.30 rows=1 width=180) (actual time=0.002..0.002 rows=1 loops=8)
Index Cond: (uid = u2.uid)
Planning time: 0.587 ms
Execution time: 0.367 ms
(36 rows)
I was told that it still could be improved (by rearranging WHERE clauses?)
Regards
Alex
With the further help of the IRC folks the query has been optimized (7-10 seconds -> 0.3 second) by adding the following indices:
CREATE INDEX ON words_games (player1, COALESCE(finished, 'INFINITY'));
CREATE INDEX ON words_games (player2, COALESCE(finished, 'INFINITY'));
CREATE INDEX ON words_moves (gid, played DESC);
CREATE INDEX ON words_social (uid, stamp DESC);
CREATE INDEX ON words_geoip USING SPGIST (block);
and switching to LEFT JOIN LATERAL for finding the most recent records in words_moves and words_social tables:
SELECT
...
-- find move record with the most recent timestamp
LEFT JOIN LATERAL (SELECT * FROM words_moves m WHERE m.gid = g.gid ORDER BY m.played DESC LIMIT 1) AS m ON TRUE
LEFT JOIN words_geoip i2 ON (CASE WHEN g.player1 = in_uid THEN u2.ip ELSE u1.ip END) <<= i2.block
LEFT JOIN LATERAL (SELECT * FROM words_social AS s1 WHERE s1.uid = u1.uid ORDER BY s1.stamp DESC LIMIT 1) AS s1 ON TRUE
LEFT JOIN LATERAL (SELECT * FROM words_social AS s2 WHERE s2.uid = u2.uid ORDER BY s2.stamp DESC LIMIT 1) AS s2 ON TRUE
...
WHERE in_uid IN (g.player1, g.player2)
AND COALESCE(g.finished, 'INFINITY') > (CURRENT_TIMESTAMP - INTERVAL '1 day')
Also I have increased the following parameters in postgresql.conf -
from_collapse_limit = 24
join_collapse_limit = 24
Now the whole query looks as following and the EXPLAIN output pasted is below -
Query - http://dpaste.com/1AQM800
Explain - https://explain.depesz.com/s/pq79
EXPLAIN ANALYZE SELECT
g.gid,
EXTRACT(EPOCH FROM g.created)::int AS created,
EXTRACT(EPOCH FROM g.finished)::int AS finished,
g.letters AS letters,
g.values AS values,
g.bid AS bid,
CARDINALITY(g.pile) AS pilelen,
m.tiles AS tiles,
m.score AS score,
CASE WHEN g.player1 = 5 THEN g.player1 ELSE g.player2 END AS player1,
CASE WHEN g.player1 = 5 THEN g.player2 ELSE g.player1 END AS player2,
CASE WHEN g.player1 = 5 THEN g.score1 ELSE g.score2 END AS score1,
CASE WHEN g.player1 = 5 THEN g.score2 ELSE g.score1 END AS score2,
CASE WHEN g.player1 = 5 THEN g.state1 ELSE g.state2 END AS state1,
CASE WHEN g.player1 = 5 THEN g.hint1 ELSE g.hint2 END AS hint1,
CASE WHEN g.player1 = 5 THEN g.chat1 ELSE g.chat2 END AS chat1,
u1.elo AS elo1,
u2.elo AS elo2,
i2.lat AS lat2,
i2.lng AS lng2,
s1.given AS given1,
s2.given AS given2,
s1.photo AS photo1,
s2.photo AS photo2,
EXTRACT(EPOCH FROM CASE WHEN g.player1 = 5 THEN g.played1 ELSE g.played2 END)::int AS played1,
EXTRACT(EPOCH FROM CASE WHEN g.player1 = 5 THEN g.played2 ELSE g.played1 END)::int AS played2,
ARRAY_TO_STRING(CASE WHEN g.player1 = 5 THEN g.hand1 ELSE g.hand2 END, '') AS hand1,
CASE
WHEN g.finished IS NOT NULL THEN NULL
WHEN g.player2 IS NULL THEN NULL
WHEN g.player1 = 5 AND g.played1 < g.played2
THEN EXTRACT(EPOCH FROM g.played2 + interval '24 hour' - CURRENT_TIMESTAMP)::int
WHEN g.player2 = 5 AND (g.played2 IS NULL OR g.played2 < g.played1)
THEN EXTRACT(EPOCH FROM g.played1 + interval '24 hour' - CURRENT_TIMESTAMP)::int
ELSE NULL
END AS left1,
CASE
WHEN g.finished IS NOT NULL THEN NULL
WHEN g.player2 IS NULL THEN NULL
WHEN g.player1 = 5 AND (g.played2 IS NULL OR g.played2 < g.played1)
THEN EXTRACT(EPOCH FROM g.played1 + interval '24 hour' - CURRENT_TIMESTAMP)::int
WHEN g.player2 = 5 AND g.played1 < g.played2
THEN EXTRACT(EPOCH FROM g.played2 + interval '24 hour' - CURRENT_TIMESTAMP)::int
ELSE NULL
END AS left2
FROM words_games g
LEFT JOIN LATERAL (select tiles, score from words_moves m where m.gid = g.gid order by m.played desc limit 1) as m on true
LEFT JOIN words_users u1 ON u1.uid = 5
LEFT JOIN words_users u2 ON u2.uid = (CASE WHEN g.player1 = 5 THEN g.player2 ELSE g.player1 END)
LEFT JOIN words_geoip i2 ON (CASE WHEN g.player1 = 5 THEN u2.ip ELSE u1.ip END) <<= i2.block
LEFT JOIN LATERAL (select * from words_social as s1 where s1.uid = u1.uid order by s1.stamp desc limit 1) as s1 on true
LEFT JOIN LATERAL (select * from words_social as s2 where s2.uid = u2.uid order by s2.stamp desc limit 1) as s2 on true
WHERE 5 IN (g.player1, g.player2)
AND COALESCE(g.finished,'infinity') > (CURRENT_TIMESTAMP - INTERVAL '1 day')
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=514.37..18613546.07 rows=1029592 width=1430) (actual time=0.095..0.287 rows=8 loops=1)
-> Nested Loop Left Join (cost=514.09..9921215.61 rows=1029592 width=1780) (actual time=0.086..0.247 rows=8 loops=1)
-> Nested Loop Left Join (cost=513.80..1352436.19 rows=1029592 width=1696) (actual time=0.081..0.229 rows=8 loops=1)
-> Nested Loop Left Join (cost=10.29..925.20 rows=67 width=1694) (actual time=0.038..0.111 rows=8 loops=1)
-> Nested Loop Left Join (cost=10.01..476.76 rows=67 width=1679) (actual time=0.035..0.090 rows=8 loops=1)
-> Nested Loop Left Join (cost=9.72..467.62 rows=67 width=1664) (actual time=0.030..0.081 rows=8 loops=1)
-> Bitmap Heap Scan on words_games g (cost=9.29..263.40 rows=67 width=1456) (actual time=0.020..0.038 rows=8 loops=1)
Recheck Cond: (((5 = player1) AND (COALESCE(finished, 'infinity'::timestamp with time zone) > (CURRENT_TIMESTAMP - '1 day'::interval))) OR ((5 = player2) AND (COALESCE(finished, 'infinity'::timestamp with time zone) > (CURRENT_TIMESTAMP - '1 day'::interval))))
Filter: (COALESCE(finished, 'infinity'::timestamp with time zone) > (CURRENT_TIMESTAMP - '1 day'::interval))
Heap Blocks: exact=8
-> BitmapOr (cost=9.29..9.29 rows=67 width=0) (actual time=0.014..0.014 rows=0 loops=1)
-> Bitmap Index Scan on words_games_player1_coalesce_idx (cost=0.00..4.33 rows=4 width=0) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: ((5 = player1) AND (COALESCE(finished, 'infinity'::timestamp with time zone) > (CURRENT_TIMESTAMP - '1 day'::interval)))
-> Bitmap Index Scan on words_games_player2_coalesce_idx (cost=0.00..4.92 rows=63 width=0) (actual time=0.006..0.006 rows=7 loops=1)
Index Cond: ((5 = player2) AND (COALESCE(finished, 'infinity'::timestamp with time zone) > (CURRENT_TIMESTAMP - '1 day'::interval)))
-> Limit (cost=0.43..3.03 rows=1 width=216) (actual time=0.004..0.005 rows=1 loops=8)
-> Index Scan using words_moves_gid_played_idx on words_moves m (cost=0.43..107.05 rows=41 width=216) (actual time=0.004..0.004 rows=1 loops=8)
Index Cond: (gid = g.gid)
-> Materialize (cost=0.29..8.31 rows=1 width=15) (actual time=0.001..0.001 rows=1 loops=8)
-> Index Scan using words_users_pkey on words_users u1 (cost=0.29..8.30 rows=1 width=15) (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: (uid = 5)
-> Index Scan using words_users_pkey on words_users u2 (cost=0.29..6.69 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=8)
Index Cond: (uid = CASE WHEN (g.player1 = 5) THEN g.player2 ELSE g.player1 END)
-> Bitmap Heap Scan on words_geoip i2 (cost=503.51..20018.14 rows=15367 width=23) (actual time=0.014..0.014 rows=1 loops=8)
Recheck Cond: (CASE WHEN (g.player1 = 5) THEN u2.ip ELSE u1.ip END <<= block)
Heap Blocks: exact=7
-> Bitmap Index Scan on words_geoip_block_idx (cost=0.00..499.67 rows=15367 width=0) (actual time=0.013..0.013 rows=1 loops=8)
Index Cond: (CASE WHEN (g.player1 = 5) THEN u2.ip ELSE u1.ip END <<= block)
-> Limit (cost=0.29..8.30 rows=1 width=180) (actual time=0.002..0.002 rows=1 loops=8)
-> Index Scan using words_social_uid_stamp_idx on words_social s1 (cost=0.29..8.30 rows=1 width=180) (actual time=0.002..0.002 rows=1 loops=8)
Index Cond: (uid = u1.uid)
-> Limit (cost=0.29..8.30 rows=1 width=180) (actual time=0.002..0.002 rows=1 loops=8)
-> Index Scan using words_social_uid_stamp_idx on words_social s2 (cost=0.29..8.30 rows=1 width=180) (actual time=0.002..0.002 rows=1 loops=8)
Index Cond: (uid = u2.uid)
Planning time: 0.587 ms
Execution time: 0.367 ms
(36 rows)
I was told that it still could be improved (by rearranging WHERE clauses?)
Regards
Alex
