Subject: Re: Global temporary tables
To: Craig Ringer <craig@2ndquadrant.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@postgresql.org>
References: <73954ab7-44d3-b37b-81a3-69bdcbb446f7@postgrespro.ru>
 <8b4bdfa6-7a90-95e2-1fd0-243767e5b357@postgrespro.ru>
 <CAMsr+YER2y7mYb82wNvL6LECvJ_vL3Bv67eToKUuDwL_aLmJaQ@mail.gmail.com>
 <94a19e7b-d99a-110c-8e5b-f5068682b474@postgrespro.ru>
 <CAMsr+YEM0kHskTucOwH6d4YPfeTSPXj6bXNH3VcG5jsF3d=-sQ@mail.gmail.com>
 <614c1e9f-c0e6-332c-ba2b-85a7e1efb956@postgrespro.ru>
 <CAFj8pRA2iMGNWre-LABC_JRLUi_Ay7mxhbET0Hp7=20CbgF7Lg@mail.gmail.com>
 <5a711f75-e999-a809-60e1-c74c8c9e7915@postgrespro.ru>
 <CAFj8pRAJ_fAZrwcp9FgKsS+mGk3g5t2iZnqpcDiPXp=O6DmLFQ@mail.gmail.com>
 <CAMsr+YFh5N0TCtaMmUXZ85wJx1EkMwV+cjWWbQvMbtyJsc2ONg@mail.gmail.com>
 <21f047d6-bb2d-950c-2aff-2fee301d5851@postgrespro.ru>
 <CAMsr+YHpjEwTWYBbRtOCp5F966yXHeDsBzy1WSUSA1ykVz33mA@mail.gmail.com>
 <91de2094-165a-04f1-9b3d-348473394345@postgrespro.ru>
 <CAMsr+YFFZ2c0XfvNO2FK27gncZn8FdYLjZL2=2wCEy=vDFZk1A@mail.gmail.com>
 <fe9db665-b488-8b8d-fb7e-d8063798eafc@postgrespro.ru>
 <CAMsr+YF-_ni33MWLEMc3jtbh_-SFSfBBpXCsPBfiB_bTYKoQug@mail.gmail.com>
From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Message-ID: <758e5293-3ccd-3a6a-8464-6373bb441994@postgrespro.ru>
Date: Fri, 16 Aug 2019 14:41:36 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.2.1
MIME-Version: 1.0
In-Reply-To: 
 <CAMsr+YF-_ni33MWLEMc3jtbh_-SFSfBBpXCsPBfiB_bTYKoQug@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------2319A5AF8C8CF82C64E59B6B"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------2319A5AF8C8CF82C64E59B6B
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit


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


--------------2319A5AF8C8CF82C64E59B6B
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <br>
    <br>
    <div class="moz-cite-prefix">On 16.08.2019 11:32, Craig Ringer
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAMsr+YF-_ni33MWLEMc3jtbh_-SFSfBBpXCsPBfiB_bTYKoQug@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div class="gmail_quote"><br>
          <div>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.</div>
          <div> </div>
          <div>&gt; In case of pulling all content of temp table in
            memory (pg_prewarm) global temp table with shared buffers
            becomes faster.</div>
          <div><br>
          </div>
          <div>Who would ever do that?</div>
          <div><br>
          </div>
          <br>
        </div>
      </div>
    </blockquote>
    <br>
    I decided to redo my experiments and now get different results which
    illustrates advantages of global temp tables with shared buffer.<br>
    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.<br>
    <br>
    <br>
    postgres=# create table big(pk bigint primary key, val bigint);<br>
    CREATE TABLE<br>
    postgres=# insert into big values
    (generate_series(1,100000000),generate_series(1,100000000)/100);<br>
    INSERT 0 100000000<br>
    postgres=# select * from buffer_usage limit 3;<br>
        relname     |  buffered  | buffer_percent | percent_of_relation
    <br>
    ----------------+------------+----------------+---------------------<br>
     big            | 678 MB     |           66.2 |                16.1<br>
     big_pkey       | 344 MB     |           33.6 |                16.1<br>
     pg_am          | 8192 bytes |            0.0 |                20.0<br>
    <br>
    postgres=# create temp table lt(key bigint, count bigint);<br>
    postgres=# \timing<br>
    Timing is on.<br>
    postgres=# insert into lt (select count(*),val as key from big group
    by val);<br>
    INSERT 0 1000001<br>
    Time: 43265.491 ms (00:43.265)<br>
    postgres=# select sum(count) from lt;<br>
         sum      <br>
    --------------<br>
     500000500000<br>
    (1 row)<br>
    <br>
    Time: 94.194 ms<br>
    postgres=# insert into gt (select count(*),val as key from big group
    by val);<br>
    INSERT 0 1000001<br>
    Time: 42952.671 ms (00:42.953)<br>
    postgres=# select sum(count) from gt;<br>
         sum      <br>
    --------------<br>
     500000500000<br>
    (1 row)<br>
    <br>
    Time: 35.906 ms<br>
    postgres=# select * from buffer_usage limit 3;<br>
     relname  | buffered | buffer_percent | percent_of_relation <br>
    ----------+----------+----------------+---------------------<br>
     big      | 679 MB   |           66.3 |                16.1<br>
     big_pkey | 300 MB   |           29.3 |                14.0<br>
     gt       | 42 MB    |            4.1 |               100.0<br>
    <br>
    <br>
    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!<br>
    <br>
    <br>
    <pre class="moz-signature" cols="72">-- 
Konstantin Knizhnik
Postgres Professional: <a class="moz-txt-link-freetext" href="http://www.postgrespro.com">http://www.postgrespro.com</a>
The Russian Postgres Company </pre>
  </body>
</html>

--------------2319A5AF8C8CF82C64E59B6B--