Indexes

B-tree (tpcpoint_btree_ops, tpcpatch_btree_ops, tpcbox_btree_ops) and hash (tpcpoint_hash_ops, tpcpatch_hash_ops) operator classes support equality and ordering predicates. The R-tree GiST and quadtree / kd-tree SP-GiST operator classes accelerate the bbox-based predicates (&&, @>, <@, ~=, -|-) on all three of tpcbox, tpcpoint, and tpcpatch:

-- GiST (R-tree)
CREATE INDEX ON my_table USING gist(my_tpcpoint_column);
CREATE INDEX ON my_table USING gist(my_tpcpatch_column);

-- SP-GiST quadtree (default) or kd-tree
CREATE INDEX ON my_table USING spgist(my_tpcpoint_column);
CREATE INDEX ON my_table USING spgist(my_tpcpoint_column tpcpoint_kdtree_ops);

SP-GiST opclasses store an stbox internally (the pcid filter is recovered by the operator's recheck), so a query against an index on a column whose values mix multiple pcids will return a slightly larger set of candidate rows for recheck — a non-issue when each table holds values of a single schema, which is the common case.