Railsアプリには、現在ログインしているユーザーに最も近いユーザーを見つけることができる機能があります。これにはGeocodergemを使用しています。ユーザーモデルでは、次のようなスコープがあります。
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)
編集2:
postgresqlが私の
add_index :users, [:latitude, :longitude]
短い距離を入力した場合のみ。10キロ近くのユーザー。
速度低下は、テーブルデータのフェッチではなく、数学演算が原因である可能性があります。基準の一部は、レコードフィールドに対してではなく、他のレコードに対する数学演算の結果に対してであるため、O(N 2)になります。
Postgresがインデックスを使用せず、代わりにSeqスキャンを選択する理由は、クエリ中にほとんどのテーブルレコードをフェッチする必要があると判断したためです。テーブル内のほとんどのレコードをフェッチする場合、インデックスがあったとしてもあまりメリットがない場合があります。
物事をスピードアップするには、PostGisの空間インデックスと近傍ベースの検索、または代わりに、Geo DistanceQueryを使用したElasticsearchの使用を検討する必要があります。
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加