Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hxS1f-0001kK-QL for pgsql-hackers@arkaria.postgresql.org; Tue, 13 Aug 2019 08:19:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hxS1e-0002Us-IJ for pgsql-hackers@arkaria.postgresql.org; Tue, 13 Aug 2019 08:19:26 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hxS1e-0002UK-17 for pgsql-hackers@lists.postgresql.org; Tue, 13 Aug 2019 08:19:26 +0000 Received: from cyclops.postgrespro.ru ([93.174.131.138] helo=mail.postgrespro.ru) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1hxS1a-0003S1-Ik for pgsql-hackers@postgresql.org; Tue, 13 Aug 2019 08:19:24 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id 7AC5321C248F; Tue, 13 Aug 2019 11:19:20 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from [192.168.27.200] (gw.postgrespro.ru [93.174.131.141]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id AF0A521C1CED; Tue, 13 Aug 2019 11:19:19 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1565684360; bh=VoaHVGmxgnxpYKwQKzgAXrLCF6h5F4pQLaQNjUggF84=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=OLS6N+d5LEQErSky2fOmwSoZAchieam0IVkVsiQESsWIJ+ZOqNL+w5vhMIZteRwWZ jz9xIRmoFAj+Qbrh4dcm09BlHjMEJiPALkN8E6BLPnSesSZ+rQQMf1GAzfc3NLg+l2 r5fWi0HkFUC96biYIuc7b5JfNgwEkf0Q8LvlYiz4= Subject: Re: Global temporary tables To: Craig Ringer , Pavel Stehule Cc: PostgreSQL Hackers References: <73954ab7-44d3-b37b-81a3-69bdcbb446f7@postgrespro.ru> <0baf9e02-23b3-f3c1-6fe9-fd35aee6fee1@postgrespro.ru> <8e0b539b-4b64-d2b2-08a1-ddaa3c7e7747@postgrespro.ru> <8b4bdfa6-7a90-95e2-1fd0-243767e5b357@postgrespro.ru> <94a19e7b-d99a-110c-8e5b-f5068682b474@postgrespro.ru> <614c1e9f-c0e6-332c-ba2b-85a7e1efb956@postgrespro.ru> <5a711f75-e999-a809-60e1-c74c8c9e7915@postgrespro.ru> From: Konstantin Knizhnik Message-ID: <21f047d6-bb2d-950c-2aff-2fee301d5851@postgrespro.ru> Date: Tue, 13 Aug 2019 11:19:19 +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: Content-Type: multipart/alternative; boundary="------------E4580E6CACD8FE6E2DA0C73A" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------E4580E6CACD8FE6E2DA0C73A Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 13.08.2019 8:34, Craig Ringer wrote: > On Tue, 13 Aug 2019 at 00:47, Pavel Stehule > wrote: > > But Postgres is not storing this information now anywhere else > except statistic, isn't it? > > > not only - critical numbers are reltuples, relpages from pg_class > > > That's a very good point. relallvisible too. How's the global temp > table impl handling that right now, since you won't be changing the > pg_class row? AFAICS relpages doesn't need to be up to date (and > reltuples certainly doesn't) so presumably you're just leaving them as > zero? As far as I understand relpages and reltuples are set only when you perform "analyze" of the table. > > What happens right now if you ANALYZE or VACUUM ANALYZE a global temp > table? Is it just disallowed? No, it is not disallowed now. It updates the statistic and also fields in pg_class which are shared by all backends. So all backends will now build plans according to this statistic. Certainly it may lead to not so efficient plans if there are large differences in number of tuples stored in this table in different backends. But seems to me critical mostly in case of presence of indexes for temporary table. And it seems to me that users are created indexes for temporary tables even rarely than doing analyze for them. > > I'll need to check, but I wonder if periodically updating those fields > in pg_class impacts logical decoding too. Logical decoding must treat > transactions with catalog changes as special cases where it creates > custom snapshots and does other expensive additional work. > (See ReorderBufferXidSetCatalogChanges in reorderbuffer.c and its > callsites). We don't actually need to know relpages and reltuples > during logical decoding. It can probably ignore relfrozenxid > and relminmxid changes too, maybe even pg_statistic changes though I'd > be less confident about that one. > > At some point I need to patch in a bunch of extra tracepoints and do > some perf tracing to see how often we do potentially unnecessary > snapshot related work in logical decoding. Temporary tables (both local and global) as well as unlogged tables are not subject of logical replication, aren't them? -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company --------------E4580E6CACD8FE6E2DA0C73A Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

On 13.08.2019 8:34, Craig Ringer wrote:
On Tue, 13 Aug 2019 at 00:47, Pavel Stehule <pavel.stehule@gmail.com> wrote:
 
But Postgres is not storing this information now anywhere else except statistic, isn't it?

not only - critical numbers are reltuples, relpages from pg_class

That's a very good point. relallvisible too. How's the global temp table impl handling that right now, since you won't be changing the pg_class row? AFAICS relpages doesn't need to be up to date (and reltuples certainly doesn't) so presumably you're just leaving them as zero?
As far as I understand relpages and reltuples are set only when you perform "analyze" of the table.


What happens right now if you ANALYZE or VACUUM ANALYZE a global temp table? Is it just disallowed?

No, it is not disallowed now.
It updates the statistic and also fields in pg_class which are shared by all backends.
So all backends will now build plans according to this statistic. Certainly it may lead to not so efficient plans if there are large differences in number of tuples stored in this table in different backends.
But seems to me critical mostly in case of presence of indexes for temporary table. And it seems to me that users are created indexes for temporary tables even rarely than doing analyze for them.

I'll need to check, but I wonder if periodically updating those fields in pg_class impacts logical decoding too. Logical decoding must treat transactions with catalog changes as special cases where it creates custom snapshots and does other expensive additional work. (See ReorderBufferXidSetCatalogChanges in reorderbuffer.c and its callsites). We don't actually need to know relpages and reltuples during logical decoding. It can probably ignore relfrozenxid and relminmxid changes too, maybe even pg_statistic changes though I'd be less confident about that one.

At some point I need to patch in a bunch of extra tracepoints and do some perf tracing to see how often we do potentially unnecessary snapshot related work in logical decoding.

Temporary tables (both local and global) as well as unlogged tables are not subject of logical replication, aren't them?


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