Problem with corrupt index

Diogo Biazus <diogo@ikono.com.br> writes:

Hi everybody,
I have a table that requires periodical updates (every 5 seconds) in
more than one register.
The table is very small (about 7 registers) and it wont grow.
Sometimes the access to that table becomes very slow, and VACUUM dont
fix it, I have to REINDEX the table.
Anyone can tell me what should be the cause of this problem?

Indexes (currently) aren't affected by VACUUM. You have to REINDEX
periodically, depending on table activity.

-Doug

On Mon, Sep 30, 2002 at 02:41:16PM -0300, Diogo Biazus wrote:

Hi everybody,
I have a table that requires periodical updates (every 5 seconds) in
more than one register.
The table is very small (about 7 registers) and it wont grow.
Sometimes the access to that table becomes very slow, and VACUUM dont
fix it, I have to REINDEX the table.

Do you have an incrementing field in the table, and index it? If so,
your problem is that the index doesn't drop empty pages or mark them as
free, and updating it will make the index bigger; the only way to
reclaim space is by means of recreating the index.

If you only have 7 registers in the table, you don't need the index
anyway: a sequential scan is much faster. My advice would be to drop
the index. If you have a primary key you can't do that, and you'll have
to continue doing REINDEX and VACUUM periodically.

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"La virtud es el justo medio entre dos defectos" (Aristoteles)

Alvaro Herrera <alvherre@atentus.com> writes:

If you only have 7 registers in the table, you don't need the index
anyway: a sequential scan is much faster. My advice would be to drop
the index.

Mine too.

If you have a primary key you can't do that, and you'll have
to continue doing REINDEX and VACUUM periodically.

In any case, the fact you are having this problem suggests that you
aren't doing VACUUMs often enough on this table. If you've got seven
live rows and you update one of them every 5 seconds, then after an hour
you have seven live rows and 720 dead ones. Things are going to be a
good bit slower than before. If you wait days between vacuums then it
gets worse.

I'd recommend putting in a cron task to vacuum this particular table
hourly, or maybe even oftener.

regards, tom lane