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?