PostgreSQL 9.5: Introduced BRIN – Block Range Index with Performance Report

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Please read this previous article, To Understand the Power of BRIN – Block Range Index.

Database Theory: What is BRIN (Block Range Index), How is faster than BTREE Index

PostgreSQL 9.5 introduced the powerful BRIN Index, which is performance much faster than the regular BTREE Index.
The most important two lines of the BRIN are: It stores only minimum and maximum value per block so it does not require more space. For extremely large table It runs faster than any other Indexes.

In this post, I am going to show the example of BRIN index with the full performance report (testing over the 6gb of Table Data).

Below are steps:

First create one sample table:

Insert Millions of data to test the performance of BRIN Index:

Check the total size of table:

Now Check the performance without any Index:

Create BRIN index on TransactionDate Column:

Now Check the performance of the same query which has BRIN index:

Now, you can see the difference between the result of the above two queries.
With the BRIN index same query took only 7 seconds and without BRIN it took around 80 seconds.

Create Partial BRIN index on TransactionDate Column:
You can also create Partial BRIN index for your individual range of data. The Partial BRIN index is also faster than normal BRIN index, but we should apply proper filter based on created Partial BRIN index.