Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
3  
If you want to see where an index makes things slower in PostGIS SET enable_seqscan = off. This will force PostgreSQL to use indexes every time. Compare speeds with it on. –  Sean Jul 27 '11 at 14:25
    
Thanks for starting this thread. I've been pouring over information on the net, trying to figure out why my organization (government) does not make use of spatial (or even attribute) indices on their oracle/sde feature classes and tables. Now I have a few arguments to present to them so taht I don't have to pull my hair out, waiting for a query to resolve itself. –  Mike May 13 at 18:22

3 Answers 3

up vote 7 down vote accepted

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.

share|improve this answer
    
cheers LR1234567. I think this is what I was looking for. –  mapoholic Jul 28 '11 at 13:08

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 
share|improve this answer
    
thanks very much for that. It may seem obvious when you say it, but my first thought would be to run a spatial query not an attribute-only. +1 for this! –  mapoholic Jul 29 '11 at 10:04

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...

share|improve this answer
3  
(+1) Do I detect a little cynicism in that last remark? :-) –  whuber Jul 27 '11 at 13:37
    
Not at all ;-) But dropping/recreating carefully tuned indices is a useful answer to "Why was X much time spent on database changes"? –  geographika Jul 27 '11 at 13:52
    
Thanks geographica- and I do agree with whuber's remark! ;-) I understand that you would drop/disable spatial indices when bulk loading - or all indices for the matter, but you can't think of a reason why you would ever do a spatial query WITHOUT using a spatial index? If a table is small enough, using the index may not make a difference- fair enough- but opting not to use the index?. Don't know, I guess I am just perplexed more with the existence of the PostGIS non-spatial-index functions... –  mapoholic Jul 27 '11 at 14:07
2  
If a table is small enough and fits into memory, using an index necessitates random disk access that is more costly than doing a sequential scan. wiki.postgresql.org/wiki/… –  Sean Jul 27 '11 at 14:26
2  
@mapoholic - the _ST_Contains could be left over from when you had to manually do a prefilter of your data, judging from old.nabble.com/… –  geographika Jul 27 '11 at 14:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.