Re: Global temporary tables

On 16.08.2019 11:32, Craig Ringer wrote:
>
> 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