A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (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 "[EMAIL PROTECTED]" "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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
