Subject: Re: [Proposal] Global temporary tables
To: Julien Rouhaud <rjuju123@gmail.com>,
 Pavel Stehule <pavel.stehule@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>, =?UTF-8?B?5pu+5paH5peMKOS5ieS7jik=?=
 <wenjing.zwj@alibaba-inc.com>,
 PostgreSQL Hackers <pgsql-hackers@postgresql.org>, =?UTF-8?B?6JSh5p2+6Zyy?=
 =?UTF-8?B?KOWtkOWYiSk=?= <zijia@taobao.com>, "Cai, Le"
 <le.cai@alibaba-inc.com>, =?UTF-8?B?5byg5bm/6IifKOaYjuiZmik=?=
 <guangzhou.zgz@alibaba-inc.com>, =?UTF-8?B?6LW15q6/5aWO?=
 <diankui.zdk@alibaba-inc.com>, =?UTF-8?B?6JCn5bCR6IGqKOmTgeW6tSk=?=
 <shaocong.xsc@alibaba-inc.com>
References: <1A1A6EDC-D0EC-47B0-BD21-C2ACBAEA65E4@alibaba-inc.com>
 <0ef6f482-11ce-a99f-207e-26fa3f2f56db@postgrespro.ru>
 <CA+Tgmob_Fg9PfNjjOReJtAE4B5gc8+UqHh+tztJ_C+iRN4KHSw@mail.gmail.com>
 <24099f4e-742c-e92b-729c-77bac1ca80d4@postgrespro.ru>
 <CAFj8pRBo8YZ=bJESqf2fkxGL8Bq2Fs7=SjxisS7UhXHh6yPSfA@mail.gmail.com>
 <80cf30c2-ebd5-a568-9f62-a970e4d91326@postgrespro.ru>
 <CA+TgmoZFWaND4PpT_CJbeu6VZGZKi2rrTuSTL-Ykd97fexTN-w@mail.gmail.com>
 <136fcaf5-2841-6435-7640-be04c4891143@postgrespro.ru>
 <CAFj8pRCt8FbZ107PAapJmXSOxm1uR79KYgvz1Ca6CWYZmepneg@mail.gmail.com>
 <CAOBaU_a-tSJxANxooMh7B4AGsQ2Z-sGdOg39Ma+pyK-Dj80QXw@mail.gmail.com>
From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Message-ID: <15c655f7-c03e-6eb5-6e75-0776fae7d4f5@postgrespro.ru>
Date: Sat, 2 Nov 2019 18:09:42 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.9.0
MIME-Version: 1.0
In-Reply-To: 
 <CAOBaU_a-tSJxANxooMh7B4AGsQ2Z-sGdOg39Ma+pyK-Dj80QXw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk


On 02.11.2019 10:19, Julien Rouhaud wrote:
> On Sat, Nov 2, 2019 at 6:31 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> pá 1. 11. 2019 v 17:09 odesílatel Konstantin Knizhnik <k.knizhnik@postgrespro.ru> napsal:
>>> On 01.11.2019 18:26, Robert Haas wrote:
>>>> On Fri, Nov 1, 2019 at 11:15 AM Konstantin Knizhnik
>>>> <k.knizhnik@postgrespro.ru> wrote:
>>>>> It seems to me that I have found quite elegant solution for per-backend statistic for GTT: I just inserting it in backend's catalog cache, but not in pg_statistic table itself.
>>>>> To do it I have to add InsertSysCache/InsertCatCache functions which insert pinned entry in the correspondent cache.
>>>>> I wonder if there are some pitfalls of such approach?
>>>> That sounds pretty hackish. You'd have to be very careful, for
>>>> example, that if the tables were dropped or re-analyzed, all of the
>>>> old entries got removed --
>>> I have checked it:
>>> - when table is reanalyzed, then cache entries are replaced.
>>> - when table is dropped, then cache entries are removed.
>>>
>>>> and then it would still fail if any code
>>>> tried to access the statistics directly from the table, rather than
>>>> via the caches. My assumption is that the statistics ought to be
>>>> stored in some backend-private data structure designed for that
>>>> purpose, and that the code that needs the data should be taught to
>>>> look for it there when the table is a GTT.
>>> Yes, if you do "select * from pg_statistic" then you will not see
>>> statistic for GTT in this case.
>>> But I do not think that it is so critical. I do not believe that anybody
>>> is trying to manually interpret values in this table.
>>> And optimizer is retrieving statistic through sys-cache mechanism and so
>>> is able to build correct plan in this case.
>>
>> Years ago, when I though about it, I wrote patch with similar design. It's working, but surely it's ugly.
>>
>> I have another idea. Can be pg_statistics view instead a table?
>>
>> Some like
>>
>> SELECT * FROM pg_catalog.pg_statistics_rel
>> UNION ALL
>> SELECT * FROM pg_catalog.pg_statistics_gtt();
>>
>> Internally - when stat cache is filled, then there can be used pg_statistics_rel and pg_statistics_gtt() directly. What I remember, there was not possibility to work with queries, only with just relations.
> It'd be a loss if you lose the ability to see the statistics, as there
> are valid use cases where you need to see the stats, eg. understanding
> why you don't get the plan you wanted.  There's also at least one
> extension [1] that allows you to backup and use restored statistics,
> so there are definitely people interested in it.
>
> [1]: https://github.com/ossc-db/pg_dbms_stats
It seems to have completely no sense to backup and restore statistic for 
temporary tables which life time is limited to life time of backend,
doesn't it?