Re: [GENERAL] Temp table or normal table for performance?
On 20 Aug 2009 13:43:10 GMT Jasen Betts ja...@xnet.co.nz wrote: On 2009-08-19, Stephen Cook scli...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Temp table or normal table for performance?
On Thu, Aug 20, 2009 at 2:43 PM, Jasen Bettsja...@xnet.co.nz wrote: On 2009-08-19, Stephen Cook scli...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Temp table or normal table for performance?
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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Temp table or normal table for performance?
On 2009-08-19, Stephen Cook scli...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 klep...@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. signature.asc Description: Digital signature
Re: [GENERAL] Temp table or normal table for performance?
Peter Hunsberger wrote: On Wed, Aug 19, 2009 at 2:03 AM, Stephen Cookscli...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
