Re: RAM-only temporary tables

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I think a large fraction of the writes you're measuring are coming from
>> the file create/unlink operations. It would certainly be important to
>> identify where the bulk of the cost *really* is before we start
>> expending effort on a solution.

> Any ideas on a good way to gather that information?

I had done some preliminary trials using strace (you need to trace the
active backend, the bgwriter, and the wal writer process to be sure you
see everything going on). However it's difficult to tell how much
physical I/O results from the create or unlink syscalls. It might be
interesting to make a test program that just creates 4000 files and then
removes them again, and see what sort of load you see from that.

> Given the temp_buffers space, would it make sense to defer the
> creation of the actual file until there is actually a need to spill
> data to the disk?

No, because that opens us to problems with reuse of relfilenode numbers.

One place that I've always wanted to look at was suppressing the
creation of a btree metapage until there's some useful data in the
table. We managed to avoid creating a root page until there's data,
but at the time avoiding the metapage seemed too invasive. (Admittedly,
though, if one assumes that your real world case does involve putting
some data in the tables, this wouldn't actually save anything...)

regards, tom lane