Re: Insertion to temp table deteriorating over time

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

On 12/14/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Steven Flatt" <steven(dot)flatt(at)gmail(dot)com> writes:
> > Regarding your other email -- interesting -- but we are vacuuming
> pg_class
> > every hour. So I don't think the answer lies there...
>
> That's good, but is the vacuum actually accomplishing anything? I'm
> wondering if there's also a long-running transaction in the mix.
> Try a manual "VACUUM VERBOSE pg_class;" after the thing has slowed down,
> and see what it says about removable and nonremovable rows.
>
> regards, tom lane
>