We use a development environment that works with Postgres via ODBC and uses cursors to insert and update rows in Postgres tables. I'm using Postgres version 7.4.5.
I have a test program that reads 34,000 rows from an external file and inserts them into a Postgres table. Under normal circumstances, it takes about 1 minute to complete the test. In troubleshooting a performance problem, I have discovered the following:
A. If I TRUNCATE or DELETE all of the rows in the table and then run VACUUM or ANALYZE on the empty table, the test program takes over 15 minutes to complete (i.e., 15X performance drop).
B. If I drop and create the table without using VACUUM or ANALYZE, it takes about 1 minute to complete the test program.
C. If I insert 94 or more rows into the table and then run VACUUM or ANALYZE, it takes 1 minute to complete the test program.
D. If I insert 93 or fewer rows into the table and then run VACUUM or ANALYZE, it takes over 15 minutes to complete the test.
D. If the test program is running slowly, I can speed it up to normal speed by running ANALYZE <table> from another database session.
My concern about this is as follows. Our application uses a number of "work" tables that will have many rows inserted into them during a process (e.g., a user running a report) and then the rows will be deleted once the process is over. (I don't think we can use TEMPORARY tables because the tables need to be seen outside of the current database session.)
If we routinely run VACUUM or VACUUM ANALYZE (e.g., nightly), these work tables will normally be empty when the VACUUM is run. So it would appear from the testing above that they will experience performance problems when inserting large numbers of rows through our application.
Is there some easy way around this problem? If there a way to force VACUUM or ANALYZE to optimize for a set number of rows even if the table is empty when it is run? Thanks for your help. Mark