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:
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.
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 ?