BRIN index on timestamptz

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"

Responses

Browse pgsql-general by date

  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