Обсуждение: 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