"Rosser Schwarz" <[EMAIL PROTECTED]> writes: > `strace -p 21882` run behind the below query and plan ... below that.
Hmm ... that took 20 seconds eh? It is a fairly interesting trace. It shows that the backend needed to read 63 system catalog pages (that weren't already in shared memory), which is not too unreasonable I think ... though I wonder if more of them shouldn't have been in memory already. The odd thing is that for *every single read* it was necessary to first dump out a dirty page in order to make a buffer free. That says you are running with the entire contents of shared buffer space dirty at all times. That's probably not the regime you want to be operating in. I think we already suggested increasing shared_buffers. You might also want to think about not using such a large checkpoint interval. (The background-writing logic already committed for 7.5 should help this problem, but it's not there in 7.4.) Another interesting fact is that the bulk of the writes were "blind writes", involving an open()/write()/close() sequence instead of keeping the open file descriptor around for re-use. This is not too surprising in a freshly started backend, I guess; it's unlikely to have had reason to create a relation descriptor for the relations it may have to dump pages for. In some Unixen, particularly Solaris, open() is fairly expensive and so blind writes are bad news. I didn't think it was a big problem in Linux though. (This is another area we've improved for 7.5: there are no more blind writes. But that won't help you today.) What's not immediately evident is whether the excess I/O accounted for all of the slowdown. Could you retry the strace with -r and -T options so we can see how much time is being spent inside and outside the syscalls? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org