Re: Long running queries degrade performance - Mailing list pgsql-performance

A long time ago, in a galaxy far, far away, nolan@gw.tssi.com (Mike Nolan) wrote:
>> We have a web app with a postgres backend.  Most queries have subsecond
>> response times through the web even with high usage.  Every once in awhile
>> someone will run either an ad-hoc query or some other long running db
>> process.
>
> Are you sure it is postgres where the delay is occurring?  I ask this
> because I also have a web-based front end to postgres, and while most of
> the time the queries respond in about a second every now and then I see
> one that takes much longer, sometimes 10-15 seconds.
>
> I've seen this behavior on both my development system and on the
> production server.
>
> The same query a while later might respond quickly again.
>
> I'm not sure where to look for the delay, either, and it is
> intermittent enough that I'm not even sure what monitoring
> techniques to use.

Well, a first thing to do is to see what query plans get set up for
the queries.  If the plans are varying over time, that suggests
something's up with ANALYZEs.

If the plans look a bit questionable, then you may be encountering the
situation where cache is helping you on the _second_ query but not the
first.  I did some tuning yesterday involving the same sort of
"symptoms," and that turned out to be what was happening.

I'll describe (in vague detail ;-)) what I was seeing.

- The table being queried was a "transaction" table, containing tens of
  thousands of records per day.

- The query was pulling summary information about one or another
  customer's activity on that day.

- The best index we had was on transaction date.

Thus, the query would walk through the "txn date" index, pulling
records into memory, and filtering them against the other selection
criteria.

The table is big, so that data is pretty widely scattered across many
pages.

The _first_ time the query is run, the data is all out on disk, and
there are hundreds-to-thousands of page reads to collect it all.  That
took 10-15 seconds.

The _second_ time it was run (as well as subsequent occasions), those
pages were all in cache, so the query runs in under a second.

What I wound up doing was to add an index on transaction date and
customer ID, so that a query that specifies both criteria will look
just for the few hundred (at most) records relevant to a particular
customer.  That's fast even the first time around.

We had a really useful "hook" on this one because the developer
noticed that the first time he queried for a particular day, it was
slow.  We could "repeat" the test easily by just changing to a day
that we hadn't pulled into cache yet.
--
(format nil "~S@~S" "cbbrowne" "acm.org")
http://cbbrowne.com/info/lisp.html
Referring to undocumented  private communications allows one to  claim
virtually anything: "we discussed this idea in  our working group last
year, and concluded that it was totally brain-damaged".
-- from the Symbolics Guidelines for Sending Mail