MIME-Version: 1.0
References: <73954ab7-44d3-b37b-81a3-69bdcbb446f7@postgrespro.ru>
 <8e0b539b-4b64-d2b2-08a1-ddaa3c7e7747@postgrespro.ru>
 <CAMsr+YFahmjG0PaFPL-J7Zxa4cgt_PFtgSNV7nRNgCE=29cvcw@mail.gmail.com>
 <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>
In-Reply-To: <fe9db665-b488-8b8d-fb7e-d8063798eafc@postgrespro.ru>
From: Craig Ringer <craig@2ndquadrant.com>
Date: Fri, 16 Aug 2019 16:32:52 +0800
Message-ID: 
 <CAMsr+YF-_ni33MWLEMc3jtbh_-SFSfBBpXCsPBfiB_bTYKoQug@mail.gmail.com>
Subject: Re: Global temporary tables
To: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Cc: Pavel Stehule <pavel.stehule@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000c5a442059037d61d"
Precedence: bulk

--000000000000c5a442059037d61d
Content-Type: text/plain; charset="UTF-8"

On Fri, 16 Aug 2019 at 15:30, Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
wrote:


>
> 1. Statistic for global temporary tables (including number of tuples,
> pages and all visible flag).
> My position is the following: while in most cases it should not be a
> problem, because users rarely create indexes or do analyze for temporary
> tables,
> there can be situations when differences in data sets of global temporary
> tables in different backends can really be a problem.
> Unfortunately I can not propose good solution for this problem. It is
> certainly possible to create some private (per-backend) cache for this
> metadata.
> But it seems to requires changes in many places.
>

Yeah. I don't really like just sharing them but it's not that bad either.


> 2. Your concerns about performance penalty of global temp tables accessed
> through shared buffers comparing with local temp tables access through
> local buffers.
> I think that this concern is not  actual any more because there is
> implementation of global temp tables using local buffers.
> But my experiments doesn't show significant difference in access speed of
> shared and local buffers. As far as shared buffers are used to be much
> larger than local buffers,
> there are more chances to hold all temp relation in memory without
> spilling it to the disk. In this case access to global temp table will be
> much faster comparing with access to
> local temp tables.
>

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 forget or do not notice some of your questions, would you be so kind as
> to repeat them?
>


-- 
 Craig Ringer                   http://www.2ndQuadrant.com/
 2ndQuadrant - PostgreSQL Solutions for the Enterprise

--000000000000c5a442059037d61d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, 16 Aug 2019 at 15:30, Konstantin =
Knizhnik &lt;<a href=3D"mailto:k.knizhnik@postgrespro.ru">k.knizhnik@postgr=
espro.ru</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><div>=C2=A0</di=
v><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;borde=
r-left:1px solid rgb(204,204,204);padding-left:1ex"><div bgcolor=3D"#FFFFFF=
">
    <br>
    1. Statistic for global temporary tables (including number of
    tuples, pages and all visible flag).<br>
    My position is the following: while in most cases it should not be a
    problem, because users rarely create indexes or do analyze for
    temporary tables, <br>
    there can be situations when differences in data sets of global
    temporary tables in different backends can really be a problem.<br>
    Unfortunately I can not propose good solution for this problem. It
    is certainly possible to create some private (per-backend) cache for
    this metadata.<br>
    But it seems to requires changes in many places.<br></div></blockquote>=
<div><br></div><div>Yeah. I don&#39;t really like just sharing them but it&=
#39;s not that bad either.</div><div>=C2=A0</div><blockquote class=3D"gmail=
_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204=
,204);padding-left:1ex"><div bgcolor=3D"#FFFFFF">2. Your concerns about per=
formance penalty of global temp tables
    accessed through shared buffers comparing with local temp tables
    access through local buffers.<br>
    I think that this concern is not=C2=A0 actual any more because there is
    implementation of global temp tables using local buffers.<br>
    But my experiments doesn&#39;t show significant difference in access
    speed of shared and local buffers. As far as shared buffers are used
    to be much larger than local buffers,<br>
    there are more chances to hold all temp relation in memory without
    spilling it to the disk. In this case access to global temp table
    will be much faster comparing with access to <br>
    local temp tables.</div></blockquote><div><br></div><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 writ=
e-out due to lack of s_b space for dirty buffers.</div><div>=C2=A0</div><di=
v>&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></d=
iv><div>Who would ever do that?</div><div><br></div><blockquote class=3D"gm=
ail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,=
204,204);padding-left:1ex"><div bgcolor=3D"#FFFFFF">I forget or do not noti=
ce some of your questions, would you be so
    kind as to repeat them?<br></div></blockquote><div>=C2=A0</div><div>=C2=
=A0</div></div><div><br></div>-- <br><div dir=3D"ltr" class=3D"gmail_signat=
ure"><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=3D"ltr">=C2=A0Cra=
ig Ringer=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 <a href=3D"http://www.2ndQuadran=
t.com/" target=3D"_blank">http://www.2ndQuadrant.com/</a><br>=C2=A02ndQuadr=
ant - PostgreSQL Solutions for the Enterprise<br></div></div></div></div></=
div></div></div>

--000000000000c5a442059037d61d--