Re: RAM-only temporary tables

>>> Alvaro Herrera <alvherre@commandprompt.com> wrote: 
> Kevin Grittner wrote:
> 
>> Note that the 150 disk writes were for the CREATE and the DROP. 
Does
>> that mean that we'd actually shave 84 of 150 writes?
> 
> Hmm, you'd shave more than 42 but not 84, because index entries are
not
> deleted until a later vacuum.  (I'd say about 56 -- 42 plus the 14
heap
> deletions).
> 
>> Also, if you're looking to account for all the writes, it's worth
>> noting that my test declared a one-column primary key (on an
integer
>> column) in the CREATE TEMPORARY TABLE statement.
> 
> That probably makes up for the extra few writes that I didn't see in
my
> quick test.
It sounds like you were counting the 8kB pages pushed from the
PostgreSQL cache to the OS cache, and I was counting the 1kB blocks
pushed from the OS cache to the RAID controller cache.  By watching
vmstat results after pushing this to a more-or-less steady state, I
was probably picking up the results of autovacuum runs, but multiple
writes to a single page were often combined by the OS.  If we match,
it's really just a coincidence.
-Kevin