Horton

Re: [Proposal] Global temporary tables

Pavel Stehule <pavel.stehule@gmail.com>

View thread
From: Pavel Stehule <pavel.stehule@gmail.com>
To: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Cc: Robert Haas <robertmhaas@gmail.com>, 曾文旌(义从) <wenjing.zwj@alibaba-inc.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>, 蔡松露(子嘉) <zijia@taobao.com>, "Cai, Le" <le.cai@alibaba-inc.com>, 张广舟(明虚) <guangzhou.zgz@alibaba-inc.com>, 赵殿奎 <diankui.zdk@alibaba-inc.com>, 萧少聪(铁庵) <shaocong.xsc@alibaba-inc.com>
Date: 2019-10-25T17:00:06Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. PageAddItemExtended(): Add LP_UNUSED assertion.

  2. Remove temporary files after backend crash

  3. Fix comment in indexing.c

  4. Fix failure to ignore leftover temp tables after a server crash.

> >
> >> So except the limitation mentioned above (which I do not consider as
> critical) there is only one problem which was not addressed: maintaining
> statistics for GTT.
> >> If all of the following conditions are true:
> >>
> >> 1) GTT are used in joins
> >> 2) There are indexes defined for GTT
> >> 3) Size and histogram of GTT in different backends can significantly
> vary.
> >> 4) ANALYZE was explicitly called for GTT
> >>
> >> then query execution plan built in one backend will be also used for
> other backends where it can be inefficient.
> >> I also do not consider this problem as "show stopper" for adding GTT to
> Postgres.
> > I think that's *definitely* a show stopper.
> Well, if both you and Pavel think that it is really "show stopper", then
> this problem really has to be addressed.
> I slightly confused about this opinion, because Pavel has told me
> himself that 99% of users never create indexes for temp tables
> or run "analyze" for them. And without it, this problem is not a problem
> at all.
>
>
Users doesn't do ANALYZE on temp tables in 99%. It's true. But second fact
is so users has lot of problems. It's very similar to wrong statistics on
persistent tables. When data are small, then it is not problem for users,
although from my perspective it's not optimal. When data are not small,
then the problem can be brutal. Temporary tables are not a exception. And
users and developers are people - we know only about fatal problems. There
are lot of unoptimized queries, but because the problem is not fatal, then
it is not reason for report it. And lot of people has not any idea how fast
the databases can be. The knowledges of  users and app developers are sad
book.

Pavel