Re: Temp table or normal table for performance?

On Wed, Aug 19, 2009 at 03:03:28AM -0400, Stephen Cook wrote:
> Let's say I have a function that needs to collect some data from various
> tables and process and sort them to be returned to the user.
>
> In general, would it be better to create a temporary table in that
> function, do the work and sorting there, and return it... or keep a
> permanent table for pretty much the same thing, but add a "user session"
> field and return the relevant rows from that and then delete them?

The big difference between temp tables and normal tables is that temp
tables are not WAL logged, are not stored in shared_buffers and
generally don't require any of the usual transaction guarentees or
worrying about concurrent accesses between backends. As such they're
useful for dumping data only needed for single transactions/backends.

pl/pgsql had some serious warts w.r.t. temp tables prior to 8.4 so be
sure to test whatever you do thoughly.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.