The actual application does not have to perform this statement since, as you suggested; it keeps track of what got loaded. However, the table has to go thru a de-duplication process because bulk load is utilized to load the potentially large number (millions) of rows. All indexes were dropped for the bulk load. This de-duplication procedure starts with a SELECT statement that identifies duplicate rows. This is the original SELECT that never returned. Later on I used the SELECT COUNT(*) to see if somehow my original SELECT had something to do with the hang and I found that this simple query hung as well.
The only way I could avoid getting into this stage was to perform a VACUUM FULL on the table before the bulk load. I would prefer not using a full vacuum every time due to the exclusive access to the table and time it requires. The plain VACUUM did not work. Regards, Jozsef -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Adam Tauno Williams Sent: Sunday, July 22, 2007 1:33 PM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Simple select hangs while CPU close to 100% > I'm having this very disturbing problem. I got a table with about > 100,000 rows in it. Our software deletes the majority of these rows > and then bulk loads another 100,000 rows into the same table. All this > is happening within a single transaction. I then perform a simple > "select count(*) from ..." statement that never returns. In the mean COUNT(*) is always slow; but either way if the process is deleting and then adding records, can't you just keep track of how may records you loaded [aka count++] rather than turning around and asking the database before any statistics have had a chance to be updated. > time, the backend Postgres process is taking close to 100% of the > CPU. The hang-up does not always happen on the same statement but > eventually it happens 2 out of 3 times. If I dump and then restore the > schema where this table resides the problem is gone until the next > time we run through the whole process of deleting, loading and > querying the table. > There is no other activity in the database. All requested locks are > granted. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
