VACUUM and ANALYZE With Empty Tables

From: "Mark Dexter" <MDEXTER(at)dexterchaney(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: VACUUM and ANALYZE With Empty Tables
Date: 2004-11-23 22:14:31
Message-ID: 5E8F9F5B63726C48836757FE673B584E01215556@dcimail.dexterchaney.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2004-11-23 22:14:53 Re: Upcoming Changes to News Server ...
Previous Message Jim Seymour 2004-11-23 22:13:59 Re: Upcoming Changes to News Server ...