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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
