Housekeeper too high with SQL query too long

[davidc]

Hello,

We encountered a permanent use of housekeeper after our upgrade.

Zabbix 6.0.3 --> zabbix 6.4.1 (all Zabbix components except agent-zabbix)
Postgres 13 --> postgres15
TimescaleDB2.4 --> TimescaleDB2.10

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
We had an SQL query running in a loop, 400 seconds long.

explain select p1.eventid from problem p1 where p1.r_clock<>0 and p1.r_clock<1681642468 and p1.eventid not in (
zabbixprod(# select cause_eventid from problem p2 where p1.eventid=p2.cause_eventid) limit 5000;
QUERY PLAN
----------------------------------------------------------------------------------------------
Limit (cost=0.42..94246128.15 rows=5000 width=8)
-> Index Scan using problem_2 on problem p1 (cost=0.42..331086647.14 rows=17565 width=8)
Index Cond: (r_clock < 1681642468)
Filter: ((r_clock <> 0) AND (NOT (SubPlan 1)))
SubPlan 1
-> Seq Scan on problem p2 (cost=0.00..18731.10 rows=1 width=8)
Filter: (p1.eventid = cause_eventid) zz0.55svkqzps7zz
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

An index seemed to be missing: the cause_eventid column in the problem table:

\d problem
Table "public.problem
Column | Type | Collation | NULL-able | Default
---------------+-------------------------+-----------------+-----------+-----------------------
eventid | bigint | | not null |
source | integer | not null | 0
object | integer | not null | 0
objectid | bigint | not null | '0'::bigint
clock | integer | not null | 0
ns | integer | not null | 0
r_eventid | bigint | |
r_clock | integer | not null | 0
r_ns | integer | not null | 0
correlationid | bigint | |
userid | bigint | |
name | character varying(2048) | not null | ''::character varying
acknowledged | integer | not null | 0
severity | integer | not null | 0
cause_eventid | bigint | | |
Index :
"problem_pkey" PRIMARY KEY, btree (eventid)
"problem_1" btree (source, object, objectid)
"problem_2" btree (r_clock)
"problem_3" btree (r_eventid)
-----------------------------------------------------------------------------------------------------------------

The missing index has been created, the request now executes in 40ms and the housekeeper has returned to normal behavior.

Can you take this bug into account and give us some feedback?

Best regards,
David

[cyber]

Bug reports go there .. -> https://support.zabbix.com/projects/ZBX/issues