Skip site navigation (1) Skip section navigation (2)

performance of temporary vs. regular tables

From: Joachim Worringen <joachim(dot)worringen(at)iathh(dot)de>
To: pgsql-performance(at)postgresql(dot)org
Subject: performance of temporary vs. regular tables
Date: 2010-05-25 07:59:56
Message-ID: 4BFB837C.6010005@iathh.de (view raw or flat)
Thread:
Lists: pgsql-performance
Greetings,

in 
http://archives.postgresql.org/message-id/1056648218.7041.11.camel@jester, 
it is stated that the performance of temporary tables is "the same as a 
regular table but without
WAL on the table contents.".

I have a datamining-type application which makes heavy use of temporary 
tables to stage (potentially large amounts of) data between different 
operations. WAL is write-ahead

To effectively multi-thread this application, I (think I) need to switch 
from temporary to regular tables, because
- the concurrent threads need to use different connections, not cursors, 
to effectively operate concurrently
- temporary tables are not visible across connections (as they are 
across cursors of the same connection)

Thus, I wonder how much this will affect performance. Access on the 
temporary table is inserting (millions of) rows once in a single 
transaction, potentially update them all once within a single 
transaction, then select on them once or more.

Of course, eventually loosing the data in these tables is not a problem 
at all. The threads are synchronized above the SQL level.

Thanks for any input on how to maximize performance for this applicaiton.

  Joachim


Responses

pgsql-performance by date

Next:From: Grzegorz JaśkiewiczDate: 2010-05-25 08:49:13
Subject: Re: performance of temporary vs. regular tables
Previous:From: David JarvisDate: 2010-05-25 06:41:29
Subject: Re: Random Page Cost and Planner

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group