On Tue, Nov 27, 2012 at 10:06 AM, Seref Arikan <serefarikan@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
