Re: Performance issue on temporary relations

"=?utf-8?B?56ug5pmo5pum?=" <zhangchenxi(at)halodbtech(dot)com> writes:
> Yes. ON COMMIT DELETE temp table will be truncated at every commit.
> But if we can control that only accessed temp tables will be truncated
> may be better. When an temp tables accessed, it will be stored in in_use
> list, and when on commit, it will check the in_use list if a truncation needed
> to happen on that temp table, and reduce unnecessary truncations.

The problem with this proposal is that you are ignoring the cost
of maintaining that list. That's going to slow down every operation
on temp tables, and in common scenarios (where a truncate would have
to happen anyway) applications will get zero benefit for the extra
overhead. So I'm not excited about adding complexity and long-term
maintenance burden to do this.

BTW, it appears to me that doing it this way is O(N^2) in the number
of active temp tables. So it's not hard to believe that the patch
as-presented would actually be a fairly serious performance drag for
some use cases with lots of temp tables. There are certainly ways
we could do better than that (hash table, bloom filter, etc) but
there would be even more engineering effort needed.

And it's also fair to wonder if you've found all the places where we'd
need to mark temp tables dirty, and what we'd need to do to be sure
we didn't introduce any oops-forgot-to-mark-it-dirty bugs in future.
If you search the commit log for mentions of bugs associated with
ON COMMIT DELETE, you'll find quite a few, which is one reason why
I'm allergic to adding more complexity here.

In short, I think you're underestimating the engineering costs and
overestimating the benefit of doing this.

regards, tom lane