Thread: BUG #13791: postgresql 9.5 beta2 brin bug (cann't auto update)
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)
digoal@126.com wrote: > 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. Yeah, index summarization is not immediate for block ranges that are not already summarized. You can invoke summarization by running VACUUM on the table, or by using the brin_summarize_new_values() function. That would improve query execution too. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services