One slow query

[tommycz]

Hello,
I turned on sql debug,because of very slow loading graphs.In the DB log is one repeating query with different itemid depending on selection of graph:

2011-09-15 11:49:30 CEST:::1(42368):qpzabbix@zabbix:[16175]: LOG: duration: 50154.003 ms statement: SELECT ht.itemid, MIN(ht.clock) as min_clock FROM trends ht WHERE (ht.itemid IN (33401)) GROUP BY ht.itemid

In fact,table trends has 30 mil. rows,history 70 mil. rows.

I have Zabbix 1.8.6,Postgresql 8.4.7.

Welcome any suggestions

Thank you
Tomas

[tommycz]

I found this>
Slow query:
SELECT ht.itemid, MIN(ht.clock) as min_clock FROM trends ht WHERE (ht.itemid IN (34713,34714,34715)) GROUP BY ht.itemid;

Fast query:

select ht.itemid,min(ht.clock) as min_clock from trends ht where (ht.itemid=34713 or ht.itemid=34714 or ht.itemid=34715) group by ht.itemid;