为什么我的Postgis不在几何字段上使用索引? [英] Why my postgis not use index on geometry field?

问题描述

postgresql 9.5 + postgis 2.2. 我首先创建一个表:

postgresql 9.5 + postgis 2.2 on windows. I firstly create a table:

CREATE TABLE points (
  id   SERIAL,
  ad   CHAR(40),
  name VARCHAR(200)
);

然后，添加一个几何字段'geom':

then, add a geometry field 'geom':

select addgeometrycolumn('points', 'geom', 4326, 'POINT', 2);

并在其上创建要点索引:

and create gist index on it:

CREATE INDEX points_index_geom ON points USING GIST (geom);

然后，我在表格中插入大约1,000,000点.

then, I insert about 1,000,000 points into the table.

我想查询距给定点给定距离内的所有点. 这是我的sql代码:

I want to query all points that within given distance from given point. this is my sql code:

SELECT st_astext(geom) as location FROM points
WHERE st_distance_sphere(
     st_geomfromtext('POINT(121.33 31.55)', 4326),
     geom) < 6000;

结果就是我想要的，但是太慢了. 当我在这段代码上explain analyze verbose时，我发现它不使用points_index_geom(解释显示seq扫描，没有索引).

the result is what I want, but it is too slow. when I explain analyze verbose on this code, I found it dose not use points_index_geom (explain shows seq scan and no index).

所以我想知道为什么它不使用索引，我应该如何改进?

So i want to know why it dose not use index, and how should i improve?

推荐答案

您不能期望ST_Distance_Sphere()在此查询上使用索引.您正在对geom字段的内容进行计算，然后对计算结果进行比较.在这种情况下，数据库可能不会使用索引，除非您拥有一个函数索引，该函数的计算与查询中的计算几乎相同.

You cannot expect ST_Distance_Sphere() to use an index on this query. You are doing a calculation on the contents of the geom field and then you are doing a comparison on the calculation result. Databases may not use an index in such a scenario unless you have a function index that does pretty much the same calculation as in your query.

查找距某点给定距离的位置的正确方法是使用 ST_DWithin

The correct way to find locations with in a given distance from some point is to use ST_DWithin

ST_DWithin —如果几何在指定范围内，则返回true 彼此的距离.对于几何单位，在空间单位中 参考和地理单位以米为单位，测量单位为 默认设置为use_spheroid = true(围绕球体的度量)，以实现更快的速度 选中，use_spheroid = false可沿球体进行测量.

ST_DWithin — Returns true if the geometries are within the specified distance of one another. For geometry units are in those of spatial reference and For geography units are in meters and measurement is defaulted to use_spheroid=true (measure around spheroid), for faster check, use_spheroid=false to measure along sphere.

和

此函数调用将自动包含边界框 比较将利用 几何形状.

This function call will automatically include a bounding box comparison that will make use of any indexes that are available on the geometries.

这篇关于为什么我的Postgis不在几何字段上使用索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！