Re: Global temporary tables

You ignore the costs of evicting non-temporary data from shared_buffers, i.e. contention for space. Also increased chance of backends being forced to do direct write-out due to lack of s_b space for dirty buffers.

 

> In case of pulling all content of temp table in memory (pg_prewarm) global temp table with shared buffers becomes faster.

Who would ever do that?

I decided to redo my experiments and now get different results which illustrates advantages of global temp tables with shared buffer.
I performed the following test at my desktop with SSD and 16GB of RAM and Postgres with default configuration except shared-buffers increased to 1Gb.


postgres=# create table big(pk bigint primary key, val bigint);
CREATE TABLE
postgres=# insert into big values (generate_series(1,100000000),generate_series(1,100000000)/100);
INSERT 0 100000000
postgres=# select * from buffer_usage limit 3;
    relname     |  buffered  | buffer_percent | percent_of_relation
----------------+------------+----------------+---------------------
 big            | 678 MB     |           66.2 |                16.1
 big_pkey       | 344 MB     |           33.6 |                16.1
 pg_am          | 8192 bytes |            0.0 |                20.0

postgres=# create temp table lt(key bigint, count bigint);
postgres=# \timing
Timing is on.
postgres=# insert into lt (select count(*),val as key from big group by val);
INSERT 0 1000001
Time: 43265.491 ms (00:43.265)
postgres=# select sum(count) from lt;
     sum     
--------------
 500000500000
(1 row)

Time: 94.194 ms
postgres=# insert into gt (select count(*),val as key from big group by val);
INSERT 0 1000001
Time: 42952.671 ms (00:42.953)
postgres=# select sum(count) from gt;
     sum     
--------------
 500000500000
(1 row)

Time: 35.906 ms
postgres=# select * from buffer_usage limit 3;
 relname  | buffered | buffer_percent | percent_of_relation
----------+----------+----------------+---------------------
 big      | 679 MB   |           66.3 |                16.1
 big_pkey | 300 MB   |           29.3 |                14.0
 gt       | 42 MB    |            4.1 |               100.0


So time of storing result in global temp table is slightly smaller than time of storing it in local temp table and time of scanning global temp table is twice smaller!

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company