The pgpro_iheap extension provides the iheap (indexed heap) table access method that is based on the built-in heap access method and integrates a BRIN index.

The iheap access method is designed to accelerate table scans when the planner selects a sequential scan plan. This scenario typically occurs when no suitable indexes are available for the query. A BRIN index is built into the iheap access method and is not user-controllable. In the rest of the document, the BRIN index integrated within iheap is referred to as the iheap index.

The iheap index is stored in a dedicated index fork of a table. This fork is an additional storage structure, analogous to the visibility map and the free space map. Within this fork, a group of BRIN indexes defined on columns is created for every N pages of the table. The BRIN index group stores the minimum and maximum values and is created for all compatible table columns. Compatible columns are those with fixed or variable length not exceeding 64 bytes. The BRIN index is not utilized for other column types. The number of pages per BRIN index group is configured using the pages_per_index index storage parameter. The default value is 256, and it can be set to any integer between 64 and 512, inclusive. A single iheap index page can store multiple BRIN index groups. The number of groups per page is determined dynamically based on the total size of indexed table columns. Consequently, one iheap page can store index information for tens or even hundreds of thousands of table pages with the standard heap access method.

When a sequential scan is executed, the system evaluates scan conditions against the BRIN index group associated with the iheap index columns prior to reading table data. If the conditions are not satisfied by the index group, the corresponding table block consisting of the number of pages set in pages_per_index is completely skipped during read operations. Skipping such blocks reduces the amount of data read from disk, thereby improving sequential scan performance.

The iheap index is most effective in the following scenarios:

The pgpro_iheap extension is included in Postgres Pro Enterprise. To enable pgpro_iheap, create the extension using the following query:

CREATE EXTENSION pgpro_iheap;

The pgpro_iheap extension provides the following functions:

In the following example, the extension is created, a table with the iheap access method is created, and various operations are performed on that table.

-- Create the pgpro_iheap extension
CREATE EXTENSION pgpro_iheap;

-- Create a table with the iheap access method
CREATE TABLE test (i int, t text, b bigint) USING iheap;

-- Add data (NULL is added to the third column in the first page)
INSERT INTO test (i, t, b) VALUES (1, '1', NULL);
INSERT INTO test (SELECT i, i::text, i % 100 FROM generate_series(2, 100000) AS i);

-- Retrieve headers of the iheap index pages
SELECT i AS blkno, (info).col_group_size, (info).col_groups_num FROM
  (SELECT i, iheap_page_info('test', i) AS info FROM
    (SELECT i FROM generate_series(0, ((pg_relation_size('test', 'idx') / 8192) - 1)::int) AS i));

 blkno | col_group_size | col_groups_num
-------+----------------+----------------
     0 |             32 |            255
(1 row)

-- Retrieve information about the iheap index groups
SELECT i AS blkno, (info).group_num, (info).flags FROM
  (SELECT i, iheap_page_groups_info('test', i) AS info FROM
    (SELECT i FROM generate_series(0, ((pg_relation_size('test', 'idx') / 8192) - 1)::int) AS i))
  WHERE array_position((info).flags, 'set') > 0;

 blkno | group_num | flags
-------+-----------+-------
     0 |         0 | {set}
     0 |         1 | {set}
     0 |         2 | {set}
(3 rows)

-- Retrieve information about columns of the iheap index groups
SELECT i AS blkno, group_num, (info).attno, (info).flags FROM
  (SELECT i, group_num, iheap_page_items_info('test', i, group_num) AS info FROM
    (SELECT i, (info).group_num AS group_num FROM
      (SELECT i, iheap_page_groups_info('test', i) AS info FROM
        (SELECT i FROM generate_series(0, ((pg_relation_size('test', 'idx') / 8192) - 1)::int) AS i))
      WHERE array_position((info).flags, 'set') > 0));

 blkno | group_num | attno |       flags
-------+-----------+-------+-------------------
     0 |         0 |     1 | {set}
     0 |         0 |     2 | {}
     0 |         0 |     3 | {set,"has nulls"}
     0 |         1 |     1 | {set}
     0 |         1 |     2 | {}
     0 |         1 |     3 | {set}
     0 |         2 |     1 | {set}
     0 |         2 |     2 | {}
     0 |         2 |     3 | {set}
(9 rows)

-- Search for a value using the iheap index
SELECT * FROM test WHERE i = 99999;

   i   |   t   | b
-------+-------+----
 99999 | 99999 | 99
(1 row)