空间索引减慢查询 [英] Spatial index slowing down query

问题描述

背景

我有一个表，其中包含代表客户区域的 POLYGONS/MULTIPOLYGONS:

• 该表包含大约 8,000 行
• 大约 90% 的多边形是圆形
• 其余的多边形代表一个或多个州、省或其他地理区域.这些形状的原始多边形数据是从 ) 具有不同程度的容忍度.对新列运行与上述相同的查询会产生以下结果:
  • 不减少:12649 毫秒
  • 减少了 10:7194 毫秒
  • 减少 20:6077 毫秒
  • 减少 30:4793 毫秒
  • 减少了 40:4397 毫秒
  • 减少 50:4290 毫秒

  显然朝着正确的方向前进，但降低精度似乎是一个不雅的解决方案.这不是索引应该用于的吗?对于这样一个基本查询，执行计划似乎仍然异常复杂.

  空间索引

  出于好奇，我去掉了空间索引，结果惊呆了:

  1. 在没有索引的情况下，查询速度更快(不减少 3 秒以下，减少容差 >= 30 时不到 1 秒)
  2. 执行计划看起来非常简单:

  我的问题

  1. 为什么我的空间索引会减慢速度?
  2. 为了加快查询速度，真的有必要降低多边形复杂性吗?降低精度可能会导致问题出现，而且似乎不会很好地扩展.

  其他说明

  • 已应用 SQL Server 2008 R2 Service Pack 1
  • 进一步研究建议 在存储过程中运行查询.试过了，似乎没有任何改变.

  解决方案

  我的第一个想法是检查索引的边界坐标；看看它们是否涵盖了您的整个几何图形.其次，根据我的经验，默认 16MMMM 的空间索引性能很差.我不确定为什么这是默认设置.我在this answer上写了一些关于空间索引调优的文章.

  首先确保索引涵盖所有几何图形.然后尝试将每个对象的单元格减少到 8 个.如果这两件事都没有提供任何改进，那么在我上面链接的答案中运行空间索引调整过程可能是值得的.

  最终的想法是，州边界有这么多顶点，并且有很多州边界多边形要测试相交，如果不减少它们，很可能需要很长时间.

  哦，既然已经两年了，从 SQL Server 2012 开始，现在有一个 GEOMETRY_AUTO_GRID tessellation 为您进行索引调整，并且大部分时间都做得很好.

  Background

  I have a table that contains POLYGONS/MULTIPOLYGONS which represent customer territories:

  • The table contains roughly 8,000 rows
  • Approximately 90% of the polygons are circles
  • The remainder of the polygons represent one or more states, provinces, or other geographic regions. The raw polygon data for these shapes was imported from US census data.
  • The table has a spatial index and a clustered index on the primary key. No changes to the default SQL Server 2008 R2 settings were made. 16 cells per object, all levels medium.

  Here's a simplified query that will reproduce the issue that I'm experiencing:

  DECLARE @point GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT (-76.992188 39.639538)', 4326)
  
  SELECT terr_offc_id
  FROM tbl_office_territories
  WHERE terr_territory.STIntersects(@point) = 1
  

  What seems like a simple, straightforward query takes 12 or 13 seconds to execute, and has what seems like a very complex execution plan for such a simple query.

  In my research, several sources have suggested adding an index hint to the query, to ensure that the query optimizer is properly using the spatial index. Adding WITH(INDEX(idx_terr_territory)) has no effect, and it's clear from the execution plan that it is referencing my index regardless of the hint.

  Reducing polygons

  It seemed possible that the territory polygons imported from the US Census data are unnecessarily complex, so I created a second column, and tested reduced polygons (w/ Reduce() method) with varying degrees of tolerance. Running the same query as above against the new column produced the following results:

  • No reduction: 12649ms
  • Reduced by 10: 7194ms
  • Reduced by 20: 6077ms
  • Reduced by 30: 4793ms
  • Reduced by 40: 4397ms
  • Reduced by 50: 4290ms

  Clearly headed in the right direction, but dropping precision seems like an inelegant solution. Isn't this what indexes are supposed to be for? And the execution plan still seems strangly complex for such a basic query.

  Spatial Index

  Out of curiosity, I removed the spatial index, and was stunned by the results:

  1. Queries were faster WITHOUT an index (sub 3 sec w/ no reduction, sub 1 sec with reduction tolerance >= 30)
  2. The execution plan looked far, far simpler:

  My questions

  1. Why is my spatial index slowing things down?
  2. Is reducing my polygon complexity really necessary in order to speed up my query? Dropping precision could cause problems down the road, and doesn't seem like it will scale very well.

  Other Notes

  • SQL Server 2008 R2 Service Pack 1 has been applied
  • Further research suggested running the query inside a stored procedure. Tried this and nothing appeared to change.

  解决方案

  My first thoughts are to check the bounding coordinates of the index; see if they cover the entirety of your geometries. Second, spatial indexes left at the default 16MMMM, in my experience, perform very poorly. I'm not sure why that is the default. I have written something about the spatial index tuning on this answer.

  First make sure the index covers all of the geometries. Then try reducing cells per object to 8. If neither of those two things offer any improvement, it might be worth your time to run the spatial index tuning proc in the answer I linked above.

  Final thought is that state boundaries have so many vertices and having many state boundary polygons that you are testing for intersection with, it very well could take that long without reducing them.

  Oh, and since it has been two years, starting in SQL Server 2012, there is now a GEOMETRY_AUTO_GRID tessellation that does the index tuning for you and does a great job most of the time.

  这篇关于空间索引减慢查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！