Is my spatial index being used?

Is my spatial index being used?

  • Comments 10

I've seen a problem commonly phrased something like:

I have X million records in a spatial table, and have created a spatial index, yet a simple select query takes me Y hours to complete.

Ouch!

If the problem is this extreme, the solution is usually pretty consistent: the index isn't being used.  You can tell pretty easily by looking at the query plan.  For example, take a query like this:

SELECT *
FROM T
WHERE g.STIntersects(@g) = 1

If the index isn't being used, you'd expect a query plan that looks like this:

image

If the index is being used, then you'll see something completely different:

image

You can generally tell just from the complexity of the plan the index has been picked up, but if you look closely at the highlighted portion you can be sure:

image

Note the "(Spatial)" annotation on the index seek.

Okay, so what do we do if the index isn't being used?  Well, we can hint it.  If our spatial index were named "g_idx", we could rewrite the query above as:

SELECT *
FROM T WITH(INDEX(g_idx))
WHERE g.STIntersects(@g) = 1

Making sure the index is being used isn't the end of spatial query tuning, but it can make the world of difference.  Quoting for a recent thread in the spatial forum:

The query went from over an hour to less than 2 seconds. I don't believe that in my 25+ year career that I can lay claim to a 180,000+% increase in performance.

That's pretty sweet.  Go forth and index.

Cheers,
-Isaac

  • Ah, sweet - that's provided my fix of spatial indexing blogs... at least, for now.

    Two questions. WHY do you need to hint the index in certain cases, and how do you know which cases you need to? The description above seems to suggest that you need to check the query plan on a case-by-case basis to find out if a hint is required or not. Shouldn't the query optimiser automatically choose the execution plan that hits the index, if that is the best plan?

    If the plan is not being generated correctly, should you always include an index hint in all queries against tables containing a spatial index, just to make sure?

  • Howdy,

    The optimizer is cost-based---it tries to pick the cheapest plan---but it doesn't always make the right choice, primarily because it can't perfectly estimate the costs.  There are things we can do---some easy, some not---to improve this, but I suspect there will always be some cases where hinting is required.  (Note, of course, that hints are not spatial-specific things---even without spatial we sometimes need them.)

    But, no: you shouldn't always hint the spatial index.  For example, if there is some other, more selective predicate, then the system may be better off answering that first, and not using the spatial index.  Again, ideally the optimizer would always make the right choice for you, but things are rarely ideal.

    Cheers,

    -Isaac

  • I have the following query:

    SELECT * From StateLocations WITH(INDEX(SPATIAL_IDX2)) Where Location.STIntersects(geography::Parse('POLYGON ((-104.83291625976562 39.590344725176692, -104.81094360351562 39.611507220193744, -104.84184265136719 39.623672726564109, -104.86656188964844 39.623143835924566, -104.86106872558594 39.597223244607449, -104.84458923339844 39.5977523336187, -104.83291625976562 39.590344725176692))')) = 1

    -------

    Note: The Location field is a GEOGRAPHY type only with Point(Lat, Long) data. *Without* the index hint, the query takes up to a minute on a 64-bit Quad Xeon w/ 12GB RAM.

    ----------------

    When I add the hint, I get the following Sql error:

    Msg 8635, Level 16, State 3, Line 1

    The query processor could not produce a query plan for a query with a spatial index hint.  Reason: Spatial indexes do not support the comparand supplied in the predicate.  Try removing the index hints or removing SET FORCEPLAN.

    ----------------

    What can I do to fix this? I can't find anything even remotely useful regarding that error.

    I'd greatly appreciate any advice sent my way - (actually, post it here so other people with this issue may find it :) )

    - D

  • **** UPDATE ***

    I got my query to run by changing an option for the database - I don't think it made a difference which one (but for completeness: I changed the parameterization back and forth between the options simple and forced).

    And then it just worked - did I find a bug?

  • Hi DaL,

    I'm a little confused.  It sounds like setting parameterization to one value works, while setting it the other way does not.  Which setting is which?

    The error indicates that the query optimizer could not find a plan that satisfied the hint you provided.  That seems a little strange in your case...

    Cheers,

    -Isaac

  • *** MORE INFO ***

    I was able to duplicate the issue on another DB - this time I fixed it by changing a different setting: I used a script to set DB compatibility to 80 (pre-Spatial support - i think it gave an error but seemed to make the change anyway)... and then I set it back to 100... And it worked. My query now uses the index with or without the hint - both execute the exact same way. Could it possibly not have had all the spatial stuff for Sql2008 enabled if the runlevel wasn't applied/pickedup or something? (Though it let me use GEOGRAPHY for my column types...)  (if that didn't sound like blind retarded speculation, i don't know what does :P)

  • Just an FYI for those having performance issues with spatial indexes.  The Query Optimizer (QO) team has looked into the issue of poor costing decisions with spatial indexes, and a fix is being included in the March cumulative update (likely called Cumulative Update 3 for SQL Server 2008).  My guess, and hope, is that this will obviate the need to hint spatial indexes with most datasets.

    Reference URL:

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=367772

  • Hi guys i have problems with such query using the spatial index.

    DECLARE @g as geometry;

    SET @g = geometry::STGeomFromText('LINESTRING(20000 20000, 60000 60000)', 2);

    SELECT ID, GUID, [GIS].[dbo].[GRID].GRID_GEOMETRY from [GIS].[dbo].[GRID] WITH(INDEX(g_idx))

    WHERE @g.STIntersects(GRID_GEOMETRY) = 1 OR GRID_GEOMETRY.STContains(@g) = 1

    The error is

    Msg 8635, Level 16, State 3, Line 1

    The query processor could not produce a query plan for a query with a spatial index hint.  Reason: Spatial indexes do not support the comparand supplied in the predicate.  Try removing the index hints or removing SET FORCEPLAN.

    but if i remove the "OR GRID_GEOMETRY.STContains(@g) = 1

    " part in my query or replace OR with AND the quesry works just fine.

    What is the problem with OR in the query, help please?

  • Hi my self is Amit & I am working as GIS Deverloper in one of the Government firm.

    We are in the process of migrating our GIS DB on SQL 2008 & I am performing some test with spatial datatype in SQL 2008

    When I try to make query using spatial Index it throw the following error.

    "Msg 8622, Level 16, State 1, Line 10

    Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN."

    SQL Query

    Select [GEOMETRY] from [dbo].[Demographic_Risk]

    WITH(INDEX(SK_Demographic_Risk_GEOMETRY_SIX))

    Where ID=1093

    Please provide any suggestion ASAP at

    AGorawadia@derbys-fire.gov.uk

  • We just installed Cumulative Update 4 and it looks like the spatial indexes are still not being used in some instances.  Does anyone have any more info?

    Thank you.

Page 1 of 1 (10 items)