Обсуждение: vacuum slowness
I just deleted all 50,000 rows from a table that has one int4 and one text field. Why does vacuum take so long? If all the rows are superceeded, so no rows actually have to be moved, should it take so long for vacuum to run? -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> I just deleted all 50,000 rows from a table that has one int4 and one text
> field.
> Why does vacuum take so long? If all the rows are superceeded, so no
> rows actually have to be moved, should it take so long for vacuum to
> run?
Do you have any indexes on the table? I've noticed (and complained in
the past ;-)) that vacuuming a table takes unreasonably long if there
are a lot of dead index entries to be cleaned. It seems faster to drop
and recreate the index in a case like that.
regards, tom lane
Bruce Momjian wrote: > > I just deleted all 50,000 rows from a table that has one int4 and one text > field. > > Why does vacuum take so long? If all the rows are superceeded, so no > rows actually have to be moved, should it take so long for vacuum to > run? Indices? Vadim
> Bruce Momjian wrote: > > > > I just deleted all 50,000 rows from a table that has one int4 and one text > > field. > > > > Why does vacuum take so long? If all the rows are superceeded, so no > > rows actually have to be moved, should it take so long for vacuum to > > run? > > Indices? Yes. That seems to be the problem. 45k lines, COPY is fast, DELETE is fast if there are no indexes. With an index, it takes a long time. Bummer. Ideas? -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian wrote: > > > > > Indices? > > Yes. That seems to be the problem. 45k lines, COPY is fast, DELETE is > fast if there are no indexes. With an index, it takes a long time. > Bummer. Ideas? I hope to implement space re-using and address vacuum slowness in 6.6 Vadim
Vadim Mikheev wrote: > I hope to implement space re-using and address vacuum slowness in 6.6 Are you intending to keep it so that you could still run PostgreSQL on top of a WORM (Write once Read Many) device? I'm plannng to put some databases directly on these new write-only DVD drives coming out.... I'd want to keep the indexes on a (WMRM) hard drive though. :) Clark
Clark Evans wrote: > > Vadim Mikheev wrote: > > I hope to implement space re-using and address vacuum slowness in 6.6 > > Are you intending to keep it so that you could still run PostgreSQL > on top of a WORM (Write once Read Many) device? I'm plannng to > put some databases directly on these new write-only DVD drives > coming out.... I'd want to keep the indexes on a (WMRM) hard drive though. Is it possible to use WORM now? Vadim
Vadim Mikheev wrote: > > Is it possible to use WORM now? > I don't know, but it's on my to-try list. I'm hoping it will work (got all excited when I was reading the acedemic papers) This was one of the goals of the database... It just seems for situations where a high degree of auditability is needed that running the database on top of a WORM is a fantastic idea. I'm writing a bookkeeping system, and think it would be a very valueable reason to move to 'free software'. It's the killer feature Oracle dosn't have. Well, acedemically it sounds nice. *smirk* It's all speculation, but fun speculation anyway... :) Clark P.S. Perhaps it's not all that great of an idea. I intend to journal all of the interactions with the database to a CDR, I was just hoping to get it for free.... *evil grin*
> -----Original Message----- > From: owner-pgsql-hackers@postgreSQL.org > [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Vadim Mikheev > Sent: Thursday, March 18, 1999 2:07 PM > To: Bruce Momjian > Cc: hackers@postgreSQL.org > Subject: Re: [HACKERS] Re: vacuum slowness > > > Bruce Momjian wrote: > > > > > > > > Indices? > > > > Yes. That seems to be the problem. 45k lines, COPY is fast, DELETE is > > fast if there are no indexes. With an index, it takes a long time. > > Bummer. Ideas? > > I hope to implement space re-using and address vacuum slowness > in 6.6 > We would be able to vacuum without blocking same-table writers in v6.5 ? Or would VACUUM block same-table readers as VACUUM does currently ? Thanks. Hiroshi Inoue Inoue@tpf.co.jp
> > Is it possible to use WORM now?
> I don't know, but it's on my to-try list. I'm hoping it
> will work (got all excited when I was reading the acedemic papers)
> This was one of the goals of the database...
... which we probably gave up when we removed time travel, quite a
while ago.
- Tom