Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.
When you create spatial index, do not include more than one spatial column in the index, and position the spatial column last in the index definition.
Also, to include a spatial column in an index, the column must have a SRID constraint.
Indexes on spatial data can reduce the cost of evaluating relationships between geometries. For example, suppose that you are considering changing the boundaries of your sales regions and want to determine the impact on existing customers. To determine which customers are located within a proposed sales region, you could use the ST_Within method to compare a point representing each customer address to a polygon representing the sales region. Without any index, the database server must test every address point in the Customer table against the sales region polygon to determine if it should be returned in the result, which could be expensive if the Customer table is large, and inefficient if the sales region is small. An index including the address point of each customer may help to return results faster. If a predicate can be added to the query relating the sales region to the states which it overlaps, results might be obtained even faster using an index that includes both the state code and the address point.
Spatial queries may benefit from a clustered index, but other uses of the table should be considered before deciding to use a clustered index. Consider, and test, the types of queries that are likely to be performed to see whether performance improves with clustered indexes.
While you can create text indexes on a spatial column, they offer no advantage over regular indexes; regular indexes are recommended instead. Spatial columns cannot be included in a primary key, unique index, or unique constraint. When a non-indexable geometry is included in an index, it behaves as though no index was created and matches all index probes.