Temp tables performance question

Zeugswetter Andreas SB wrote:

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.

I am somewhat confused. What does the max 2 copies issue have to do with
inserts, where you only have one copy of the row anyway ?

You may want to rollback the transaction;

--------------
Hannu

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.

I am somewhat confused. What does the max 2 copies issue have to do with
inserts, where you only have one copy of the row anyway ?

Andreas

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, Pennsylvania 19026

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.

I do not think that WAL in its first version can speed anything up,
it will rather slow things down.
I think that insert performance should be somewhere near "\copy"
performance which is not so bad now.
Thus it probably could be improved for both regular and temp tables.

Andreas

PS: I am off for a week now

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, Pennsylvania 19026

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