添加索引以加快地理编码器附近的搜索速度

在我的 Rails 应用程序中，我具有允许查找最接近当前登录用户的用户的功能。我正在为此使用地理编码器 gem。在用户模型中，我的范围如下：

   scope :close_to, -> (user:, distance:) {
    where.not(id: user.id)
    .near([user.latitude, user.longitude], distance)
  }

这非常有效，但对于较大的用户集合来说速度很慢。当我调用此范围时，它会生成以下 sql 查询：

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;

我正在尝试为此创建索引，但它们不起作用。我正在尝试以下组合：

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]

我应该如何添加索引来加快查询速度？

编辑：我忘记补充一点，我的纬度和经度列是小数。

该查询的 ANALYZE 返回类似的内容：

 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)

EDIT 2:

我注意到 postgresql 使用我的

add_index :users, [:latitude, :longitude]

仅当我输入短距离时。用户在近10公里范围内。

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(N²).

Postgres之所以不使用索引而选择Seq扫描，是因为它决定查询时必须取出大部分表记录。当要获取表中的大多数记录时，索引可能不会带来太多好处（如果有的话）。

为了加快速度，您应该考虑使用空间索引和基于邻近区域的搜索PostGis http://postgis.net/或者，Elasticsearch 与地理距离查询 https://www.elastic.co/guide/en/elasticsearch/reference/5.x/query-dsl-geo-distance-query.html.