Re: Create and drop temp table in 8.3.4

От: Kevin Grittner
Тема: Re: Create and drop temp table in 8.3.4
Дата: ,
Msg-id: 49131226.EE98.0025.0@wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: Re: Create and drop temp table in 8.3.4  ("Scott Carey")
Список: pgsql-performance

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

Create and drop temp table in 8.3.4  ("Kevin Grittner", )
 Re: Create and drop temp table in 8.3.4  ("Kevin Grittner", )
 Re: Create and drop temp table in 8.3.4  ("Kevin Grittner", )
  Re: Create and drop temp table in 8.3.4  ("Kevin Grittner", )
   Re: Create and drop temp table in 8.3.4  ("Joshua D. Drake", )
    Re: Create and drop temp table in 8.3.4  ("Kevin Grittner", )
     Re: Create and drop temp table in 8.3.4  ("Scott Carey", )
      Re: Create and drop temp table in 8.3.4  ("Kevin Grittner", )
      Re: Create and drop temp table in 8.3.4  ("Scott Marlowe", )
       Re: Create and drop temp table in 8.3.4  ("Kevin Grittner", )
        Re: Create and drop temp table in 8.3.4  ("Scott Marlowe", )
         Re: Create and drop temp table in 8.3.4  ("Kevin Grittner", )
          Re: Create and drop temp table in 8.3.4  ("Scott Marlowe", )
           Re: Create and drop temp table in 8.3.4  ("David Rees", )
           Re: Create and drop temp table in 8.3.4  (Matthew Wakeling, )
    Re: Create and drop temp table in 8.3.4  (Aidan Van Dyk, )
 Re: Create and drop temp table in 8.3.4  (Tom Lane, )

>>> "Scott Carey" <> wrote:
> Note that Write Barriers can be very important for data integrity
when power
> loss or hardware failure are a concern.  Only disable them if you
know the
> consequences are mitigated by other factors (such as a BBU + db using
the
> WAL log with sync writes), or if you accept the additional risk to
data
> loss.

For those using xfs, this link may be useful:

http://oss.sgi.com/projects/xfs/faq.html#wcache

> On Temp Tables:
> I am a bit ignorant on the temp table relationship to file creation
-- it
> makes no sense to me at all that a file would even be created for a
temp
> table unless it spills out of RAM or is committed.  Inside of a
transaction,
> shouldn't they be purely in-memory if there is space?  Is there any
way to
> prevent the file creation?  This seems like a total waste of time for
many
> temp table use cases, and explains why they were so slow in some
exploratory
> testing we did a few months ago.

As I learned today, creating a temporary table in PostgreSQL can
easily create four files and do dozens of updates to system tables;
that's all before you start actually inserting any data into the
temporary table.

-Kevin


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

От: "David Rees"
Дата:
Сообщение: Re: Create and drop temp table in 8.3.4
От: "Віталій Тимчишин"
Дата:
Сообщение: Re: PostgreSQL OR performance