I have a function that creates a temp table, inserts rows into it, performs joins, and returns a single integer as a result. This is pg 9.1. All sessions are using the exact same temp table structure.
re performance requirements: I need this function to return as fast as possible :) On a production server, if the function can complete in around 10-20 milliseconds, it would be really good (below 10 ms would be great). The average number of inserted into temp table is around 800, and there are about 10 columns in the current design.
Kind regards
Seref
On Tue, Nov 27, 2012 at 3:50 PM, Merlin Moncure
<mmoncure@gmail.com> wrote:
On Tue, Nov 27, 2012 at 9:44 AM, Seref Arikan <
serefarikan@gmail.com> wrote:
>> > Also I need those tables per session, so creating and dropping with TEMP
>> > tables appear to be faster.
>>
>> Performance of creating tables is going to be storage bound. what are
>> your performance requirements? Even if the temp table itself is moved
>> to ramdisk you have catalog updating. Usually from performance
>> standpoint, creation of temp tables is not interesting -- but there
>> are exceptions. If you need extremely fast creation/drop of tempe
>> tables, you probably need to reorganize into permanent table with
>> session local records using various tricks.
>
>
> I am very interested in what you've written in the last sentence above,
> since it is exactly what my requirement is. Could you explain that a bit
> more?
*) is your temporary data session or transaction local (transaction
meaning for duration of function call or till 'commit').
*) if 'transaction' above, what version postgres? if 9.1+ let's
explore use of wcte
*) what are your performance requirements in detail
*) are all sessions using same general structure of temp table(s)?
merlin