Re: Global temporary tables - Mailing list pgsql-hackers

On Tue, 13 Aug 2019 at 21:50, Konstantin Knizhnik <k.knizhnik@postgrespro.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