How BRIN Indexes Use allnulls and hasnulls to Slash PostgreSQL Scans

This third installment of a series on PostgreSQL 9.5's new BRIN index type focuses on practical usage of the index itself, following earlier articles that compared BTREE and BRIN performance and explored hybrid indexing strategies.

BRIN indexes consist of three parts: a metadata page, a revmap page, and regular pages. The regular page holds the core index entries, each containing the minimum and maximum values for a range of heap pages as well as two Boolean flags: allnulls (true if every indexed column value in the range is NULL) and hasnulls (true if the range contains at least one NULL value).

To illustrate, a table t4 with 2,000,001 rows is created. The id column is sequential, while id1 mirrors id for most rows but includes a few NULLs. A BRIN index on id1 is built with the default pages_per_range=128. The table layout and the index's metapage are shown below:

Inspecting the regular page entries reveals the following patterns:

Block 0 (heap pages 0‑128): allnulls = f, hasnulls = f – all values are non‑NULL.

Blocks 128, 4352, 4480: allnulls = f, hasnulls = t – the range contains both NULL and non‑NULL values.

All remaining blocks: allnulls = t, hasnulls = f – every value in the range is NULL.

The first four blocks together cover 512 heap pages that contain non‑NULL id1 values, so their entries also store min/max values.

When counting rows where id1 is non‑NULL, PostgreSQL’s executor uses the allnulls and hasnulls flags to skip irrelevant heap pages. Only 512 of the 8,850 heap pages are scanned, corresponding to four BRIN entries, and a total of 514 buffers are read (2 for the index itself plus 512 for the selected heap pages).

Reversing the data—updating most NULLs to non‑NULL and a few non‑NULLs to NULL—causes the index entry count to rise from 70 to 139 because PostgreSQL’s MVCC retains old tuple versions and does not immediately free heap pages. Even after a manual VACUUM, the visibility map changes but the dead tuples remain, leading to many additional index entries and reduced performance gains.

Post‑update analysis shows 9,218 buffer reads (2 for the index and 9,216 for heap pages). Because many entries now have hasnulls = t, the executor must scan a large portion of the table, illustrating how MVCC‑induced bloat can diminish the BRIN advantage.

After performing VACUUM FULL and REINDEX, the table is rebuilt, dead tuples are removed, and the BRIN index contains only the current data entries. Re‑executing the count query again scans only 514 buffers (the same 512 heap pages as before), confirming that proper maintenance restores the index’s effectiveness.

Conclusion

BRIN index entries’ allnulls and hasnulls flags enable PostgreSQL to skip large numbers of heap pages, delivering significant query‑time reductions.

However, MVCC bloat can generate many redundant index entries, limiting the practical performance benefit.

Regular maintenance—vacuuming, reindexing, and removing dead tuples—is essential to keep BRIN‑based queries efficient.