create table in memory

I've converted some mssql functions and they appear to be slower in pgsql.
I use a lot of declared tables in mssql as they are created in memory.
Which makes it very fast.

2012/11/23 Peter Kroon <plakroon@gmail.com>

On Fri, Nov 23, 2012 at 2:43 PM, Peter Kroon <plakroon@gmail.com> wrote:

I've converted some mssql functions and they appear to be slower in pgsql.
I use a lot of declared tables in mssql as they are created in memory.
Which makes it very fast.

2012/11/23 Peter Kroon <plakroon@gmail.com>

Is a temp table created to memory(ram) or disk?
I've converted some msssq

While the temporary table is in-use, For a small table the data will be in
the memory, For a large table if data is not fit in memory then data will
be flushed to disk periodically as the database engine needs more working
space for other requests.

A permanent table persist after terminating PostgreSQL session, whereas
temporary table is automatically destroyed when PostgreSQL session ends.

The memory is controlled by temp_buffers parameter (postgresql.conf)
--

Thanks & Regards,

Raghu Ram

EnterpriseDB Corporation

Blog:http://raghurc.blogspot.in/

On Fri, Nov 23, 2012 at 2:43 PM, Peter Kroon <plakroon@gmail.com> wrote:

I've converted some mssql functions and they appear to be slower in pgsql.
I use a lot of declared tables in mssql as they are created in memory.
Which makes it very fast.

2012/11/23 Peter Kroon <plakroon@gmail.com>

Is a temp table created to memory(ram) or disk?
I've converted some msssq

Not exactly as MS Sql declare tables.
In PostgreSQL, TEMP tables are session-private. These tables are session
based and stored in a special schema and visible only to the backend which
has created. Memory management is controlled with temp_buffer(shared by all
backends) in postgresql.conf.

You should check UNLOGGED tables of same family, these tables are visible
to all the backends and data shared across backends.
Since, data is not written to WAL's you should get better performance.

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

I've put up a small test case for creating TEMP and UNLOGGED tables.
DROP TABLE IF EXISTS test CASCADE;
CREATE TEMP TABLE test(
id serial,
the_value text
);
Exec time: 54ms

DROP TABLE IF EXISTS test CASCADE;
CREATE UNLOGGED TABLE test(
id serial,
the_value text
);
Exec time: 198ms

There is a significant difference.

Also I need those tables per session, so creating and dropping with TEMP
tables appear to be faster.

Best,
Peter KRoon

2012/11/23 Raghavendra <raghavendra.rao@enterprisedb.com>

I found out that declaring tables outside of functions increases the
execution time of the function.
And CREATE UNLOGGED TABLE is very fast.

2012/11/23 Peter Kroon <plakroon@gmail.com>

On 11/24/2012 02:15 AM, Peter Kroon wrote:

I found out that declaring tables outside of functions increases the
execution time of the function.

Strictly, what's probably happening is that creating a table in the same
transaction as populating it is a lot faster than creating it,
committing, and populating it in a new transaction. In the 1st case WAL
logging for the heap can be avoided if you aren't using replication or
PITR (ie wal_level is minimal).

Functions are automatically wrapped in a transaction if you don't open
one explicitly so doing a CREATE TABLE inside a function will be
quicker. The same result should be achieved by beginning a transaction,
creating the table, then calling the function.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Could you provide an example?

Fo me:
Drop/Creat/populating tables inside a function are slow.
Creating tables outside a function and populating he table inside a
function is fast..

2012/11/24 Craig Ringer <craig@2ndquadrant.com>

On Fri, Nov 23, 2012 at 4:09 AM, Peter Kroon <plakroon@gmail.com> wrote:

I've put up a small test case for creating TEMP and UNLOGGED tables.
DROP TABLE IF EXISTS test CASCADE;
CREATE TEMP TABLE test(
id serial,
the_value text
);
Exec time: 54ms

DROP TABLE IF EXISTS test CASCADE;
CREATE UNLOGGED TABLE test(
id serial,
the_value text
);
Exec time: 198ms

There is a significant difference.

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.

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi Merlin,
See below please

On Tue, Nov 27, 2012 at 3:29 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Fri, Nov 23, 2012 at 4:09 AM, Peter Kroon <plakroon@gmail.com> wrote:

I've put up a small test case for creating TEMP and UNLOGGED tables.
DROP TABLE IF EXISTS test CASCADE;
CREATE TEMP TABLE test(
id serial,
the_value text
);
Exec time: 54ms

DROP TABLE IF EXISTS test CASCADE;
CREATE UNLOGGED TABLE test(
id serial,
the_value text
);
Exec time: 198ms

There is a significant difference.

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?

Best regards
Seref

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?

Well, first,
*) 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 10:06 AM, Seref Arikan <serefarikan@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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@gmail.com> wrote: