Re: Temporary tables and disk activity

I don't think temporary tables have any special rules regarding disk
writes, so I'd expect them ot get written out like everything else. The
database doesn't know you're going to delete them later.

Are the tables big?

On Thu, Dec 09, 2004 at 10:10:21PM +0000, Phil Endecott wrote:
> 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.
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.