BRIN INDEX value - Mailing list pgsql-hackers

When creating a brin index, it shows an interesting behavior when used
with VACUUM.

First, I created a brin index after inserting data.
===============================================================
DROP TABLE t1;
DROP TABLE
CREATE TABLE t1(i int);
CREATE TABLE
INSERT INTO t1 VALUES (generate_series(1, 100000));
INSERT 0 100000
CREATE INDEX brinidx ON t1 USING brin (i);
CREATE INDEX
SELECT * FROM brin_revmap_data(get_raw_page('brinidx', 1)) WHERE pages != '(0,0)'::tid;pages 
-------(2,1)(2,2)(2,3)(2,4)
(4 rows)

SELECT * FROM brin_page_items(get_raw_page('brinidx', 2), 'brinidx');itemoffset | blknum | attnum | allnulls | hasnulls
|placeholder |       value       
 
------------+--------+--------+----------+----------+-------------+-------------------         1 |      0 |      1 | f
     | f        | f           | {1 .. 28928}         2 |    128 |      1 | f        | f        | f           | {28929
..57856}         3 |    256 |      1 | f        | f        | f           | {57857 .. 86784}         4 |    384 |      1
|f        | f        | f           | {86785 .. 100000}
 
(4 rows)

SELECT * FROM brin_revmap_data(get_raw_page('brinidx', 1)) WHERE pages != '(0,0)'::tid;pages 
-------(2,1)(2,2)(2,3)(2,4)
(4 rows)

VACUUM;
VACUUM
SELECT * FROM brin_revmap_data(get_raw_page('brinidx', 1)) WHERE pages != '(0,0)'::tid;pages 
-------(2,1)(2,2)(2,3)(2,4)
(4 rows)

SELECT * FROM brin_page_items(get_raw_page('brinidx', 2), 'brinidx');itemoffset | blknum | attnum | allnulls | hasnulls
|placeholder |       value       
 
------------+--------+--------+----------+----------+-------------+-------------------         1 |      0 |      1 | f
     | f        | f           | {1 .. 28928}         2 |    128 |      1 | f        | f        | f           | {28929
..57856}         3 |    256 |      1 | f        | f        | f           | {57857 .. 86784}         4 |    384 |      1
|f        | f        | f           | {86785 .. 100000}
 
(4 rows)
===============================================================

As you can see brin index value for block 384 or more is {86785.. 100000}. Good.

However I inserted data *after* creating index, the value is
different.
===============================================================
psql -e -f test.sql test
Pager usage is off.
DROP TABLE t1;
DROP TABLE
CREATE TABLE t1(i int);
CREATE TABLE
CREATE INDEX brinidx ON t1 USING brin (i);
CREATE INDEX
SELECT * FROM brin_revmap_data(get_raw_page('brinidx', 1)) WHERE pages != '(0,0)'::tid;pages 
-------(2,1)
(1 row)

SELECT * FROM brin_page_items(get_raw_page('brinidx', 2), 'brinidx');itemoffset | blknum | attnum | allnulls | hasnulls
|placeholder | value 
 
------------+--------+--------+----------+----------+-------------+-------         1 |      0 |      1 | t        | f
    | f           | 
 
(1 row)

INSERT INTO t1 VALUES (generate_series(1, 100000));
INSERT 0 100000
SELECT * FROM brin_revmap_data(get_raw_page('brinidx', 1)) WHERE pages != '(0,0)'::tid;pages 
-------(2,1)
(1 row)

VACUUM;
VACUUM
SELECT * FROM brin_revmap_data(get_raw_page('brinidx', 1)) WHERE pages != '(0,0)'::tid;pages 
-------(2,1)(2,2)(2,3)(2,4)
(4 rows)

SELECT * FROM brin_page_items(get_raw_page('brinidx', 2), 'brinidx');itemoffset | blknum | attnum | allnulls | hasnulls
|placeholder |      value       
 
------------+--------+--------+----------+----------+-------------+------------------         1 |      0 |      1 | f
    | f        | f           | {1 .. 28928}         2 |    128 |      1 | f        | f        | f           | {28929 ..
57856}        3 |    256 |      1 | f        | f        | f           | {57857 .. 86784}         4 |    384 |      1 |
f       | f        | f           | {1 .. 100000}
 
(4 rows)
===============================================================

How the index value for block 384 could be {1 .. 100000}?

I have tested with 9.5 alpha2 and 9.5-stable head as of today.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp