Re: [Proposal] Global temporary tables
От | Julien Rouhaud |
---|---|
Тема | Re: [Proposal] Global temporary tables |
Дата | |
Msg-id | CAOBaU_YG=54byWLUJopeokDJR_m+8GkMtN1WC=+iC2jeOLKmRg@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: [Proposal] Global temporary tables (Pavel Stehule <pavel.stehule@gmail.com>) |
Список | pgsql-hackers |
On Sat, Nov 2, 2019 at 8:23 AM Pavel Stehule <pavel.stehule@gmail.com> wrote: > > so 2. 11. 2019 v 8:18 odesílatel Julien Rouhaud <rjuju123@gmail.com> napsal: >> >> 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 inbackend'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. WhatI 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 > > > I don't think - the extensions can use UNION and the content will be same as caches used by planner. Yes, I agree that changing pg_statistics to be a view as you showed would fix the problem. I was answering Konstantin's point: >> >> 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. which is IMHO a wrong assumption.
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Michael PaquierДата:
Сообщение: Re: update ALTER TABLE with ATTACH PARTITION lock mode (docs)
Следующее
От: Adrien NayratДата:
Сообщение: Re: Adding percentile metrics to pg_stat_statements module