Thanks Merlin, I'll take a better look at CTE. Best regards Seref
On Tue, Nov 27, 2012 at 4:48 PM, Merlin Moncure <mmonc...@gmail.com> wrote: > On Tue, Nov 27, 2012 at 10:06 AM, Seref Arikan <serefari...@gmail.com> > wrote: > > 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. > > Well, one thing to explore is use of CTE. general structure is: > WITH temp_data AS > ( > SELECT a_bunch_of_stuff .. > ), > modify_something AS > ( > UPDATE something_else > FROM temp_data ... > RETURNING * > ) > SELECT result_code > FROM modify_something ...; > > There are some pros and cons with this approach vs classic temp table > generation. > Pros: > *) since 9.1 and 'data modifying with' feature, you are not very much > constrained > *) dispense with traditional headaches in terms of managing temp tables > *) very neat and clean > Cons: > *) can't build indexes > > A hybrid approach, which is more complicated, is to organize a > permanent table with the current transaction id (via > txid_current()::text) as the left most part of the primary key. > > CREATE TABLE transaction_data > ( > xid text default txid_current()::text, > keyfield1 int, > keyfield2 text, > [data fields] > ); > > This is really fast since the data/indexes are ready to go at all > time. Your function always inserts, cleanup of stale transaction > records we can dispense to background process, particularly if you can > find appropriate time to TRUNCATE the table (which would hiccup > processes using the table). > > merlin >
