First: thanks for this great monitoring system. We use it with PostgreSQL on several production servers, and will be able to test and give feedback / patches for PostgreSQL issues / improvements we may find for Zabbix.
I got that query an slow one (with more than 80 seconds query duration):
delete from history where itemid=22330 and oid in (select oid from history where itemid=22330 limit 500;
After taking a look at the code, it is from housekeeper.c , around line 93 (on 1.8.5 source).
Anyway, I just added that index:
create index idx_oid on history (oid);
Please note the index is not unique. And now the query takes only 1.5 seconds.
This is a Major issue because it was actually slowing-down the whole server (there are other apps using this DB server).
I think you should include that index into the DB schema.
On other issue (related): that delete query looks a little scary to me, because it doesn't precisely identify which rows it have to delete.... I'd feel more safe if you were to use a more specific query, something like:
delete from history where itemid=22330 and oid in (select oid from history where itemid=22330 order by clock asc limit 500);
That will sort using clock, and delete 500 oldest items, this query currently takes around 2 seconds to run.
I hope this helps to improve this already good monitoring system. I'll take a closer look at the DB usage, and report other findings I may come up.