Re: Queries intermittently slow

Scott Rankin <srankin(at)motus(dot)com> writes:
> Thanks for the update. The query in question is a pretty simple one - it joins 3 tables, all of which are static - they dont have any writes being done against them. They have very few rows, and the query plan for them indicates that they are all sequential scans. When doing an EXPLAIN ANALYZE, the delay is not consistently on one table, it can vary between the three tables involved in the query.

No writes at all? That's pretty odd then. All the likely explanations
involve autovacuum or other forms of deferred maintenance.

A possible theory is that the slow cases represent times when the desired
page is not in cache, but you'd have to have a seriously overloaded disk
subsystem for a disk fetch to take hundreds of ms. Unless maybe this is
running on some cloud service with totally unspecified I/O bandwidth?

> I will look at changing the deadlock_timeout, but that might have to wait for the weekend since this is a production system.

You needn't restart the server for that, just edit postgresql.conf and
SIGHUP the postmaster.

regards, tom lane