Thread: Temp table or normal table for performance?
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? Sorry this is vague, I know it most likely depends on the workload and such, but I'm just putting this together now. I could go either way, and also switch it up in the future if necessary. Is there a rule of thumb on this one? I'm a bit biased against temporary tables, but then again if the normal table gets a lot of action it might not be the optimal choice. Thanks and regards, Stephen Cook
Peter Hunsberger wrote: > On Wed, Aug 19, 2009 at 2:03 AM, Stephen Cook<sclists@gmail.com> 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? >> >> Sorry this is vague, I know it most likely depends on the workload and such, >> but I'm just putting this together now. I could go either way, and also >> switch it up in the future if necessary. Is there a rule of thumb on this >> one? I'm a bit biased against temporary tables, but then again if the >> normal table gets a lot of action it might not be the optimal choice. >> > > This completely depends on the specifics, there's no way anyone can > give you a general answer for this kind of problem. However, why do > you think you will need a temp or permanent table? Why can't you just > use your function to compute the answers at the time the user needs > the data? > I figured that would be the response I'd get :) I've decided on some type of table storage because basically I'm combining information from several different tables (some of which need to recursively get other rows) and massaging it and sorting it in ways far too convoluted to use a single query with UNION and ORDER BY, and then returning the results.
On Wed, Aug 19, 2009 at 08:10:14PM -0400, Stephen Cook wrote: > I've decided on some type of table storage because basically I'm > combining information from several different tables (some of which need > to recursively get other rows) and massaging it and sorting it in ways > far too convoluted to use a single query with UNION and ORDER BY, and > then returning the results. Sounds like you want a temp table to keep things in; you can add an ON COMMIT DROP which should help keep things tidy. If you're on 8.4 the WITH clause may make this use case easier. -- Sam http://samason.me.uk/
On 2009-08-19, Stephen Cook <sclists@gmail.com> 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. plpgsql functions don't play well with temp tables IME. there are work-arounds and they are ugly. if you caus use a different language it could work. > 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? > Sorry this is vague, I know it most likely depends on the workload and > such, but I'm just putting this together now. I could go either way, and > also switch it up in the future if necessary. Is there a rule of thumb > on this one? I'm a bit biased against temporary tables, but then again > if the normal table gets a lot of action it might not be the optimal choice. temp tables are usually worth the effort.
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@svana.org> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.
Attachment
On 20 Aug 2009 13:43:10 GMT Jasen Betts <jasen@xnet.co.nz> wrote: > On 2009-08-19, Stephen Cook <sclists@gmail.com> 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. > > plpgsql functions don't play well with temp tables IME. Why? you mean that since you generally use temp table for computation and looping several times over the table... a more expressive language would be suited? -- Ivan Sergio Borgonovo http://www.webthatworks.it
On Thu, Aug 20, 2009 at 2:43 PM, Jasen Betts<jasen@xnet.co.nz> wrote: > On 2009-08-19, Stephen Cook <sclists@gmail.com> 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. > > plpgsql functions don't play well with temp tables IME. > there are work-arounds and they are ugly. if you caus use a different > language it could work. it does on 8.3, prior versions have known flow. It makes a lot of sense to use TT if you pass a lot of data back and forth. It makes sense to open transaction, stick data into temp table, and pass that around. Or even, in some cases, for duration of connection - instead of storing data in client app.