
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, Pennsylvania 19026

Wed, 17 Mar 1999 13:25:30 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
vacuum slowness
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

Wed, 17 Mar 1999 20:52:36 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] vacuum slowness 
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

Thu, 18 Mar 1999 08:53:41 +0700
Vadim Mikheev <vadim@krs.ru>
Re: vacuum slowness
> 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, Pennsylvania 19026

Wed, 17 Mar 1999 21:45:13 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
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

Vadim

Thu, 18 Mar 1999 12:06:42 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Re: vacuum slowness
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

Thu, 18 Mar 1999 05:36:34 +0000
Clark Evans <clark.evans@manhattanproject.com>
Re: [HACKERS] Re: vacuum slowness
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

Thu, 18 Mar 1999 13:46:20 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Re: vacuum slowness
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*

Thu, 18 Mar 1999 07:32:27 +0000
Clark Evans <clark.evans@manhattanproject.com>
Re: [HACKERS] Re: vacuum slowness


> -----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

Thu, 18 Mar 1999 17:19:40 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] Re: vacuum slowness
> > 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

Fri, 26 Mar 1999 16:38:30 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] Re: vacuum slowness