Bug 1470992

Summary: [RFE] Use BRIN indexes
Product: [oVirt] ovirt-engine Reporter: Martin Perina <mperina>
Component: Database.CoreAssignee: Eli Mesika <emesika>
Status: CLOSED DEFERRED QA Contact: Lucie Leistnerova <lleistne>
Severity: medium Docs Contact:
Priority: medium    
Version: ---CC: bugs, lsvaty, mgoldboi, mperina
Target Milestone: ---Keywords: FutureFeature, Performance
Target Release: ---Flags: rule-engine: ovirt-4.2+
lsvaty: testing_plan_complete-
mgoldboi: planning_ack+
mperina: devel_ack+
lsvaty: testing_ack+
Hardware: Unspecified   
OS: Unspecified   
Whiteboard:
Fixed In Version: Doc Type: Enhancement
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2017-10-19 08:17:24 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: Infra RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:
Bug Depends On: 1459134    
Bug Blocks:    
Deadline: 2017-10-31   

Description Martin Perina 2017-07-14 08:43:58 UTC 
Description of problem:

BRIN indexes (Block Range INdexes [1]) store metadata on a range of pages. At the moment this means the minimum and maximum values per block. This may improve some queries in large tables


[1] https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.5#BRIN_Indexes

Version-Release number of selected component (if applicable):


How reproducible:


Steps to Reproduce:
1.
2.
3.

Actual results:


Expected results:


Additional info:
Comment 1 Eli Mesika 2017-07-17 19:17:10 UTC 
Where does BRIN bring most benefit then? 

If you're doing analytical queries with aggregates, sums, averages, means, and such. Those usually touch huge number of rows. In this case, looking up values in a btree index doesn't make sense, it's usually more performant to just read the whole table and do a filter on every row, so it's common for those to be sequential scans on tables. With BRIN index, you can skip 1MB of data[1] just by comparing your filtering value with the two in the index, effectively getting a huge boost with very small index size.
Comment 3 Eli Mesika 2017-10-18 15:21:57 UTC 
I tested the usage of BRIN index on audit_log(audit_log_id) instead of the regular btree index created with the audit_log table PK.

Tests were done on a table with over than 2 Millions records 

Performance on mostly used query (search on events) didn't show any significant change between brin and btree 

Seems that BRIN indexes are mostly effective with queries that handled no6t only large data but also aggregations/grouping which are not used in audit_log  

I would recommend to close this RFE for 4.2 or mark it for future releases since I can not see any other table candidate for it right now.

Martin ?