Please help to improve SELECT JOIN performance, EXPLAIN ANALYZE attached

On 2021-02-20 10:24:36 +0100, Alexander Farber wrote:
> The query when I try it does run in 15ms, but evening logs show the query (I
> think only small percentage of it) running 1-3s.
>
> At the same time my CentOS 8 server with 64 GB RAM is never loaded, the load
> average show by top is 0.35-0.45.
>
> What could be the reason, does PostgreSQL 13.2 has some scheduled task maybe?

One very common reason for wildly different execution times is where the
data is: Accessing data in RAM is a lot faster than accessing it on disk
(even for SSDs, but especially for rotating disks).

So it might be that when you executed the EXPLAIN the data just happened
to be in RAM (that's almost certainly the case if you executed the same
query without EXPLAIN before that) while in those cases where it was
slow, the data (or a large part of it) had to be loaded from disk first.

Data might not be in RAM because it wasn't needed for some time, because
some other query or process which needed lots of RAM displaced it, or
because the server was just rebootet.


> I have only changed few lines in the postgresql.conf:

I don't see shared_buffers here. That's quite small by default so you
might want to increase it. The usual recommendation is to start with 25%
of the memory (that would be 16 GB in your case) and then see if it gets
better if decrease or increase it.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [hidden email]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

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.
|_|_) |                    |
| |   | [hidden email]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On 2021-02-20 06:43:28 -0700, Michael Lewis wrote:
> Using auto_explain may be needed to catch the plan when it is slow, if the
> other suggestions don't resolve things for you.

Yes, auto_explain is very helpful. Unfortunately, by default it only
does an explain, not the equivalent of an explain analyze. So you know
what the plan was and what the total time was, but you still don't know
where it spent that time.

You can set auto_explain.log_analyze = on, but the documentation warns
that "this can have an extremely negative impact on performance". Does
anybody have experience with trying this in a real-world workload? (I
was never brave enough)

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [hidden email]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On 2021-02-21 10:14:04 -0700, Michael Lewis wrote:
> No issues for us. We have used a low sample rate of 1% or so and gotten some
> very useful data.

Oh, somehow I never noticed the auto_explain.sample_rate parameter in
the docs. Good to know.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [hidden email]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"