Обсуждение: UNLOGGED TEMPORARY tables?

I was tested write speed to temporary and unlogged tables and noticed that
unlogged tables was a much faster

Postgres 9.2.2

Write speed

Temporary 14.5k/s
UNLOGGED 50k/s

Before test I was convinced that temporary tables in postgres >= 9.1 are
unlogged





--
View this message in context: http://postgresql.1045698.n5.nabble.com/UNLOGGED-TEMPORARY-tables-tp5749477.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

I'm pretty sure that unlogged tables and temp tables are two separate
& distinct features, with no overlap in functionality.  It would be
nice if it was possible to create an unlogged temp table.

On Sun, Mar 24, 2013 at 1:32 PM, aasat <satriani@veranet.pl> wrote:
> I was tested write speed to temporary and unlogged tables and noticed that
> unlogged tables was a much faster
>
> Postgres 9.2.2
>
> Write speed
>
> Temporary 14.5k/s
> UNLOGGED 50k/s
>
> Before test I was convinced that temporary tables in postgres >= 9.1 are
> unlogged
>
>
>
>

aasat <satriani@veranet.pl> writes:
> I was tested write speed to temporary and unlogged tables and noticed that
> unlogged tables was a much faster

> Postgres 9.2.2

> Write speed

> Temporary 14.5k/s
> UNLOGGED 50k/s

I think there's something skewed about your test.

Temp tables *are* unlogged.  They also live in session-private buffers,
which eliminates a great deal of synchronization overhead; at the cost
that any writing that does happen has to be done by the backend process
itself, without help from the background writer.  It's possible that
there's something about your specific test case that makes that scenario
look bad.  Another likely source of bogus results is if you were testing
a tiny temp_buffers setting versus a more appropriately sized
shared_buffers setting.

However, this is all speculation, since you provided not a whit of
detail about your test case.  Nobody's going to take these numbers
seriously if you haven't explained how to reproduce them.

            regards, tom lane

On Mon, Mar 25, 2013 at 4:49 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
>
>
> On Tue, Mar 26, 2013 at 8:26 AM, Lonni J Friedman <netllama@gmail.com>
> wrote:
>>
>> I'm pretty sure that unlogged tables and temp tables are two separate
>> & distinct features, with no overlap in functionality.  It would be
>> nice if it was possible to create an unlogged temp table.
>
> Temporary tables are a subtype of unlogged tables, as temporary tables are
> not WAL-logged.
> This article from Robert Haas will give a good summary of such differences:
> http://rhaas.blogspot.jp/2010/05/global-temporary-and-unlogged-tables.html


Thanks, that's good to know.  the official dox don't really make it
clear that temp tables are unlogged.

New test with temporary table and unlogged

Total rows 600k
Table size after copy 121MB

temp_buffers / copy speed

16MB - 12999 rows/s
128MB - 13005 rows/s
256MB - 13258 rows/s
512MB - 13399 rows/s
1GB -  13145 rows/s

Unlogged table - 13333 rows/s

I don't undestand why previous test showed the difference



--
View this message in context: http://postgresql.1045698.n5.nabble.com/UNLOGGED-TEMPORARY-tables-tp5749477p5749662.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

temp_buffers = 16MB



--
View this message in context: http://postgresql.1045698.n5.nabble.com/UNLOGGED-TEMPORARY-tables-tp5749477p5749656.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.