When should you NOT use a spatial index?

Question

I am asking this because I was mainly working with Oracle but for the past year I've been doubling with PostGIS and SQLServer 2008. Most spatial functions in Oracle won't work without a spatial index returning the ORA-13226 error:

13226, 00000, "interface not supported without a spatial index" //*Cause: The geometry table does not have a spatial index. // *Action:Verify that the geometry table referenced in the spatial operator has a spatial index on it.

To me this makes sense. You run a spatial query=you must have a spatial index. But as far as I understand, neither PostGIS not SQL Serve require this. PostGIS even seems to have functions (_* e.g. _STContains) that EXPLICITLY won't use the spatial index.

So the question is- are there any cases where you should NOT use a spatial index?. Not necessarily whether is a 'take it or leave it' approach i.e. it won't make any difference, but where NOT using the spatial index will impove performance? To me, the last sentence is a contradiction in terms but otherwise why PostGIS would provide these functions?

Answer 1

If your dataset is added to and updated often, then INSERT, DELETE and UPDATE statements which cause the index to be rebuilt may slow the database down.

For bulk inserts, such as loading the entire OSM dataset into a database, it may be quicker to drop the indices and create them again afterwards.

If it is more efficient to ignore an index (for example the table is small enough to be loaded into memory), the database query processor should do this automatically.

I'd expect the main reason to allow queries to be run without a spatial index is to measure the performance benefits you get by using an index, without having to drop it.

Finally if you want to show a huge performance boost to queries and map displays you may want to delay creating indices to an opportune moment in system development...

Answer 2

mapoholic,

Generally speaking, there isn't a reason to do a spatial query without a spatial index unless you are dealing with really small tables. Still though you would use the ST_ which don't use an index but do have the && indexable short circuit box operators. the functions that start with _ST are not meant to be used by end users. The reason they exist is because they have to. PostGIS spatial indexes use SQL inlining to force use of index -- the _ST is usually done by GEOS and the && is the index that may get reordered. So the _ST are really an implementation artifact.

so in short- its not one function so that the index operation can be reordered to happen all at once before the more intense spatial check.

Answer 3

I think this is implied, but I would NOT use a spatial index for a query when I had a non-spatial index that I could use instead. For example, I have 2,113,450 points that span the United States loaded into a table. If I wanted to pull all of the points that were within the state of Alaska, I could either do a spatial query that used the GIST index on the point geometries to compare against the geometry of the state of Alaska, OR, I could just use the "state_alpha" field in the point data (which is also indexed) to return all of the points that have "state_alpha" = 'AK'.

"Where is the spatial part of this", you ask? Well, if I need to do some further spatial analysis on the Alaska_points after I collect them, it's faster to gather up those point geometries using a non-spatial query first. It also means that for really big data sets, you benefit from adding a lookup field (or table). Again, I know this is probably obvious to everyone aleady, I only mention it because I encountered it in the past with global data sets that were only spatially indexed, and where a common query was "all features within a country". We gained alot of performance by adding an indexed country_fips field.

Below are some results from EXPLAIN ANALYZE that prove the point. (NOTE: I tried to make the spatial query as efficient as possible by using a BBOX query. Using the state outlines would have only made it slower.)

# explain analyze select count(*) from gnis_names where state_alpha = 'AK';
Aggregate  (cost=57359.45..57359.46 rows=1 width=0) (actual time=76.606.. 76.607 rows=1 loops=1)
<snip>
Total runtime: 76.676 ms

# explain analyze select count(*) from gnis_names where the_geom && GeomFromText('POLYGON((-179.14734 51.219862,-179.14734 71.3525606439998,179.77847 71.3525606439998,179.77847 51.219862,-179.14734 51.219862))',4326);
Aggregate  (cost=27699.86..27699.87 rows=1 width=0) (actual time=86.523..86.524 rows=1 loops=1)
<snip>
Total runtime: 86.584 ms 

Answer 4

Just noticed this statement

To me this makes sense. You run a spatial query=you must have a spatial index

To me this doesn't make sense at all and I think both SQL Server and Postgis do a better job or at least do not bother you with performance details. In fact, both SQL Server and Postgis sometimes do not even use the spatial index at all (revert to full table scan).

For Oracle, you must create the index and therefore you must fill user_sdo_geom_metadata.

Just comparing this with alphanumeric indexes, they are there for performance reasons, your SQL statement should work with and without it.

In an Oracle database, drop the index and you will get loads of errors and apps that won't be able to use spatial queries, hence fail to work.