Re: Queries intermittently slow

Scott Rankin <srankin@motus.com> writes:
> I’m trying to track down why some queries on my database system are intermittently much slower than usual.  I have
somequeries that run, on average, 2-3ms, and they run at a rate of about 10-20 queries/second.  However, every 3-5
seconds,one of the queries will be 500-100ms.  This is making the average query time turn out to be closer to 20ms,
witha very large standard deviation. 

> This happens to a number of otherwise very fast queries, and I’m trying to trace the reason.  I’ve turned on lock
loggingand checkpoint logging, and this behavior happens whether or not a checkpoint is occurring.  There are no lock
waitshappening in the system either. 

I doubt you've proved that --- log_lock_waits will only report on waits
longer than deadlock_timeout, which you don't appear to have changed from
its default of 1 sec.  If you're trying to capture events that last a few
hundred msec, you're going to need to reduce deadlock_timeout to maybe
100ms.

It would help to know more about what the queries are, too.  The cause
might be something like GIN index pending-list cleanup but we can't tell
on the basis of this much info.

            regards, tom lane