Indexes & Query Performance
Non-B-Tree Indexes
Non-B-tree families encode different questions: exact equality, set membership, geometric relations, text containment, or value summaries over page ranges. Choose by operator semantics and workload, not by a generic “faster index” hierarchy.
- Hash indexes target equality.
- Bitmaps represent sets compactly.
- Inverted indexes map terms to rows.
- Spatial trees preserve spatial predicates.
- Block-range indexes summarize storage.
- Operator support is the contract.
| Family | Natural question | Strong fit | Tradeoff / recheck |
|---|---|---|---|
| Hash | key = ? | Exact-key lookup | No natural range/order; collisions |
| Bitmap | set A AND/OR set B | Warehouse filters and index combination | Memory/maintenance; order lost |
| Inverted / GIN | contains term/element | JSON, arrays, full text | Large postings and write amplification |
| Spatial / GiST / SP-GiST | overlaps, contains, nearest | Geometry and partitionable spaces | May be lossy; operator-class dependent |
| Block-range / BRIN | which page ranges may match? | Huge append-correlated tables | False positives and heap recheck |
-- Isolated search, spatial, and telemetry workload; these are not canonical commerce tables.
-- Requires PostGIS for the geometry type and GiST spatial operator class.
CREATE TABLE search_documents (
document_id BIGINT PRIMARY KEY,
attributes JSONB NOT NULL
);
CREATE TABLE service_regions (
region_id BIGINT PRIMARY KEY,
service_area geometry(POLYGON, 4326) NOT NULL
);
CREATE TABLE telemetry_events (
event_id BIGINT PRIMARY KEY,
occurred_at TIMESTAMPTZ NOT NULL,
payload JSONB NOT NULL
);
CREATE INDEX search_documents_attrs_gin ON search_documents USING gin (attributes);
CREATE INDEX service_regions_area_gist ON service_regions USING gist (service_area);
CREATE INDEX telemetry_events_occurred_brin ON telemetry_events USING brin (occurred_at);
SELECT document_id FROM search_documents
WHERE attributes @> '{"color":"blue"}'::jsonb;The GIN index answers containment by postings; it is not a substitute for a B-tree ordering of a scalar extracted from JSON. The BRIN example is most promising when creation time follows physical append order; correlation loss increases candidate page ranges.