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.
|_|_) | |
| | | hjp@xxxxxx | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachment:
signature.asc
Description: PGP signature
