Re: Global temporary tables

On Fri, 16 Aug 2019 at 15:30, Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)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