From: Bill Chandler <billybobc1210@yahoo.com>
To: pgsql-perform <pgsql-performance@postgresql.org>
Subject: Index bloat problem?
Date: Thu, 21 Apr 2005 10:00:18 -0700 (PDT)
Message-ID: <20050421170018.2998.qmail@web51403.mail.yahoo.com> (raw)
All,
Running PostgreSQL 7.4.2, Solaris.
Client is reporting that the size of an index is
greater than the number of rows in the table (1.9
million vs. 1.5 million). Index was automatically
created from a 'bigserial unique' column.
Database contains several tables with exactly the same
columns (including 'bigserial unique' column). This
is the only table where this index is out of line with
the actual # of rows.
Queries on this table take 40 seconds to retrieve 2000
rows as opposed to 1-2 seconds on the other tables.
We have been running 'VACUUM ANALYZE' very regularly.
In fact, our vacuum schedule has probably been
overkill. We have been running on a per-table basis
after every update (many per day, only inserts
occurring) and after every purge (one per day,
deleting a day's worth of data).
It is theoretically possible that at some time a
process was run that deleted all rows in the table
followed by a VACUUM FULL. In this case we would have
dropped/recreated our own indexes on the table but not
the index automatically created for the bigserial
column. If that happened, could that cause these
symptoms?
What about if an out-of-the-ordinary number of rows
were deleted (say 75% of rows in the table, as opposed
to normal 5%) followed by a 'VACUUM ANALYZE'? Could
things get out of whack because of that situation?
thanks,
Bill
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: billybobc1210@yahoo.com
Subject: Re: Index bloat problem?
In-Reply-To: <20050421170018.2998.qmail@web51403.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox