From: | Mohan Radhakrishnan <radhakrishnan(dot)mohan(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | BRIN index on timestamptz |
Date: | 2021-04-23 08:31:38 |
Message-ID: | CAOoXFP_YK8Mo80YB4-bQmKZtAkjHgGOBvGm67=P7gtoqoQNuTw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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"
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2021-04-23 08:48:24 | Re: client waits for end of update operation and server proc is idle |
Previous Message | Matthias Apitz | 2021-04-23 08:15:05 | client waits for end of update operation and server proc is idle |