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