Re: nooby Q: temp tables good for web apps?

Temp tables can be great for simplifying your code into more logical
sections. When making a case for using them, make sure to point out that
using them more aggressively can cut down on the amount of indexing you
need on the big tables, which has positive implications in terms of
getting simpler and robust query plans and cutting down on insertion
overhead.

You should be sure to turn on log_temp_files (which is handy in general,
that's not specific to temp tables). One specific thing to look for to
support your case is that sorts that used to execute in RAM and spill to
disk when they exceed work_mem might instead execute with less memory
usage; you'll be doing the final sort/filter steps using the temp tables
instead. If that is already happening, the overhead of using the temp
table can end up looking pretty good.

One thing I like doing when in the early development stages is to create a
seperate disk partition for the temporary tables, turn that into a
tablespace, and then use temp_tablespaces to point the temp tables toward
it. The idea is to separate out I/O to the temp tables so that you can
measure it to see how significant it is.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD