Project

General

Profile

Actions
Copy link

Bug #4363

closed

Extend the combined GIN index (events_combined_idx)

Added by Radko Krkoš about 6 years ago. Updated about 6 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Radko Krkoš
Category:
Design
Target version:
2.2
Start date:
10/11/2018
Due date:
% Done:

100%

Estimated time:
To be discussed:

Description

The GIN index "events_combined_idx" on the table "events" was designed to index all array type columns to cover some more complex filtering conditions by at least one of the array columns (currently: category, node_name, protocol, source_port, target_port, source_type, target_type, node_type, cesnet_resolvedabuses, cesnet_inspectionerrors).
Since its inception, new array type columns have been added and are accessible for filtering from the GUI. Therefore the index should be extended to cover these also.
The proposal is to replace the index as currently defined on columns:

category, node_name, protocol, source_port, target_port

with:
category, node_name, protocol, source_port, target_port, source_type, target_type, node_type, cesnet_resolvedabuses, cesnet_inspectionerrors

Because of the nature of GIN indices, additional columns add little size overhead. Also the order does not matter for GIN indices, the performance impact is negligible.

Remark:
An index such as proposed here was already created for testing on all hub, alt and dev with the name of "events_combined_new_idx".
A transition action in production therefore could be:

DROP INDEX events_combined_idx;
ALTER INDEX events_combined_new_idx RENAME TO events_combined_idx;

The change in Mentat source should be based on the information above.

Actions
Copy link
 #1

Updated by Radko Krkoš about 6 years ago

  • Assignee set to Jan Mach
Actions
Copy link
 #2

Updated by Radko Krkoš about 6 years ago

This would also make the index "events_cesnet_resolvedabuses_idx" redundant with smaller total size.

Actions
Copy link
 #3

Updated by Jan Mach about 6 years ago

  • Target version changed from Backlog to 2.2
Actions
Copy link
 #4

Updated by Jan Mach about 6 years ago

  • Status changed from New to In Progress
  • Assignee changed from Jan Mach to Radko Krkoš
  • % Done changed from 50 to 90

According to the recommendations I have just performed transition on all hub, alt and dev servers from old combined index to new one:

DROP INDEX events_combined_idx;
ALTER INDEX events_combined_new_idx RENAME TO events_combined_idx;

Attached commit changes the built-in definition of the combined index in the DBAL.

Do I understand it correctly that the events_cesnet_resolvedabuses_idx can be dropped without any negative effect on performance?

Actions
Copy link
 #5

Updated by Radko Krkoš about 6 years ago

  • Status changed from In Progress to Closed
  • % Done changed from 90 to 100

Jan Mach wrote:

Do I understand it correctly that the events_cesnet_resolvedabuses_idx can be dropped without any negative effect on performance?

That is the idea. It was already tested on dev and alt and seems to work alright. I have therefore performed:

DROP INDEX events_cesnet_resolvedabuses_idx;

on mentat-hub also. We should now close this issue.

Actions
Copy link

Also available in: Atom PDF