Do you observe PostgreSQL TEMP TABLE performance degradation since 8.3?

Question

My app uses temporary tables in PostgreSQL. With new, 8.4 release I tested its performance compared to old 8.2 release and I observed that temp tables are ten times slower!

Test of 8.3 release show that it was slower in 8.3 too. I compared configs of all bases and they are similar. All bases work on the same server. While my app uses JDBC driver I used Jython to test it:

import time
import traceback
import sys

from java.sql import DriverManager
from java.lang import Class

Class.forName("org.postgresql.Driver")

def test_bench(db, temp):
    if temp:
     temp_str = ' TEMP '
     temp_desc = 'temp   '
    else:
     temp_str = ' '
     temp_desc = 'regular'
    try:
     c = db.createStatement()
     c.execute("CREATE %s TABLE test_table_md_speed(id serial primary key, txt varchar(100))" % temp_str)
     cnt = '?'
     try:
      t0 = time.time()
      for i in range(1000):
       c.execute("INSERT INTO test_table_md_speed(txt) VALUES ('ala ma %d kota')" % i)
      t2 = time.time()
      rs = c.executeQuery("SELECT COUNT(*) AS ile FROM test_table_md_speed")
      while (rs.next()):
       cnt = rs.getString(1)
      print("%s\ttime: %7.3f [s]\tcnt: %s" % (temp_desc, (t2-t0), cnt))
     finally:
      c.execute("DROP TABLE test_table_md_speed")
     c.close()
    except:
     print("\nthere were errors!")
     s = traceback.format_exc()
     sys.stderr.write("%s\n" % (s))

def test_db(db_url, usr, passwd):
    print("\n\n--------------")
    db = DriverManager.getConnection(db_url, usr, passwd)
    try:
     c = db.createStatement()
     rs = c.executeQuery("SELECT version()")
     while (rs.next()):
      print('ver: %s' % (rs.getString(1)))
     test_bench(db, 0)
     test_bench(db, 1)
    finally:
     db.close()

test_db('jdbc:postgresql://db-test64:5432/db_stable?stringtype=unspecified', 'postgres', 'postgres')
test_db('jdbc:postgresql://db-test64:5434/db_stable?stringtype=unspecified', 'postgres', 'postgres')
test_db('jdbc:postgresql://db-test64:5435/db_stable?stringtype=unspecified', 'postgres', 'postgres')

I also wrote similar test using ActivePython and odbc from win32 and this program shows the same results. My results:

C:\tools\pyscripts\>jython jdbc_pg_bench.py
--------------
ver: PostgreSQL 8.2.12 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
regular time:  12.016 [s]       cnt: 1000
temp    time:   1.187 [s]       cnt: 1000
--------------
ver: PostgreSQL 8.3.6 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
regular time:  11.922 [s]       cnt: 1000
temp    time:  10.516 [s]       cnt: 1000
--------------
ver: PostgreSQL 8.4.0 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21), 64-bit
regular time:  13.375 [s]       cnt: 1000
temp    time:  13.609 [s]       cnt: 1000

Do you observe the same degradation of speed of temp tables in 8.3 and 8.4 releases? Is there any information about changes in TEMP tables behaviour?

EDIT

I installed PostgreSQL 8.2 and 8.4 on my Windows machine, tested it with 10 000 inserts and it seems that 8.2 was much faster with temp tables:

ver: PostgreSQL 8.2.11 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)
regular time:  40.672 [s]       cnt: 10000
temp    time:  14.859 [s]       cnt: 10000

ver: PostgreSQL 8.4.0, compiled by Visual C++ build 1400, 32-bit
regular time:  56.860 [s]       cnt: 10000
temp    time:  49.110 [s]       cnt: 10000

Answer 1

I checked it - but I cannot repeat your results. On my computer the speed of insert to temp table is similar for all version what I tested. It some strange so insert on 8.2.12 on your comp is 10 times faster than insert to regular table. On my computer insert to temp table is about 1.5-2 times faster than insert to regular table.

Answer 2

8.3 introduced this change: (From the commit log on the wiki)

2007-06-03 13:05 tgl Create a GUC parameter temp_tablespaces that allows selection of the tablespace(s) in which to store temp tables and temporary files. This is a list to allow spreading the load across multiple tablespaces (a random list element is chosen each time a temp object is to be created). Temp files are not stored in per-database pgsql_tmp/ directories anymore, but per-tablespace directories.

Release notes give credit to "Jaime Casanova, Albert Cervera, Bernd Helmle"

---

There is a report to the pgsql-performance list that might be related. The original poster identified that changing their kernel at the same time was more likely the cause, however a post from Tom Lane includes:

"So I suspect that the slowdown you saw comes from making a larger number of catalog updates in 8.3;"

---

Your comment that the 8.2 config was copied for 8.3... the default autovacuum setting changed from off to on. Maybe your temp tables were being vacuumed in 8.3 but not in 8.2?