Thread: index files still growing
Hello, 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 ?? Is it solved in 7.1.3 ?? I spent an hour on deja.com without finding any answer. Any idea ?? Thanks. Pierre.
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