Re: Please help to improve SELECT JOIN performance, EXPLAIN ANALYZE attached

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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"