CODEWITHSUNDEEP
postgresqldatabase-indexes
D_C
D_C

Reputation: 390

BRIN index creation

I am attempting to use BRIN indexes and I want to ensure that my data is ordered before i create the index.

With all the points made in Change order of the table rows in PostgreSQL and Postgres choosing BTREE instead of BRIN index posts, comments, and answers, is it best to put an order by clause on your column, execute the BRIN index, and then turn off the constraint on the table. Does this work and maintain the BRIN index (index doesn't blow up) once constraint is gone?

It just appears that I cannot get the BRIN index to have any positive performance affect on my queries as the poster from the second link had noticed. The answer in the first link seems like a possible methodology, but I cannot find how to add an order by constraint on a column when creating or altering a table.

Upvotes: 0

Views: 723

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246653

There is no ORDER BY clause for a table.

The only thing you can do is use the CLUSTER command to rewrite a table in the order of a certain index. Since BRIN indexes only work if the ordering is perfect, you can then use a BRIN index as long as there are no more modifications to the database. The exception here are INSERTs in the correct order (say, if the BRIN index is on an always increasing timestamp).

So BRIN indexes are only useful for data warehouses where the data are not modified and for time series. They also cannot be used to support ORDER BY.

If in doubt, use a B-tree index.

Upvotes: 1

Related Questions