Postgres selects BTREE instead of BRIN

Question

I am running Postgres 9.5 and playing with the BRIN indices. I have a fact table with approximately 150 million rows, and I'm trying to get PG to use the BRIN index. My request:

select sum(transaction_amt), 
       sum (total_amt) 
from fact_transaction 
where transaction_date_key between 20170101 and 20170201 

I created both the BTREE index and the BRIN index (the default value is page_per_range 128) in the transaction_date_key column (the above query applies to January-February 2017). I would have thought PG would prefer to use the BRIN index, however it comes with the BTREE index. Here is a plan of explanation:

https://explain.depesz.com/s/uPI

Then I deleted the BTREE index, made a vacuum / parsed the table and re-executed the query, and it selected the BRIN index, however, the execution time was much longer:

https://explain.depesz.com/s/5VXi

In fact, my tests were getting faster when using the BTREE index rather than the BRIN index. I thought it should be the other way around?

I would prefer to use the BRIN index because of its smaller size, but I cannot get PG to use it.

Note. I uploaded data from January 2017 to June 2017 (defined via transaction_date_key) when I read that the ordering of physical tables matters when using BRIN indexes.

Does anyone know why PG prefers to use the BTREE index and why BRIN is much slower in my case?

Answer 1

, BRIN – 30 , , .

, , , transaction_date_key .

A BRIN ( pages_per_range, 128). .

, transaction_date_key 20170101 20170201, , .

:

• pages_per_range. , "" .

• transaction_date_key. , ( ) B- .