Обсуждение: 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