The following bug has been logged on the website:
Bug reference: 13791
Logged by: digoal
Email address: digoal@126.com
PostgreSQL version: Unsupported/Unknown
Operating system: CentOS 6.x x64
Description:
ostgres=# create unlogged table test(id serial8, info text, crt_time
timestamp);
CREATE TABLE
postgres=# alter sequence test_id_seq cache 10000000;
ALTER SEQUENCE
postgres=# create index idx_test on test using brin (id) with
(pages_per_range =1);
CREATE INDEX
$vi test.sql
insert into test (info) select null from generate_series(1,10000000);
$pgbench -M prepared -n -r -P 1 -f ./test.sql -c 1 -j 1 -t 1000
the brin index cann't auto update, when i create it 224k. and when test some
times elapse, it's also 224k.
and when i query , select * from test where id=1000;
postgres=# explain analyze select * from test where id=1000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=12.01..16.02 rows=1 width=48) (actual
time=47.930..73214.043 rows=1 loops=1)
Recheck Cond: (id = 1000)
Rows Removed by Index Recheck: 489999090
Heap Blocks: lossy=539248
-> Bitmap Index Scan on idx_test (cost=0.00..12.01 rows=1 width=0)
(actual time=47.818..47.818 rows=5392480 loops=1)
Index Cond: (id = 1000)
Planning time: 0.282 ms
Execution time: 73214.128 ms
(8 rows)