Re: Global temporary tables

On 16.08.2019 9:25, Craig Ringer wrote:
> On Tue, 13 Aug 2019 at 21:50, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru <mailto:k(dot)knizhnik(at)postgrespro(dot)ru>> wrote:
>
>> As far as I understand relpages and reltuples are set only
>> when you perform "analyze" of the table.
>>
>>
>> Also autovacuum's autoanalyze.
>
> When it happen?
> I have created normal table, populated it with some data and then
> wait several hours but pg_class was not updated for this table.
>
>
>
> heap_vacuum_rel() in src/backend/access/heap/vacuumlazy.c below
>
>      * Update statistics in pg_class.
>
> which I'm pretty sure is common to explicit vacuum and autovacuum. I
> haven't run up a test to verify 100% but most DBs would never have
> relpages etc set if autovac didn't do it since most aren't explicitly
> VACUUMed at all.

Sorry, I already understood it myself.
But to make vacuum process the table it is necessary to remove or update
some rows in it.
It seems to be yet another Postgres problem, which was noticed by
Darafei Praliaskouski some time ago: append-only tables are never
proceeded by autovacuum.

>
> I thought it was done when autovac ran an analyze, but it looks like
> it's all autovac. Try setting very aggressive autovac thresholds and
> inserting + deleting a bunch of tuples maybe.
>
> I attach to this mail slightly refactored versions of this patches
> with fixes of issues reported in your review.
>
>
> Thanks.
>
> Did you have a chance to consider my questions too? I see a couple of
> things where there's no patch change, which is fine, but I'd be
> interested in your thoughts on the question/issue in those cases.
>
>
Sorry, may be I didn't notice some your questions. I have a filling that
I have replied on all your comments/questions.
Right now I reread all this thread and see two open issues:

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.

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. But the fact is that right now in the most frequent
scenario of temp table usage:

    SELECT ... FROM PersistentTable INTO TempTable WHERE ...;
    SELECT * FROM TempTable;

local temp table are more efficient than global temp table access
through shared buffer.
I think it is explained by caching and eviction policies.
In case of pulling all content of temp table in memory (pg_prewarm)
global temp table with shared buffers becomes faster.

I forget or do not notice some of your questions, would you be so kind
as to repeat them?

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company