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
