To: "Steven Flatt" <steven.flatt@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Insertion to temp table deteriorating over time 
In-reply-to: <357fa7590612151026t4ae033f6w5367cac9d0b67064@mail.gmail.com> 
References: <357fa7590612130844q58ef3ca5q31e164f9b006e0fc@mail.gmail.com>
	<9315.1166030958@sss.pgh.pa.us>
	<357fa7590612131517q375fc0by50cb4257bcce7a94@mail.gmail.com>
	<13032.1166052447@sss.pgh.pa.us>
	<357fa7590612141240t55e70695q770a05a68a67536c@mail.gmail.com>
	<25209.1166131396@sss.pgh.pa.us>
	<357fa7590612150821i3eca3f66ge2ced1ee20fa2dac@mail.gmail.com>
	<11651.1166202586@sss.pgh.pa.us>
	<357fa7590612150955g1b9c733duaf000c23d28aa8af@mail.gmail.com>
	<12508.1166206006@sss.pgh.pa.us>
	<357fa7590612151026t4ae033f6w5367cac9d0b67064@mail.gmail.com>
Comments: In-reply-to "Steven Flatt" <steven.flatt@gmail.com>
	message dated "Fri, 15 Dec 2006 13:26:31 -0500"
Date: Fri, 15 Dec 2006 14:09:48 -0500
Message-ID: <13947.1166209788@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Steven Flatt" <steven.flatt@gmail.com> writes:
> Are the removable rows in pg_class even an issue?  So what if 5000-6000 dead
> tuples are generated every hour then vacuumed?  Performance continues to
> steadily decline over a few days time.  Memory usage does not appear to be
> bloating.  Open file handles remain fairly fixed.  Is there anything else I
> can monitor (perhaps something to do with the odbc connection) that I could
> potentially correlate with the degrading performance?

At this point I think the most productive thing for you to do is to try
to set up a self-contained test case that reproduces the slowdown.  That
would allow you to poke at it without disturbing your production system,
and would let other people look at it too.  From what you've said, I'd
try a simple little program that inserts some data into a temp table,
truncates the table, and repeats, as fast as it can, using the same SQL
commands as your real code and similar but dummy data.  It shouldn't
take long to observe the slowdown if it occurs.  If you can't reproduce
it in isolation then we'll know that some other part of your application
environment is contributing to the problem; if you can, I'd be happy to
look at the test case with gprof or oprofile and find out exactly what's
going on.

			regards, tom lane