添加索引以加快Geocoder靠近搜索的速度 [英] Add indexes to speed up Geocoder near search
问题描述
在我的rails应用程序中，我具有允许查找与当前登录用户最接近的用户的功能。我正在为此使用Geocoder gem。在用户模型中，我的范围是这样的：
In my rails app I have functionality that allow to find users that are closest to currently logged in user. I'm using Geocoder gem for this. In User model I have scope like this:
scope :close_to, -> (user:, distance:) {
where.not(id: user.id)
.near([user.latitude, user.longitude], distance)
}
此方法效果很好，但对于大量用户而言速度较慢。当我调用此作用域时，它将生成以下sql查询：
This works very well but it is slow on larger collection of users. When I call this scope it generates the following sql query:
SELECT users.*, 6371.0 * 2 * ASIN(SQRT(POWER(SIN((48.471645 - users.latitude) * PI() / 180 / 2), 2) + COS(48.471645 * PI() / 180) * COS(users.latitude * PI() / 180) * POWER(SIN((-83.102801 - users.longitude) * PI() / 180 / 2), 2))) AS distance, MOD(CAST((ATAN2( ((users.longitude - -83.102801) / 57.2957795), ((users.latitude - 48.471645) / 57.2957795)) * 57.2957795) + 360 AS decimal), 360) AS bearing FROM "users" WHERE ("users"."id" != 43362) AND (users.latitude BETWEEN 39.4784289408127 AND 57.46486105918731 AND users.longitude BETWEEN -96.6674214298497 AND -69.5381805701503 AND (6371.0 * 2 * ASIN(SQRT(POWER(SIN((48.471645 - users.latitude) * PI() / 180 / 2), 2) + COS(48.471645 * PI() / 180) * COS(users.latitude * PI() / 180) * POWER(SIN((-83.102801 - users.longitude) * PI() / 180 / 2), 2)))) BETWEEN 0.0 AND 1000) ORDER BY distance ASC;
我正在尝试为此创建索引，但它们不起作用。我正在尝试以下组合：
I'm trying to create index for that but they not work. I was trying following combinations:
1.
add_index :users, [:id, :latitude]
add_index :users, [:id, :longitude]
2. add_index :users, [:id, :latitude, :longitude]
3. add_index :users, [:latitude]
add_index :users, [:longitude]
4. add_index :users, [:id, :latitude]
如何添加索引以加快查询速度？
How should I add index to speed up this query?
编辑：我忘记添加我的纬度和经度列为小数。
I forgot to add that my lattitude and longitude columns are decimals.
此查询的分析返回类似以下内容的内容：
ANALYZE of this query returns something like that:
Sort (cost=7141.66..7142.14 rows=191 width=327) (actual time=575.995..585.543 rows=36598 loops=1)
Sort Key: ((12742::double precision * asin(sqrt((power(sin((((((48.471645 - latitude))::double precision * 3.14159265358979::double precision) / 180::double precision) / 2::double precision)), 2::double precision) + ((0.662990616338754::double precision * cos((((latitude)::double precision * 3.14159265358979::double precision) / 180::double precision))) * power(sin(((((((-83.102801) - longitude))::double precision * 3.14159265358979::double precision) / 180::double precision) / 2::double precision)), 2::double precision)))))))
Sort Method: external merge Disk: 4672kB
-> Seq Scan on users (cost=0.00..7134.43 rows=191 width=327) (actual time=0.381..517.615 rows=36598 loops=1)
Filter: ((id <> 43362) AND (latitude >= 39.4784289408127) AND (latitude <= 57.46486105918731) AND (longitude >= (-96.6674214298497)) AND (longitude <= (-69.5381805701503)) AND ((12742::double precision * asin(sqrt((power(sin((((((48.471645 - latitude))::double precision * 3.14159265358979::double precision) / 180::double precision) / 2::double precision)), 2::double precision) + ((0.662990616338754::double precision * cos((((latitude)::double precision * 3.14159265358979::double precision) / 180::double precision))) * power(sin(((((((-83.102801) - longitude))::double precision * 3.14159265358979::double precision) / 180::double precision) / 2::double precision)), 2::double precision)))))) >= 0::double precision) AND ((12742::double precision * asin(sqrt((power(sin((((((48.471645 - latitude))::double precision * 3.14159265358979::double precision) / 180::double precision) / 2::double precision)), 2::double precision) + ((0.662990616338754::double precision * cos((((latitude)::double precision * 3.14159265358979::double precision) / 180::double precision))) * power(sin(((((((-83.102801) - longitude))::double precision * 3.14159265358979::double precision) / 180::double precision) / 2::double precision)), 2::double precision)))))) <= 1000::double precision))
Rows Removed by Filter: 6756
Planning time: 1.041 ms
Execution time: 587.695 ms
(8 rows)
编辑2：
我注意到PostgreSQL使用了我的
I noticed that postgresql uses my
add_index :users, [:latitude, :longitude]
仅当我输入小距离ex时。用户在10公里附近。
only when I type small distance ex. User in near 10 kilometers.
推荐答案
速度下降可能是由于数学运算而不是由于获取表数据引起的。您的部分条件不是针对记录字段，而是针对其他记录上的数学运算结果，因此它正在变为O（N 2 ）。
The slowdown is likely caused by math operations and not by fetching table data. Part of your criteria is not against record fields but against the outcome of the math operation on other records so it is becoming an O(N2).
Postgres不使用索引而是选择Seq扫描的原因是因为它决定查询时必须获取大多数表记录。当要获取表中的大多数记录时，索引可能不会带来太大的好处。
The reason Postgres does not use an index and chooses Seq scan instead is because it decides that most of the table records will have to be fetched while querying. When most records in the table are to be fetched, indexes may not bring much benefit if any.
要加快处理速度，您应该考虑使用空间索引和基于邻域的搜索 PostGis 或使用地理距离查询。
To speed things up you should consider using spacial indexes and vicinity-based search of PostGis or, alternatively, Elasticsearch with Geo Distance Query.
这篇关于添加索引以加快Geocoder靠近搜索的速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
