We have an issue with the housekeeper on PostgreSQL 8.4, the query written for PostgreSQL doesn't work very well, and in a couple of years the housekeeper queue is very large (32000 items).

I do not know the history behind this statement, and I'm not a database expert, but I wonder if the current statement is written to emulate an Oracle feature, to prevent filling up the undo table space?

Below I have listed two alternative statements which I hope can be implemented for Zabbix 1.8.1, because the housekeeper does not work in its current state (it deletes data slower than it is inserted). I believe the best option would be the unlimited delete (alternative 2).

PS! The runtimes are from items with different sizes, so the variation is expected.

• Current statement:
  duration: 257371.351 ms statement: delete from history where oid in (select oid from history where itemid=22442 limit 500)
  duration: 261079.443 ms statement: delete from history where oid in (select oid from history where itemid=23259 limit 500)
  duration: 257834.690 ms statement: delete from history where oid in (select oid from history where itemid=22460 limit 500)
  duration: 257393.873 ms statement: delete from history where oid in (select oid from history where itemid=23265 limit 500)
  duration: 258183.607 ms statement: delete from history where oid in (select oid from history where itemid=25988 limit 500)

1. EXPLAIN delete from history where oid in (select oid from history where itemid=25988 limit 500);
   QUERY PLAN
   -----------------------------------------------------------------------------------------------------
   Hash Semi Join (cost=1762.48..16917543.41 rows=200 width=6)
   Hash Cond: (public.history.oid = public.history.oid)
   -> Seq Scan on history (cost=0.00..14693125.32 rows=846722432 width=10)
   -> Hash (cost=1756.23..1756.23 rows=500 width=4)
   -> Limit (cost=0.00..1751.23 rows=500 width=4)
   -> Index Scan using history_1 on history (cost=0.00..613064.01 rows=175038 width=4)
   Index Cond: (itemid = 25988)
   (7 rows)

• Alternative statement 1 (index "hint"):
  duration: 3508.723 ms statement: delete from history where itemid=29064 and oid in (select oid from history where itemid=29064 limit 500)
  duration: 39967.581 ms statement: delete from history where itemid=25419 and oid in (select oid from history where itemid=25419 limit 500)
  duration: 15575.868 ms statement: delete from history where itemid=29078 and oid in (select oid from history where itemid=29078 limit 500)
  duration: 55610.652 ms statement: delete from history where itemid=22231 and oid in (select oid from history where itemid=22231 limit 500)
  duration: 45966.948 ms statement: delete from history where itemid=29073 and oid in (select oid from history where itemid=29073 limit 500)

1. EXPLAIN delete from history where itemid=25988 and oid in (select oid from history where itemid=25988 limit 500);
   QUERY PLAN
   -----------------------------------------------------------------------------------------------------
   Hash Semi Join (cost=1762.48..615295.19 rows=200 width=6)
   Hash Cond: (public.history.oid = public.history.oid)
   -> Index Scan using history_1 on history (cost=0.00..613064.01 rows=175038 width=10)
   Index Cond: (itemid = 25988)
   -> Hash (cost=1756.23..1756.23 rows=500 width=4)
   -> Limit (cost=0.00..1751.23 rows=500 width=4)
   -> Index Scan using history_1 on history (cost=0.00..613064.01 rows=175038 width=4)
   Index Cond: (itemid = 25988)
   (8 rows)

• Alternative statement 2 (no limit):
  duration: 75994.597 ms statement: delete from history where itemid=28908
  duration: 153382.502 ms statement: delete from history where itemid=22948
  duration: 70893.997 ms statement: delete from history where itemid=28910
  duration: 181384.096 ms statement: delete from history where itemid=23512
  duration: 63091.321 ms statement: delete from history where itemid=28904

1. EXPLAIN delete from history where itemid=25988;
   QUERY PLAN
   -----------------------------------------------------------------------------------
   Index Scan using history_1 on history (cost=0.00..613064.01 rows=175038 width=6)
   Index Cond: (itemid = 25988)
   (2 rows)