Re: Insertion to temp table deteriorating over time - Mailing list pgsql-performance

Here's the output of "VACUUM VERBOSE pg_class".  I think it looks fine.  I even did it three times in a row, each about 10 minutes apart, just to see what was changing:

 

--------------------

INFO:  vacuuming "pg_catalog.pg_class"
INFO:  index "pg_class_oid_index" now contains 3263 row versions in 175 pages
DETAIL:  5680 index row versions were removed.
150 index pages have been deleted, 136 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_class_relname_nsp_index" now contains 3263 row versions in 1301
 pages
DETAIL:  5680 index row versions were removed.
822 index pages have been deleted, 734 are currently reusable.
CPU 0.01s/0.01u sec elapsed 0.03 sec.
INFO:  "pg_class": removed 5680 row versions in 109 pages
DETAIL:  CPU 0.00s/0.01u sec elapsed 0.04 sec.
INFO:  "pg_class": found 5680 removable, 3263 nonremovable row versions in 625 p
ages
DETAIL:  0 dead row versions cannot be removed yet.
There were 23925 unused item pointers.
0 pages are entirely empty.
CPU 0.02s/0.04u sec elapsed 0.10 sec.
VACUUM

--------------------

INFO:  vacuuming "pg_catalog.pg_class"
INFO:  index "pg_class_oid_index" now contains 3263 row versions in 175 pages
DETAIL:  24 index row versions were removed.
150 index pages have been deleted, 150 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_class_relname_nsp_index" now contains 3263 row versions in 1301
 pages
DETAIL:  24 index row versions were removed.
822 index pages have been deleted, 822 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_class": removed 24 row versions in 2 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_class": found 24 removable, 3263 nonremovable row versions in 625 pag
es
DETAIL:  0 dead row versions cannot be removed yet.
There were 29581 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

--------------------

INFO:  vacuuming "pg_catalog.pg_class"
INFO:  index "pg_class_oid_index" now contains 3263 row versions in 175 pages
DETAIL:  150 index pages have been deleted, 150 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_class_relname_nsp_index" now contains 3263 row versions in 1301
 pages
DETAIL:  822 index pages have been deleted, 822 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_class": found 0 removable, 3263 nonremovable row versions in 625 page
s
DETAIL:  0 dead row versions cannot be removed yet.
There were 29605 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

--------------------

 

The one thing that seems to be steadily increasing is the number of unused item pointers.  Not sure if that's normal.  I should also point out that SELECT statements are not experiencing the same degradation as the INSERTs to the temp table.  SELECTs are performing just as well now (24 hours since restarting the connection) as they did immediately after restarting the connection.  INSERTs to the temp table are 5 times slower now than they were 24 hours ago.

 

I wonder if the problem has to do with a long running ODBC connection.

 

Steve