What Is a SP-GiST Indexes in PostgreSQL?
Space-partitioned GiST supports structures such as quadtrees, k-d trees, and radix tries, dividing some data domains into independent regions.
How the SP-GiST Indexes Algorithm Works
Space-partitioned GiST supports structures such as quadtrees, k-d trees, and radix tries, dividing some data domains into independent regions.
When to Use SP-GiST Indexes
Supported point, geometric, and prefix-search workloads when the operator class matches the query.
How to Create a PostgreSQL SP-GiST Indexes
Review data quality, existing structures, feature support, build time, and deployment requirements before creating an index on a production table.
CREATE INDEX points_location_spgist_idx ON points USING spgist (location);SP-GiST Indexes Query Example
This example demonstrates the matching query shape. Check the execution plan to confirm that the engine can use the index.
SELECT point_id FROM points WHERE location <@ box(point(0,0), point(10,10));Queries Supported by SP-GiST Indexes
Supported point, geometric, and prefix-search workloads when the operator class matches the query. A similar-looking predicate may not use the same access path; operators, expressions, key order, and data type all matter.
Read Performance, Storage, and Write Costs
Not a general B-tree/GiST replacement. Performance depends on operator class and distribution. Indexes reduce some search work but require storage and add maintenance to writes.
SP-GiST Indexes Limitations and Common Mistakes
Only supported data types/operators work; nearest-neighbor capability is operator-class-specific.
Do not assume that an index being present means the optimizer chose it or that the query became selective.
How to Measure and Tune SP-GiST Indexes
Test representative and skewed data; inspect rechecks, index size, and actual plans.
Compare representative plans and timings before and after, including concurrent writes and realistic data volumes.
SP-GiST Indexes Questions
Will the database always use this index?
No. The optimizer weighs selectivity, estimated costs, statistics, cache state, and the amount of data returned.
What should I verify before production?
Confirm version support, query compatibility, write overhead, build/rollback procedures, and index behavior during backup and recovery.
Official PostgreSQL Index Documentation
Check PostgreSQL documentation: Index Types for release-specific requirements and supported operators.