Hi,
On 11.08.2019 10:14, Pavel Stehule wrote:
Hi
There is one more problem with global temporary tables for which I
do not know good solution now: collecting statistic.
As far as each backend has its own data, generally them may need
different query plans.
Right now if you perform "analyze table" in one backend, then it
will affect plans in all backends.
It can be considered not as bug, but as feature if we assume that
distribution if data in all backens is similar.
But if this assumption is not true, then it can be a problem.
Last point is probably the most difficult issue and I think about it
years.
I have a experience with my customers so 99% of usage temp tables is
without statistics - just with good information only about rows. Only
few customers know so manual ANALYZE is necessary for temp tables
(when it is really necessary).
Sharing meta data about global temporary tables can real problem -
probably not about statistics, but surely about number of pages and
number of rows.
But Postgres is not storing this information now anywhere else except
statistic, isn't it?
There was proposal to cache relation size, but it is not implemented
yet. If such cache exists, then we can use it to store local information
about global temporary tables.
So if 99% of users do not perform analyze for temporary tables, then
them will not be faced with this problem, right?
There are two requirements:
a) we need some special meta data for any instance (per session) of
global temporary table (row, pages, statistics, maybe multicolumn
statistics, ...)
b) we would not to use persistent global catalogue (against catalogue
bloating)
I see two possible solution:
1. hold these data only in memory in special buffers
2. hold these data in global temporary tables - it is similar to
normal tables - we can use global temp tables for metadata like
classic persistent tables are used for metadata of classic persistent
tables. Next syscache can be enhanced to work with union of two system
tables.
I prefer @2 because changes can be implemented on deeper level.
Sharing metadata for global temp tables (current state if I understand
well) is good enough for develop stage, but It is hard to expect so it
can work generally in production environment.
I think that it not possible to assume that temporary data will aways
fir in memory.
So 1) seems to be not acceptable solution.
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
