Problem with VACUUM after very large delete?

From: Doug Fields <dfields-pg-general(at)pexicom(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Problem with VACUUM after very large delete?
Date: 2002-12-17 22:47:13
Message-ID: 5.1.0.14.2.20021217174300.01fc7ac8@pop.pexicom.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello all,

(7.2.1 on Debian Woody 3.0 with various 7.2.3 patches backported)

Earlier today I deleted about 31 million records from one of our tables
with a very complex query which required a full table scan. This query took
quite a few hours to run (in fact, overnight).

Later, I did a VACUUM and then an ANALYZE.

However, it does not appear that either:

1) The records were actually deleted or

2) The VACUUM is working properly

Why? The VACUUM doesn't seem to be vacuuming the two indexes on the table.
I'm not sure what's going on, but if you could make any suggestions, I'd
appreciate it.

Details are below.

Thanks,

Doug

pexicast_lg=# ANALYZE VERBOSE audit_log;
NOTICE: Analyzing audit_log
ANALYZE
pexicast_lg=# VACUUM VERBOSE audit_log;
NOTICE: --Relation audit_log--
NOTICE: Pages 2083605: Changed 2961, Empty 0; Tup 105773248: Vac 0, Keep
31106432, UnUsed 2.
Total CPU 89.49s/13.67u sec elapsed 1101.17 sec.
VACUUM
pexicast_lg=# \d audit_log
Table "audit_log"
Column | Type | Modifiers
----------+--------------------------+---------------
at | timestamp with time zone | default now()
type | integer |
severity | integer |
who | integer |
target | integer |
var1 | integer |
var2 | integer |
message | character varying(255) |
ip | character(15) |
login | integer |
Indexes: audit_type_target_idx,
audit_type_var1_idx

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Robert Treat 2002-12-17 23:12:43 Re: Problem with VACUUM after very large delete?
Previous Message John Cochran 2002-12-17 22:33:34 Creating trigger functions in pl/pgsql