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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
