index files still growing

carex <bk226157@skynet.be> writes:

I am using Postgresql 7.0.2
The problem is that my index files are still growing.
( and I vacuum each night !! )
My solution is to DROP & CREATE my indexes files once
a week.

Is this a known problem with 7.0.2 ??

It's a known problem in general. VACUUM doesn't vacuum indexes. :(

Is it solved in 7.1.3 ??

No.

Your solution is currently the only one available. Not pretty, but it
works.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

Doug McNaught <doug@wireboard.com> writes:

My solution is to DROP & CREATE my indexes files once
a week.

Your solution is currently the only one available. Not pretty, but it
works.

REINDEX is a marginally prettier answer; at least you don't have to
remember the index parameters. Shrinking indexes on-the-fly is still
on the TODO list.

regards, tom lane

On Wed, 24 Oct 2001, Tom Lane wrote:

Doug McNaught <doug@wireboard.com> writes:

My solution is to DROP & CREATE my indexes files once
a week.

Your solution is currently the only one available. Not pretty, but it
works.

REINDEX is a marginally prettier answer; at least you don't have to
remember the index parameters. Shrinking indexes on-the-fly is still
on the TODO list.

But at least in 7.1 REINDEX has to be run under stand-alone Postgres, or
so say the docs:

REINDEX is used to recover corrupted system indexes. In order to run
REINDEX command, postmaster must be shut down and stand-alone Postgres
should be started instead with options -O and -P (an option to ignore
system indexes).

Whereas the DROP INDEX/CREATE INDEX method works on-the-fly. You may end
up running a seqscan somewhere, but that's not so big a problem, IMHO.

--
Alvaro Herrera (<alvherre[@]atentus.com>)
The eagle never lost so much time as
when he submitted to learn from the crow. (Nobody)

Alvaro Herrera <alvherre@atentus.com> writes:

But at least in 7.1 REINDEX has to be run under stand-alone Postgres, or
so say the docs:

The docs are not very well phrased (I've just made a note to fix that).
If you are trying to recover from a corrupted system index then indeed
you have to do all the pushups mentioned, because you don't want the
backend trying to use the broken index along the way:

REINDEX is used to recover corrupted system indexes. In order to run
REINDEX command, postmaster must be shut down and stand-alone Postgres
should be started instead with options -O and -P (an option to ignore
system indexes).

If you are trying to optimize an index on a user table, you can just do
REINDEX INDEX or REINDEX TABLE without the funny stuff.

Whereas the DROP INDEX/CREATE INDEX method works on-the-fly. You may end
up running a seqscan somewhere, but that's not so big a problem, IMHO.

True, that way doesn't lock out concurrent readers of the table while
the index is rebuilt.

regards, tom lane

REINDEX is a marginally prettier answer; at least you don't have to
remember the index parameters. Shrinking indexes on-the-fly is still
on the TODO list.

But at least in 7.1 REINDEX has to be run under stand-alone Postgres, or
so say the docs:

REINDEX is used to recover corrupted system indexes. In order to run
REINDEX command, postmaster must be shut down and stand-alone Postgres
should be started instead with options -O and -P (an option to ignore
system indexes).

Whereas the DROP INDEX/CREATE INDEX method works on-the-fly. You may end
up running a seqscan somewhere, but that's not so big a problem, IMHO.

The single-user warning is only for system index reindexing.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tom Lane wrote:

Alvaro Herrera <alvherre@atentus.com> writes:

Whereas the DROP INDEX/CREATE INDEX method works on-the-fly. You may end
up running a seqscan somewhere, but that's not so big a problem, IMHO.

True, that way doesn't lock out concurrent readers of the table while
the index is rebuilt.

Have the locking issues with reindex been resolved in 7.1.3?

--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com