我有一個包含 10,301,390 個 GPS 記錄、城市、國家和 IP 地址塊的表。我有使用者目前的緯度和經度位置。我創建了這個查詢：

SELECT
 *, point(45.1013021, 46.3021011) <@> point(latitude, longitude) :: point AS distance
FROM
 locs
WHERE
 (
   point(45.1013021, 46.3021011) <@> point(latitude, longitude)
 ) < 10 -- radius
ORDER BY
 distance LIMIT 1;

這個查詢成功地給了我想要的東西，但是速度很慢。根據給定的緯度和經度，獲得一條記錄需要 2 到 3 秒。

latitude我在and列上嘗試了 B-Tree 索引longitude，也嘗試過GIST( point(latitude, longitude));，但查詢仍然很慢。

我怎樣才能加快這個查詢？

更新：

似乎緩慢是由ORDER BY但我想獲得最短距離造成的，所以問題仍然存在。

您可以考慮使用基於使用函式的 GIST 索引ll_to_earth。該索引將允許快速“附近”搜尋。

CREATE INDEX 
  ON locs USING gist (ll_to_earth(lat, lng));

一旦你有了這個索引，你的查詢應該以不同的方式完成。

您的 (lat, lng) 對需要轉換為earth類型，並與索引值（相同類型）進行比較。您的查詢需要有兩個條件，一個是“近似”結果，一個是“精確”結果。第一個將能夠使用以前的索引：

SELECT
   *
FROM
   locs
WHERE
   /* First condition allows to search for points at an approximate distance:
      a distance computed using a 'box', instead of a 'circumference'.
      This first condition will use the index.
      (45.1013021, 46.3021011) = (lat, lng) of search center. 
      25000 = search radius (in m)
   */
   earth_box(ll_to_earth(45.1013021, 46.3021011), 25000) @> ll_to_earth(lat, lng) 

   /* This second condition (which is slower) will "refine" 
      the previous search, to include only the points within the
      circumference.
   */
   AND earth_distance(ll_to_earth(45.1013021, 46.3021011), 
            ll_to_earth(lat, lng)) < 25000 ;

要使用此程式碼，您需要兩個擴展（包含在大多數 PostgreSQL 發行版中）：

CREATE EXTENSION IF NOT EXISTS cube ;
CREATE EXTENSION IF NOT EXISTS earthdistance;

這是他們的文件：

• 立方體。你應該看看@> 操作符的描述。下一個模組需要這個模組。
• 地球距離。您將在此處找到有關earth_box和的資訊earth_distance。該模組假設地球是球形的，這對於大多數應用來說是一個足夠好的近似值。

對包含來自自由世界城市數據庫的 220 萬行的表進行的測試為我提供了對上一個查詢的以下答案（與您的不完全相同）：

"ru","andra-ata","Andra-Ata","24",,44.9509,46.3327
"ru","andratinskiy","Andratinskiy","24",,44.9509,46.3327
"ru","chernozemelskaya","Chernozemelskaya","24",,44.9821,46.0622
"ru","gayduk","Gayduk","24",,44.9578,46.5244
"ru","imeni beriya","Imeni Beriya","24",,45.0208,46.3906
"ru","imeni kirova","Imeni Kirova","24",,45.2836,46.4847
"ru","kumskiy","Kumskiy","24",,44.9821,46.0622
"ru","kumskoy","Kumskoy","24",,44.9821,46.0622
"ru","lopas","Lopas","17",,44.937,46.1833
"ru","pyatogo dekabrya","Pyatogo Dekabrya","24",,45.1858,46.1656
"ru","svetlyy erek","Svetlyy Erek","24",,45.0079,46.4408
"ru","ulan tuk","Ulan Tuk","24",,45.1542,46.1097

要對時間有一個“數量級”的想法：pgAdmin III 告訴我得到這個答案的時間是 22 毫秒。（帶有“開箱即用”參數的 PostgreSQL 9.6.1，在帶有 Mac OS 10.12、Core i7、SSD 的 Mac 上）

引用自：https://dba.stackexchange.com/questions/158349