Re: Temp tables and LRU-K caching

Mike Mascari <mascarm(at)mascari(dot)com> writes:
> Bruce wrote:
> "Yes, someone from India has a project to test LRU-K and MRU for
> large table scans and report back the results. He will
> implement whichever is best."
> Did this make it into 7.3?

No, we never heard back from that guy. It is still a live topic though.
One of the Red Hat people was looking at it over the summer, and I think
Neil Conway is experimenting with LRU-2 code right now.

> 2. Gavin Sherry had worked up a patch so that temporary
> relations could be dropped automatically upon transaction
> commit. Did any of those patches it make it?

No they didn't; I forget whether there was any objection to his last try
or it was just too late to get reviewed before feature freeze.

> I notice that
> whenever I create a temporary table in a transaction, my HD
> light blinks. Is this a forced fsync() causes by the fact that
> the SQL standard defines temporary relations as surviving across
> transactions?

A completely-in-memory temp table is not really practical in Postgres,
for two reasons: one being that its schema information is stored in
the definitely-not-temp system catalogs, and the other being that we
request allocation of disk space for each page of the table, even if
it's temp. It might be possible to work around the latter issue (at
the cost of quite unfriendly behavior should you run out of disk space)
but short of a really major rewrite there isn't any way to avoid keeping
temp table catalog info in the regular catalogs. So you are certainly
going to get a disk hit when you create or drop a temp table.

7.3 should be considerably better than 7.1 or 7.2 for temp table access
because it doesn't WAL-log operations on the data within temp tables,
though.

Another thing I'd like to see in the near future is a configurable
setting for the amount of memory space that can be used for temp-table
buffers. The current setting is ridiculously small (64*8K IIRC), but
there's not much point in increasing it until we also have a smarter
management algorithm for the temp buffers. I've asked Neil to look at
making the improved LRU-K buffer management algorithm apply to temp
buffers as well as regular shared buffers.

regards, tom lane