On 2021-02-19 21:25:36 +0100, Alexander Farber wrote: > In the PostgreSQL log I have noticed that the duration for 2 particular queries > have increased, especially in the evenings: [...] > One of the queries is actually a simple SELECT ... LIMIT 15 to find the 15 > longest words played by a user (they are displayed at the player profile page). > > I have run the EXPLAIN ANALYZE here: https://explain.depesz.com/s/IcqN Well, I wouldn't call a query with 8 joins "simple". I notice two things: 1) It does a lot more than the description ("Select 15 longest words played by the user id 5") suggests. It not only selects those words, but also the games in which they were played and some information about both players. 2) The query seems to spend most of the time in https://explain.depesz.com/s/IcqN#l11, retrieving all the words ever played by that user via a parallel bitmap heap scan, only to sort them and discard all but the 15 longest. I think using an index should make that much more efficient. Therefore, three suggestions: 1) Add an index on words_scores(uid, length(word) desc). 2) If postgresql still isn't using that, isolate the core query by putting it into a CTE: with s as (select * from words_scores where uid = 5 order by length(word)) select ... from s join words_moves m ... 3) If that shifts the bottleneck towards that extra per-user info (location, photo, ...) try to cache that in the application. That probably doesn't change very often and doesn't have to be retrieved from the database every time. hp -- _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@xxxxxx | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!"
Attachment:
signature.asc
Description: PGP signature
