Postgresql 9.5 BRIN index dramatically slower than expected

I have a table with over 12 million rows of log data and have migrated to Postgres 9.5 to take advantage of the new BRIN index because I have disk space limitations. I assumed that my situation was made to order for BRIN indexing given the natural ordering by date of my log lines.

However, I'm started by the results. BRIN is more than an order of magnitude slower than btree.

Original Btree index:

EXPLAIN ANALYZE SELECT COUNT(*) from logline where date BETWEEN '2016-01-15' and '2016-01-31';

 Aggregate  (cost=153488.38..153488.39 rows=1 width=0) (actual time=7672.508..7672.509 rows=1 loops=1)
   ->  Index Only Scan using logline_date on logline  (cost=0.43..145945.76 rows=3017046 width=0) (actual time=18.548..4084.455 
rows=2977593 loops=1)
         Index Cond: ((date >= '2016-01-15 00:00:00-05'::timestamp with time zone) AND (date   Bitmap Heap Scan on logline  (cost=31543.27..1222862.87 rows=3062173 width=0) (actual time=103.876..100675.372 rows=2977593 loops=1)
         Recheck Cond: ((date >= '2016-01-15 00:00:00-05'::timestamp with time zone) AND (date   Bitmap Index Scan on logline_date_brin  (cost=0.00..30777.73 rows=3062173 width=0) (actual time=103.079..103.079 rows=6968320 loops=1)
               Index Cond: ((date >= '2016-01-15 00:00:00-05'::timestamp with time zone) AND (date <= '2016-01-31 00:00:00-05'::timestamp with time zone))
 Planning time: 0.377 ms
 Execution time: 105805.567 ms
(9 rows)

The BRIN index was >600 times smaller than the Btree, but I didn't expect the execution time to be so much slower.

Does that mean BRIN is not for me, or am I doing something wrong?

I'm guessing when migrating you didn't import rows ordered by date. You could check this by issuing

select * from logline;

And check if date looks like it's increasing monotonically. If that's not the case, you could try sorting the table, for example:

select * into logline2 from logline order by date asc;

... create index on second table ...

CREATE index logline2_date_brin on logline2 using BRIN(date)

... and try 'luck' with second table:

EXPLAIN ANALYZE SELECT COUNT(*) from logline2 where date BETWEEN '2016-01-15' and '2016-01-31';

If the time is drastically better, perfect.

If you have disk space limitations, you should also check extension cstore_fdw. It's really good for analytics and can store data in compressed form. It features indexing similar to BRIN, but has some limitations: you can only append data and transactions are not supported.

StackExchange Database Administrators Q#130819, answer score: 5