Vacuuming an index takes way too long

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: Vacuuming an index takes way too long
Date: 1998-07-26 15:00:53
Message-ID: 11812.901465253@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

With current development sources, I am noticing that if I delete a large
number of entries from a table, the next vacuum on the table will spend
an *unreasonable* amount of time vacuuming the indexes on the table.

Here's a sample vacuum log:

NOTICE: --Relation marketorderhistory--
NOTICE: Pages 1016: Changed 0, Reapped 1016, Empty 0, New 0; Tup 8983: Vac 63439, Crash 5, UnUsed 234, MinLen 92, MaxLen 120; Re-using: Free/Avail. Space 7013200/7009228; EndEmpty/Avail. Pages 0/1015. Elapsed 1/2 sec.
NOTICE: Ind marketorderhistory_sequenceno_i: Pages 550; Tuples 8983: Deleted 63439. Elapsed 7876/2684 sec.
NOTICE: Ind marketorderhistory_completionti: Pages 312; Tuples 8983: Deleted 63439. Elapsed 0/51 sec.
NOTICE: Ind marketorderhistory_ordertime_in: Pages 273; Tuples 8983: Deleted 63439. Elapsed 1/21 sec.
NOTICE: Ind marketorderhistory_oid_index: Pages 454; Tuples 8983: Deleted 63439. Elapsed 5047/1861 sec.
NOTICE: Rel marketorderhistory: Pages: 1016 --> 129; Tuple(s) moved: 8983. Elapsed 2/22 sec.
NOTICE: Ind marketorderhistory_sequenceno_i: Pages 550; Tuples 8983: Deleted 8983. Elapsed 0/3 sec.
NOTICE: Ind marketorderhistory_completionti: Pages 312; Tuples 8983: Deleted 8983. Elapsed 0/2 sec.
NOTICE: Ind marketorderhistory_ordertime_in: Pages 273; Tuples 8983: Deleted 8983. Elapsed 0/3 sec.
NOTICE: Ind marketorderhistory_oid_index: Pages 454; Tuples 8983: Deleted 8983. Elapsed 1/3 sec.

Three and a half hours to vacuum a table of a few thousand entries isn't
acceptable performance in my book. You could drop and recreate these
indexes in four seconds each (measured result); so what's going on here?

In case it helps, the indices in question are defined like so:

CREATE UNIQUE INDEX MarketOrderHistory_oid_Index on MarketOrderHistory
USING btree (oid);
CREATE INDEX MarketOrderHistory_orderTime_Index ON MarketOrderHistory
USING btree (orderTime);
CREATE INDEX MarketOrderHistory_completionTime_Index ON MarketOrderHistory
USING btree (completionTime);
CREATE UNIQUE INDEX MarketOrderHistory_sequenceNo_Index ON MarketOrderHistory
USING btree (sequenceNo);

where orderTime and completionTime are datetime fields, sequenceNo is int4.

One thing that jumps out at me is that the indexes that are taking a
long time to process are unique indexes.

regards, tom lane

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 1998-07-26 15:10:51 Minor bug: inconsistent handling of overlength names
Previous Message Cyril VELTER 1998-07-26 13:17:00