Vacuuming an index takes way too long
От | Tom Lane |
---|---|
Тема | Vacuuming an index takes way too long |
Дата | |
Msg-id | 11812.901465253@sss.pgh.pa.us обсуждение исходный текст |
Список | 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 по дате отправления: