Missing indexes in db schema ?

[misteryoda]

Hi,

Sorry if I'm wrong but I noticed our zabbix went slow over the months and tried to understand why.

Tonight I found out that a query:

SELECT COUNT(DISTINCT i.itemid) as cnt FROM items i, hosts h WHERE i.hostid=h.hostid AND h.status=0 AND i.status=1

was taking a huge amount of time to complete and the disk i/o ready were almost at around 50Mb/s.

After adding :

zabbix=> create index hosts_hostid on hosts (hostid);
CREATE INDEX
zabbix=> create index items_hostid on items (hostid);
CREATE INDEX

The query time went down to almost 0 sec and the load dropped.

Is there a reason why there is no such indexes in the base schemal used at install ? are there other indexes worth to add to optimize the beast ?

Thanks a lot

[misteryoda]

Hi,

forgot to precise that the db engine is postgresql 9.0 using partitioned scheme for history tables.
But the impacted tables are not partitionned..

Cheers,
Sébastien