Hi!
Working with temp relations is some kind of bottleneck in Postgres, in my view.
There are no problems if you want to handle it from time to time, not arguing
that. But if you have to make a massive temp tables creation/deletion, you'll
soon step into a performance degradation.
To the best of my knowledge, there are two obvious problems:
1. We have to add or remove an entry in pg_class when temp table created and
deleted, resulting in "bloating" of pg_class. Thus, auto-vacuum is needed, but
it will acquire a lock, slowing things down.
2. Temp tables almost universally treated as regular tables. And this is 100%
correct and makes code much simpler. But also involve all the locking mechanism.
As for the first issue, I do not see how any significant improvements can be made,
unfortunately.
But for the second one: do we really need any lock for temp relations? AFAICU
they are backend only, apart from pg_class entries.
I do not have any particular solution for now, only some kind of concept: we can
put checks for temp relations in LockAcquire/LockRelease in order to skip
locking.
Do I miss something and idea is doomed or there are no visible obstacles here
and it's worth the effort to make a POC patch?
-- Best regards,
Maxim Orlov.