[Tilesathome] Using RAM-drive for ROMA temp tables

[Martijn van Oosterhout]

On Sat, Dec 6, 2008 at 9:22 AM, Mathieu Arnold <mat at mat.cc> wrote:
> I'd like to add one thing at that. On my instance, the query returning the
> nodes in the bbox takes at most 1s, and most of the time is under 0.1s. The
> index takes about 14GB, and I only have 3.5GB of RAM. I do think it's *not*
> that bad :-)

Note it's more complicated still. Even though the index is 14GB, if
you remove all the leaves of the index, it's probably less than 1GB
because the width of the index entry is so small. That you cache
easily. Which means that each node lookup will take at most 2 disk
seeks. Add locality of reference by area and the fact that render
requests are not distributed evenly over the world and the average
performance would be pretty good.

PostgreSQL does treat temp tables specially, they generate (almost) no
WAL, don't use up space in the shared_buffers and indeed won't be
written out of not necessary. temp_buffers is per backend. One
downside of adding a tablespace on a RAM disk is that it will probably
break if you restart your computer.

Have a nice day,
-- 
Martijn van Oosterhout <kleptog at gmail.com> http://svana.org/kleptog/