Temporary tables and disk activity

Dear All,

I sent a message last weekend asking about temporary tables being
written to disk but didn't get any replies.  I'm sure there is someone
out there who knows something about this - please help!  Here is the
question again:


Looking at vmstat output on my database server I have been suprised to
see lots of disk writes going on while it is doing what should be
exclusively read-only transactions. I see almost no disk reads as the
database concerned is small enough to fit into the OS disk cache.

I suspect that it might be something to do with temporary tables. There
are a couple of places where I create temporary tables to "optimise"
queries by factoring out what would otherwise be duplicate work. The
amount of data being written is of the right order of magnitude for this
to be the cause. I fear that perhaps Postgresql is flushing these tables
to disk, even though they will be dropped before the end of the
transaction. Is this a possibility? What issues should I be aware of
with temporary tables? Are there any other common causes of lots of disk
writes within read-only transactions? Is there any debug output that I
can look at to track this down?

Thanks in advance for any help that you can offer.

Regards,

Phil Endecott.