I just noticed this old thread. Thomas Kellerer wrote: > > 62.1. Introduction > > ... > > "A block range is a group of pages that are physically adjacent in the table; for each block range, some summary info is stored by the index." > > > > From the above, may I presume that it is best to cluster (or sort), > > the table based on the intended BRIN column(s) before actually > > creating the index to insure the pages are adjacent? If so, should > > that not be included in the documentation, instead of implied? > > That is something I am also curious about. If that was true, it would > mean that BRIN indexes couldn't be used on tables that are not > clustered along the index There's no hard requirement that values must be clustered. If the values are clustered, that's the best case scenario for BRIN and things will be very quick. However, clustering a table is a slow operation and requires locking the table, so I don't recommend that. But strict correlation isn't really necessary either -- you just need the values to be grouped together. To illustrate, consider this simplistic case: table has four pages, all the values in the first page have col1=999, page 2 has all col1=1, page 3 has all col1=1500, page 4 has col1=-1000. There's little correlation there, but a BRIN index with pages_per_range=1 can still help a query that looks for col1 > 500 execute optimally. > it wouldn't make sense to have more than one BRIN index. Well, you can put all the columns in a single index, and it works just like if you had one index for each column. However, if you want a BRIN index that's more detailed for certain columns than others, you can use different pages_per_range settings on multiple indexes. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
