The btree h3index_ops operator class registers support functions 1 (cmp) and 2 (sortsupport), but not 4 (equalimage). Without it PostgreSQL disables B-tree deduplication for every h3index index.
To be clear about scope: this adds no user-facing function and nothing outside the H3 Core API. It completes an operator class the extension already ships: the same one fixed in #190.
Impact
On a table of 26.4M rows keyed by res-7 cells (~721 rows per distinct cell, a common shape when attaching many records to a coarse grid):
| index state |
size |
| current |
830 MB |
after REINDEX alone |
566 MB |
after REINDEX with equalimage |
173 MB |
A 3.3x reduction on top of the reindex. The gain scales with duplication, so an index with few duplicate cells sees little or nothing (at R15 the gains are negligible).
Why it is safe
equalimage asserts that equality implies bitwise identity. PostgreSQL registers it for int4, int8, uuid, macaddr and text, and deliberately omits it for float8 (-0.0 = 0.0, differing bit patterns) and numeric (1.0 = 1.00, differing display scale) (i.e. exactly the types where equal values can differ in representation).
h3index_eq is PG_RETURN_BOOL(a == b) on the raw 64-bit value (h3/src/operators.c), with no normalization, scale or collation, and the type is declared LIKE = int8. There is no pair of distinct bit patterns that compares equal, so the unconditional-true form used by PostgreSQL's own btequalimage applies.
Verification
Running this in production on PostgreSQL 18 against the index above: bt_metap().allequalimage is true, posting lists are present,
bt_index_check(heapallindexed => true) passes, and results were cross-checked against forced sequential scans.
Offer
I have a patch: about 20 lines across h3/src/opclass_btree.c and h3/sql/install/11-opclass_btree.sql and would add regression tests covering both the dedup property and comparator direction, per the contributing note.
Happy to open a PR if this is something you would take.
The btree
h3index_opsoperator class registers support functions 1 (cmp) and 2 (sortsupport), but not 4 (equalimage). Without it PostgreSQL disables B-tree deduplication for everyh3indexindex.To be clear about scope: this adds no user-facing function and nothing outside the H3 Core API. It completes an operator class the extension already ships: the same one fixed in #190.
Impact
On a table of 26.4M rows keyed by res-7 cells (~721 rows per distinct cell, a common shape when attaching many records to a coarse grid):
REINDEXaloneREINDEXwith equalimageA 3.3x reduction on top of the reindex. The gain scales with duplication, so an index with few duplicate cells sees little or nothing (at R15 the gains are negligible).
Why it is safe
equalimageasserts that equality implies bitwise identity. PostgreSQL registers it forint4,int8,uuid,macaddrandtext, and deliberately omits it forfloat8(-0.0 = 0.0, differing bit patterns) andnumeric(1.0 = 1.00, differing display scale) (i.e. exactly the types where equal values can differ in representation).h3index_eqisPG_RETURN_BOOL(a == b)on the raw 64-bit value (h3/src/operators.c), with no normalization, scale or collation, and the type is declaredLIKE = int8. There is no pair of distinct bit patterns that compares equal, so the unconditional-true form used by PostgreSQL's ownbtequalimageapplies.Verification
Running this in production on PostgreSQL 18 against the index above:
bt_metap().allequalimageis true, posting lists are present,bt_index_check(heapallindexed => true)passes, and results were cross-checked against forced sequential scans.Offer
I have a patch: about 20 lines across
h3/src/opclass_btree.candh3/sql/install/11-opclass_btree.sqland would add regression tests covering both the dedup property and comparator direction, per the contributing note.Happy to open a PR if this is something you would take.