BRIN – Block Range Index with Performance Report Futures of PostgreSQL 9.5

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:

CREATE TABLE tbl_ItemTransactions 
 (
     TranID SERIAL
     ,TransactionDate TIMESTAMPTZ
     ,TransactionName TEXT
 );

Insert Millions of data to test the performance of BRIN Index:
INSERT INTO tbl_ItemTransactions 
(TransactionDate, TransactionName)
SELECT x, 'dbrnd' 
FROM generate_series('2008-01-01 00:00:00'::timestamptz, '2016-08-01 00:00:00'::timestamptz,'2 seconds'::interval) a(x);
Check the total size of table:
SELECT pg_size_pretty(pg_total_relation_size('tbl_ItemTransactions')) AS TableSize;
 
/*
    TableSize
------------------
    6741 MB
*/

Now Check the performance without any Index:
EXPLAIN ANALYSE 
SELECT COUNT(1) FROM tbl_ItemTransactions 
WHERE TransactionDate BETWEEN '2012-01-01 00:00:00' and '2014-08-08 08:08:08';
/*
--Result:
   QueryPlan
-------------------------------------------------------------------------
Aggregate  (cost=2997896.81..2997896.82 rows=1 width=0) 
 (actual time=40651.793..40651.793 rows=1 loops=1)
  ->  Seq Scan on tbl_itemtransactions  (cost=0.00..2894105.00 rows=41516724 width=0) 
 (actual time=0.009..38726.686 rows=41054645 loops=1)
        Filter: ((transactiondate >= '2012-01-01 00:00:00+05:30'::timestamp with time zone) 
  AND (transactiondate <= '2014-08-08 08:08:08+05:30'::timestamp with time zone))
        Rows Removed by Filter: 94377356
Planning time: 0.860 ms
Execution time: 80651.837 ms
*/

Create BRIN index on TransactionDate Column:
CREATE INDEX idx_tbl_ItemTransactions_TransactionDate
ON tbl_ItemTransactions
USING BRIN (TransactionDate);

Now Check the performance of the same query which has BRIN index:
EXPLAIN ANALYSE 
SELECT COUNT(1) FROM tbl_ItemTransactions 
WHERE TransactionDate BETWEEN '2012-01-01 00:00:00' and '2014-08-08 08:08:08';
/*
   QueryPlan
---------------------------------------------------------------------------
Aggregate  (cost=2014834.09..2014834.10 rows=1 width=0) 
 (actual time=7108.998..7108.998 rows=1 loops=1)
  ->  Bitmap Heap Scan on tbl_itemtransactions  (cost=425666.42..1911042.28 rows=41516724 width=0) 
 (actual time=16.995..5415.086 rows=41054645 loops=1)
        Recheck Cond: ((transactiondate >= '2012-01-01 00:00:00+05:30'::timestamp with time zone) 
  AND (transactiondate <= '2014-08-08 08:08:08+05:30'::timestamp with time zone))
        Rows Removed by Index Recheck: 21579
        Heap Blocks: lossy=261632
        ->  Bitmap Index Scan on idx_tbl_itemtransactions_transactiondate  
  (cost=0.00..415287.24 rows=41516724 width=0) (actual time=15.547..15.547 rows=2616320 loops=1)
              Index Cond: ((transactiondate >= '2012-01-01 00:00:00+05:30'::timestamp with time zone) 
              AND (transactiondate <= '2014-08-08 08:08:08+05:30'::timestamp with time zone))
Planning time: 0.059 ms
Execution time: 7109.060 ms
*/

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.

CREATE INDEX idx_tbl_ItemTransactions_TransactionDate_2012
ON tbl_ItemTransactions
USING BRIN (TransactionDate)
WHERE TransactionDate BETWEEN '2012-01-01' AND '2012-12-31';

CREATE INDEX idx_tbl_ItemTransactions_TransactionDate_2013
ON tbl_ItemTransactions
USING BRIN (TransactionDate)
WHERE TransactionDate BETWEEN '2013-01-01' AND '2013-12-31';