so 2. 11. 2019 v 8:18 odesílatel Julien Rouhaud <rjuju...@gmail.com> napsal:
> On Sat, Nov 2, 2019 at 6:31 AM Pavel Stehule <pavel.steh...@gmail.com> > wrote: > > > > pá 1. 11. 2019 v 17:09 odesílatel Konstantin Knizhnik < > k.knizh...@postgrespro.ru> napsal: > >> > >> On 01.11.2019 18:26, Robert Haas wrote: > >> > On Fri, Nov 1, 2019 at 11:15 AM Konstantin Knizhnik > >> > <k.knizh...@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 I don't think - the extensions can use UNION and the content will be same as caches used by planner.
