Hi,
I am planning to use as I search based on timestamptz fields.
There are millions of records.I refer
https://www.percona.com/blog/2019/07/16/brin-index-for-postgresql-dont-forget-the-benefits
I execute this on the AWS RDS instance. Is there something in the plan I
should pay attention to ? I notice the Execution Time.
Thanks,
Mohan
INSERT INTO testtab (id, date, level, msg) SELECT g, CURRENT_TIMESTAMP + (
g || 'minute' ) :: interval, random() * 6, md5(g::text) FROM
generate_series(1,8000000) as g;
create index testtab_date_idx on testtab(date);
"Gather (cost=1000.00..133475.57 rows=1 width=49) (actual
time=848.040..862.638 rows=0 loops=1)"
" Workers Planned: 2"
" Workers Launched: 2"
" -> Parallel Seq Scan on testtab (cost=0.00..132475.47 rows=1 width=49)
(actual time=832.108..832.109 rows=0 loops=3)"
" Filter: ((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))"
" Rows Removed by Filter: 2666667"
"Planning Time: 0.238 ms"
"Execution Time: 862.662 ms"
explain analyze select * from public.testtab where date between '2019-08-08
14:40:47.974791' and '2019-08-08 14:50:47.974791';
"Gather (cost=1000.00..133475.57 rows=1 width=49) (actual
time=666.283..681.586 rows=0 loops=1)"
" Workers Planned: 2"
" Workers Launched: 2"
" -> Parallel Seq Scan on testtab (cost=0.00..132475.47 rows=1 width=49)
(actual time=650.661..650.661 rows=0 loops=3)"
" Filter: ((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))"
" Rows Removed by Filter: 2666667"
"Planning Time: 0.069 ms"
"Execution Time: 681.617 ms"
create index testtab_date_brin_idx on rm_owner.testtab using brin (date);
explain analyze select * from public.testtab where date between '2019-08-08
14:40:47.974791' and '2019-08-08 14:50:47.974791';
"Bitmap Heap Scan on testtab (cost=20.03..33406.84 rows=1 width=49)
(actual time=0.143..0.143 rows=0 loops=1)"
" Recheck 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))"
" -> 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"
