Hi,
it looks like we setup hypertables in a wrong way, adding unnecessary space partitioning. This causes hypertable to have lots of chunks, so queries like SELECT max(event_id) ... took lot of time, because each chunk should be queried. For net major release we already implemented conversion of log tables structure. You can wait for it (likely release time is September) or you can try to convert your event_log and alarms tables manually using the following queries:
ALTER TABLE alarms RENAME TO old_alarms;
CREATE TABLE alarms (
alarm_id integer not null,
parent_alarm_id integer not null,
alarm_state integer not null,
hd_state integer not null,
hd_ref varchar(63) null,
creation_time integer not null,
last_change_time integer not null,
rule_guid varchar(36) null,
source_object_id integer not null,
zone_uin integer not null,
source_event_code integer not null,
source_event_id bigint not null,
dci_id integer not null,
message varchar(2000) null,
original_severity integer not null,
current_severity integer not null,
repeat_count integer not null,
alarm_key varchar(255) null,
ack_by integer not null,
resolved_by integer not null,
term_by integer not null,
timeout integer not null,
timeout_event integer not null,
ack_timeout integer not null,
alarm_category_ids varchar(255) null,
event_tags varchar(2000) null,
rca_script_name varchar(255) null,
impact varchar(1000) null,
PRIMARY KEY(alarm_id));
CREATE INDEX idx_alarms_source_object_id ON alarms(source_object_id);
CREATE INDEX idx_alarms_last_change_time ON alarms(last_change_time);
INSERT INTO alarms (alarm_id,parent_alarm_id,alarm_state,hd_state,hd_ref,creation_time,last_change_time,rule_guid,source_object_id,zone_uin,source_event_code,source_event_id,dci_id,message,original_severity,current_severity,repeat_count,alarm_key,ack_by,resolved_by,term_by,timeout,timeout_event,ack_timeout,alarm_category_ids,event_tags,rca_script_name,impact) SELECT alarm_id,parent_alarm_id,alarm_state,hd_state,hd_ref,creation_time,last_change_time,rule_guid,source_object_id,zone_uin,source_event_code,source_event_id,dci_id,message,original_severity,current_severity,repeat_count,alarm_key,ack_by,resolved_by,term_by,timeout,timeout_event,ack_timeout,alarm_category_ids,event_tags,rca_script_name,impact FROM old_alarms;
DROP TABLE old_alarms CASCADE;
ALTER TABLE event_log RENAME TO old_event_log;
DROP INDEX IF EXISTS idx_event_log_event_timestamp;
DROP INDEX IF EXISTS idx_event_log_source;
DROP INDEX IF EXISTS idx_event_log_root_id;
CREATE TABLE event_log (
event_id bigint not null,
event_code integer not null,
event_timestamp integer not null,
origin integer not null,
origin_timestamp integer not null,
event_source integer not null,
zone_uin integer not null,
dci_id integer not null,
event_severity integer not null,
event_message varchar(2000) null,
event_tags varchar(2000) null,
root_event_id bigint not null,
raw_data text null,
PRIMARY KEY(event_id,event_timestamp));
CREATE INDEX idx_event_log_event_timestamp ON event_log(event_timestamp);
CREATE INDEX idx_event_log_source ON event_log(event_source);
CREATE INDEX idx_event_log_root_id ON event_log(root_event_id) WHERE root_event_id > 0;
SELECT create_hypertable('event_log', 'event_timestamp', chunk_time_interval => 86400);
INSERT INTO event_log (event_id,event_code,event_timestamp,origin,origin_timestamp,event_source,zone_uin,dci_id,event_severity,event_message,event_tags,root_event_id,raw_data) SELECT event_id,event_code,event_timestamp,origin,origin_timestamp,event_source,zone_uin,dci_id,event_severity,event_message,event_tags,root_event_id,raw_data FROM old_event_log;
DROP TABLE old_event_log CASCADE;
Please note that data copy could take significant time. If you are not intended to keep existing event log you can just drop event_log table and create new one.
Don't forget to make database snapshot or backup before trying this!
Best regards,
Victor
