Re: Global temporary tables

From: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
To: Craig Ringer <craig(at)2ndquadrant(dot)com>
Cc: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Global temporary tables
Date: 2019-08-16 11:41:36
Message-ID: 758e5293-3ccd-3a6a-8464-6373bb441994@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Ibrar Ahmed 2019-08-16 12:58:59 Re: [Proposal] Table-level Transparent Data Encryption (TDE) and Key Management Service (KMS)
Previous Message Konstantin Knizhnik 2019-08-16 11:21:36 Re: Global temporary tables