Vacuuming an index takes way too long - Mailing list pgsql-hackers

From Tom Lane
Subject Vacuuming an index takes way too long
Date
Msg-id 11812.901465253@sss.pgh.pa.us
Whole thread Raw
List 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,
MaxLen120; 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

pgsql-hackers by date:

Previous
From: Cyril VELTER
Date:
Subject: ...
Next
From: Tom Lane
Date:
Subject: Minor bug: inconsistent handling of overlength names