performance of temporary vs. regular tables

От: Joachim Worringen
Тема: performance of temporary vs. regular tables
Дата: ,
Msg-id: 4BFB837C.6010005@iathh.de
(см: обсуждение, исходный текст)
Ответы: Re: performance of temporary vs. regular tables  (Grzegorz Jaśkiewicz)
Список: pgsql-performance

Скрыть дерево обсуждения

performance of temporary vs. regular tables  (Joachim Worringen, )
 Re: performance of temporary vs. regular tables  (Grzegorz Jaśkiewicz, )
  Re: performance of temporary vs. regular tables  (Joachim Worringen, )
   Re: performance of temporary vs. regular tables  (Thom Brown, )
    Re: performance of temporary vs. regular tables  (Joachim Worringen, )
     Re: performance of temporary vs. regular tables  (Grzegorz Jaśkiewicz, )
      Re: performance of temporary vs. regular tables  (Joachim Worringen, )
   Re: performance of temporary vs. regular tables  (Andres Freund, )
    Re: performance of temporary vs. regular tables  (Joachim Worringen, )
     Re: performance of temporary vs. regular tables  (Grzegorz Jaśkiewicz, )
     Re: performance of temporary vs. regular tables  (Joachim Worringen, )
      Re: performance of temporary vs. regular tables  (Rob Wultsch, )
      Re: performance of temporary vs. regular tables  ("Pierre C", )
       Re: performance of temporary vs. regular tables  (Joachim Worringen, )
        Re: performance of temporary vs. regular tables  ("Pierre C", )

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



В списке pgsql-performance по дате сообщения:

От: Juan Pablo Sandoval Rivera
Дата:
Сообщение: tunning pgsql 7.3.7 over RHEL 4.0 32 x86 (2.6.9-5ELsmp)
От: Joshua Tolley
Дата:
Сообщение: Re: tunning pgsql 7.3.7 over RHEL 4.0 32 x86 (2.6.9-5ELsmp)