Comparison between BRIN and BTREE

Table and data:

CREATE TABLE spotify_track
(
    spotify_id VARCHAR(255) PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    href TEXT NOT NULL,
    ...
    created_date TIMESTAMP DEFAULT timezone('utc'::text, now()) NOT NULL,
    modified_date TIMESTAMP DEFAULT timezone('utc'::text, now()) NOT NULL,
    CONSTRAINT spotify_track_spotify_album_spotify_id_fk FOREIGN KEY (album_id) REFERENCES spotify_album (spotify_id)
);

SELECT COUNT(*) FROM spotify_track;

count
-----
37016603

Code

EXPLAIN ANALYSE
SELECT t.spotify_id
FROM spotify_track t
WHERE t.modified_date > '2016-10-24 00:00:00';

Indexing

-- cleanup
DROP INDEX spotify_track_modified_date_idx;

-- brin index
CREATE INDEX spotify_track_modified_date_idx
  ON spotify_track
  USING BRIN (modified_date);

-- btree index
CREATE INDEX spotify_track_modified_date_idx
  ON spotify_track
  USING BTREE (modified_date);

-- btree descending index
CREATE INDEX spotify_track_modified_date_idx
  ON spotify_track
  USING BTREE (modified_date DESC);

Results

No index

Seq Scan on spotify_track t  (cost=0.00..3146934.16 rows=1270300 width=23) (actual time=437789.687..800219.294 rows=1801222 loops=1)
Filter: (modified_date > '2016-10-24 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 35203683
Planning time: 2.352 ms
Execution time: 800770.528 ms

BRIN index

Bitmap Heap Scan on spotify_track t  (cost=10207.92..2229365.51 rows=1270699 width=23) (actual time=2177.755..291859.831 rows=1812531 loops=1)
Recheck Cond: (modified_date > '2016-10-24 00:00:00'::timestamp without time zone)
Rows Removed by Index Recheck: 15170
Heap Blocks: lossy=137224
->  Bitmap Index Scan on idx_spotify_track_modified_date  (cost=0.00..9890.24 rows=1270699 width=0) (actual time=724.805..724.805 rows=1373440 loops=1)
Index Cond: (modified_date > '2016-10-24 00:00:00'::timestamp without time zone)
Planning time: 49.718 ms
Execution time: 292388.712 ms

BTREE index

Index Scan using idx_spotify_track_modified_date on spotify_track t  (cost=0.56..29766.87 rows=294646 width=23) (actual time=1.796..4108.866 rows=330606 loops=1)
Index Cond: (modified_date > '2016-10-25 00:00:00'::timestamp without time zone)
Planning time: 0.469 ms
Execution time: 4223.034 ms

BTREE descending index

Index Scan using spotify_track_modified_date_idx on spotify_track t  (cost=0.56..128360.02 rows=1270712 width=23) (actual time=0.017..1284.323 rows=1812854 loops=1)
  Index Cond: (modified_date > '2016-10-24 00:00:00'::timestamp without time zone)
Planning time: 0.055 ms
Execution time: 2635.976 ms

Conclusion

BTREE descending index is the best, however it seems the normal accending is equally good, the difference is due constant '2016-10-25 00:00:00' chosen. It is obvious, that for the different constant, the winner would be the opposite.