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

Greg Smith wrote:
> 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.

Thx, I will keep that in mind as a good way of really seeing what is
going on. I did notice the tablespace feature but wasn't sure how to
leverage it. Mgmt has been lusting after those new solid-state memory
disks (SSDs?), this could be a good excuse for a PO. We are a skunkworks
project getting as much praise so far for the speed of the web app as
anything else so we don't want to give up this plus.

ken