I execute this on the AWS RDS instance. Is there something in the plan I should pay attention to ? I notice the Execution Time.
" -> Bitmap Index Scan on "testtab_date_brin_idx " (cost=0.00..20.03 rows=12403 width=0) (actual time=0.141..0.141 rows=0 loops=1)"
" Index Cond: ((date >= '2019-08-08 14:40:47.974791'::timestamp without time zone) AND (date <= '2019-08-08 14:50:47.974791'::timestamp without time zone))"
"Planning Time: 0.126 ms"
"Execution Time: 0.161 ms"
What's your question exactly? If you have confidence that correlation will remain high (insert only table, or occasional cluster/repack with cluster is done), then BRIN can be a good fit. If you do updates and deletes and new tuples (inserts and updates) come in and fill in those gaps left behind in early pages even though timestamp is high, then correlation will go down and brin will no longer be a good fit.
Note- timestamp *with* timezone is recommended.