2

The table in question has 3 relevant columns: A, B, and C. The table is expected to have north of 1B records.

A unique index can be created with A, B, C where A and B are text fields and C is a numeric id (incrementing)

C on it's own is not unique.

BRIN indexes seem to fit nicely with this model. We have an incrementing integer with data that never has updates to the indexed field.

Can BRIN indexes be used efficiently with a multi column index in a non-partitioned table or should partitions be used instead with an index on a single column in each partition?

For context, the 99% use case is a query by a, b, c where c is either in a range (or orordered by with a limit if that's possible). We also want to query the MAX c by a, b.

Thanks for any advice.

Improve this question

1 Answer 1

Reset to default
3

Multi-column BRIN indexes index each column on its own, so a multi-column BRIN index will work just as well as a BRIN index on each individual column. As the documentation says:

The only reason to have multiple BRIN indexes instead of one multicolumn BRIN index on a single table is to have a different pages_per_range storage parameter.

However, a BRIN index only makes sense with columns whose values are ever-increasing (or decreasing). So with an insert-only table with incrementing c it would be useful to have a BRIN index on only c.

That index would be very small, and with the query you describe, it could quickly identify one or a few block ranges that may contain matching rows. These blocks have to be scanned, which is not as fast as a B-tree index, but probably fast enough. With BRIN you trade size for speed.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Thanks for the helpful response. This confirms my suspicions.
A very late follow-up question. What if your query is always WHERE col1 = xx and col2 BETWEEN this and that? If Col2 is just increasing set of integral values and col1 is a collection of random integers, would we need a BTREE index on Col1 and a BRIN on col2?
@ForeverLearning I don't know if they can be used together, you'd have to try. But if you have a (large) B-tree index anyway, why not add the second column to it? The index would be somewhat bigger, but very effective.
@LaurenzAlbe "because BRIN keeps the minimum and maximum value per block range. Now minimum and maximum of a composite are taken using lexicographic ordering, ..." - are you sure about that? I think a BRIN index stores minmaxes independently across columns, not as a combined lexicographic minmax. To reproduce: gist.github.com/akdor1154/88f192f4d4bc2e747d98fce235a7d138 . You can see t1 \in [1,9999] and t2 \in [1,2]. If brin stored combined, I would expect to see (t1, t2) \in [(1,1), (9999,1)]. A multicol table ordering itself could only be lexicographic though, is that what you meant?
@Jarrad You are right. I have fixed my answer.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.