Frontend very slow on Zabbix-Postgresql

[Murilex]

I'm experiencing a very slow frontend with zabbix. I don't know to say when the problem really started, but access the Overview, Latest Data and Graphics tabs on the frontend became practically impossible to me.

I'm using zabbix 1.8.3 and PostgreSQL 8.4.3. Using the "Server Status" tool on pgAdminIII interface I could find a query taking a long time to complete:

SELECT  DISTINCT  g.* FROM groups g,hosts_groups hg,hosts h WHERE ((g.groupid  BETWEEN 000000000000000 AND 099999999999999)) AND hg.groupid=g.groupid AND h.hostid=hg.hostid AND h.status=0 AND EXISTS (SELECT i.hostid FROM items i WHERE hg.hostid=i.hostid AND i.status=0) AND EXISTS( SELECT t.triggerid  FROM items i, functions f, triggers t WHERE i.hostid=hg.hostid  AND i.status=0 AND i.itemid=f.itemid  AND f.triggerid=t.triggerid  AND t.status=0)
Query complete time: 174151ms

The same query is returned faster rewritten as:

SELECT  DISTINCT  g.* FROM groups g,hosts_groups hg,hosts h WHERE hg.groupid=g.groupid AND h.hostid=hg.hostid AND h.status=0 AND EXISTS (SELECT i.hostid FROM items i WHERE hg.hostid=i.hostid AND i.status=0) AND EXISTS( SELECT t.triggerid  FROM items i, functions f, triggers t WHERE i.hostid=hg.hostid  AND i.status=0 AND i.itemid=f.itemid  AND f.triggerid=t.triggerid  AND t.status=0)
Query complete time: 3151ms

I have only 21 groups in my zabbix database and I'm wondering why the ((g.groupid BETWEEN 000000000000000 AND 099999999999999)) block are adding so much execution time to the first query.

The execution plan of the first query is:
"HashAggregate (cost=28699.99..28700.00 rows=1 width=158)"
" -> Nested Loop Semi Join (cost=931.63..28699.88 rows=15 width=158)"
" Join Filter: (h.hostid = i.hostid)"
" -> Nested Loop Semi Join (cost=0.86..608.59 rows=10 width=182)"
" -> Nested Loop (cost=0.86..74.85 rows=15 width=174)"
" -> Nested Loop (cost=0.00..11.31 rows=22 width=166)"
" -> Seq Scan on groups g (cost=0.00..1.36 rows=1 width=158)"
" Filter: ((groupid >= 0) AND (groupid <= 99999999999999::bigint))"
" -> Index Scan using hosts_groups_2 on hosts_groups hg (cost=0.00..9.67 rows=22 width=16)"
" Index Cond: (hg.groupid = g.groupid)"
" -> Bitmap Heap Scan on hosts h (cost=0.86..2.88 rows=1 width=8)"
" Recheck Cond: (h.hostid = hg.hostid)"
" Filter: (h.status = 0)"
" -> Bitmap Index Scan on hosts_pkey (cost=0.00..0.86 rows=1 width=0)"
" Index Cond: (h.hostid = hg.hostid)"
" -> Index Scan using items_1 on items i (cost=0.00..1463.06 rows=81 width=8)"
" Index Cond: (i.hostid = h.hostid)"
" Filter: (i.status = 0)"
" -> Hash Join (cost=930.77..42528.33 rows=5610 width=8)"
" Hash Cond: (f.triggerid = t.triggerid)"
" -> Nested Loop (cost=4.70..41490.05 rows=5610 width=16)"
" -> Seq Scan on functions f (cost=0.00..3789.10 rows=5610 width=16)"
" -> Bitmap Heap Scan on items i (cost=4.70..6.71 rows=1 width=16)"
" Recheck Cond: (i.itemid = f.itemid)"
" Filter: (i.status = 0)"
" -> Bitmap Index Scan on items_pkey (cost=0.00..4.70 rows=1 width=0)"
" Index Cond: (i.itemid = f.itemid)"
" -> Hash (cost=844.54..844.54 rows=6523 width=8)"
" -> Seq Scan on triggers t (cost=0.00..844.54 rows=6523 width=8)"
" Filter: (status = 0)"

I don't have enough knowledge on Postgresql database. I've tried to create an index on column groups.groupid but the execution time didn't decrease. Is there some workaround or fix? Should I remove the ((g.groupid BETWEEN 000000000000000 AND 099999999999999)) block of zabbix scripts? In which file is this code? Thanks.