Re: create table in memory

Thanks Merlin,
I'll take a better look at CTE.

Best regards
Seref

On Tue, Nov 27, 2012 at 4:48 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Tue, Nov 27, 2012 at 10:06 AM, Seref Arikan <serefarikan(at)gmail(dot)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
>