2

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 <= '2016-01-31 00:00:00-05'::timestamp with time zone))
         Heap Fetches: 5809
 Planning time: 0.293 ms
 Execution time: 7672.562 ms
(6 rows)


DROP index logline_date
CREATE index logline_date_brin on logline using BRIN(date)

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

 Aggregate  (cost=1230518.30..1230518.31 rows=1 width=0) (actual time=105789.131..105789.133 rows=1 loops=1)
   ->  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 <= '2016-01-31 00:00:00-05'::timestamp with time zone))
         Rows Removed by Index Recheck: 2899899
         Heap Blocks: lossy=696832
         ->  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 your where condition returns too few rows for the BRIN index to matter – Mihai Feb 29 '16 at 18:29
3

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.

  • Ok, you've hit the nail on the head. I created a second table and timings went to Planning time: 0.101 ms Execution time: 8249.896 ms. My date date can vary because timezones, but I didn't think that would be enough to skew the BRIN index. I guess I'll need a holding table and copy to the main table when the timezone differential has passed. – Thinkwell Feb 29 '16 at 20:18
  • Also, thanks for the tip about cstore_fdw. I'd never heard of that one before. That could be a good idea for my main table! – Thinkwell Feb 29 '16 at 20:21
  • You can also use table inheritance to have part of the data in cstore and part in regulart table and do queries on both at once. See drive.google.com/file/d/0B_mCJTCKj3AuX25UYVZ0aWpNWDQ/view slide "Compressing JSONB" and next. When the postgres table stops getting new data for a given date range, you can just move data into cstore. – hruske Feb 29 '16 at 20:40
  • 1
    All dates should be stored with the same timezone. Use a timestamp with timezone datatype if you want to preserve those timestamps. Postgres will store them all as whatever your database timezone is set to, this will prevent your rows from being scattered and should increase the performance of your BRIN. P.S. "BRIN Index" is redundant. That's like saying "ATM Machine" – Volte May 1 '17 at 21:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.