Thread: Temp tables performance question
I have a question about performance issues related to temporary tables. IIRC temporary tables were implemented as ordinary tables with some pre/post-processing to give them unique names so that they would not clash with similar-named tables from other sessions. Is this all that is done or has some work been done to improve their performance further? I'm mainly interested in INSERT performance as this is the area that is much slower than other operations. IMHO temporary tables could be made significantly faster than "ordinary" as they are only accessed inside one session and thus have no need for locking or even WAL as they could do with max 2 copies of the same row the other of which can be discarded at end of transaction thereby making it possible to provide much faster insert behaviour. --------------------- Hannu
> I have a question about performance issues related to temporary tables. > > IIRC temporary tables were implemented as ordinary tables with some > pre/post-processing to give them unique names so that they would not > clash with similar-named tables from other sessions. Right. > > Is this all that is done or has some work been done to improve their > performance further? > > I'm mainly interested in INSERT performance as this is the area that is > much slower than other operations. So you are not saying that INSERT on temp tables is any slower than ordinary tables, just that you think there is a way to make temp tables faster. My guess is that WAL is going to make INSERT's poor performance a non-issue. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Hannu Krosing <hannu@tm.ee> writes: > I have a question about performance issues related to temporary tables. > IIRC temporary tables were implemented as ordinary tables with some > pre/post-processing to give them unique names so that they would not > clash with similar-named tables from other sessions. Right, there's basically no performance difference at all from ordinary tables. It'd be possible to have them go through the "local buffer manager" for their entire lives, rather than only for the transaction in which they are created, as happens for ordinary tables. This would avoid at least some shared-buffer-manipulation overhead. I'm not sure it'd buy a whole lot, but it probably wouldn't take much work to make it happen, either. I think it would be folly to try to make them use a different smgr or avoid WAL; that'd require propagating differences between ordinary and temp tables into way too many places. regards, tom lane
> It'd be possible to have them go through the "local buffer manager" > for their entire lives, rather than only for the transaction in which > they are created, as happens for ordinary tables. This would avoid > at least some shared-buffer-manipulation overhead. I'm not sure it'd > buy a whole lot, but it probably wouldn't take much work to make it > happen, either. > > I think it would be folly to try to make them use a different smgr or > avoid WAL; that'd require propagating differences between ordinary and > temp tables into way too many places. Yes, temp table optimization hardly seems worth it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Tom Lane wrote: > > Hannu Krosing <hannu@tm.ee> writes: > > I have a question about performance issues related to temporary tables. > > IIRC temporary tables were implemented as ordinary tables with some > > pre/post-processing to give them unique names so that they would not > > clash with similar-named tables from other sessions. > > Right, there's basically no performance difference at all from ordinary > tables. > > It'd be possible to have them go through the "local buffer manager" > for their entire lives, rather than only for the transaction in which > they are created, as happens for ordinary tables. This would avoid > at least some shared-buffer-manipulation overhead. I'm not sure it'd > buy a whole lot, but it probably wouldn't take much work to make it > happen, either. I was hoping that at least fsync()'s could be avoided on temp tables even without -F. Other kinds of improvemants should be possible too, due to the essentially non-transactional nature of temp tables. > I think it would be folly to try to make them use a different smgr or > avoid WAL; that'd require propagating differences between ordinary and > temp tables into way too many places. Does using a different storage manager really need propagating differences in other places than the storage manager code ? In an ideal world it should not be so ;) ----------- Hannu