寻找最近的点 - 优化

Question

下面的 SQL 查询几乎是项目中最常用的部分。它的功能完全符合我的要求，但它的成本 (cost=11835.77..11835.82 rows=21 width=137) 太高了，并且它消耗了服务器资源。

SELECT
  "companies"."id",
  "companies"."name",
  MIN(
    ST_Distance(
      addresses.location,
      ST_SetSRID(ST_Point(28.9856799, 41.0842721), 4326)
    )
  ) as distance
from
  "companies"
  left join "branches" on "companies"."id" = "branches"."company_id"
  and "branches"."active" = true
  inner join "addresses" on "branches"."id" = "addresses"."addressable_id"
  and "addresses"."addressable_type" = 'App\Domains\Company\Models\Branch'
where
  "available" = true
group by
  "companies"."id"
order by
  "distance" asc
limit
  21 offset 0;

如果我必须简要解释，每个公司都有许多分支机构。我将分支机构的位置存储在addresses表中。我的目标是通过分页列出最靠近发送点的公司。

以下查询的成本(cost=0.57..23.12 rows=21 width=137)非常低，但有多个分支机构的公司会出现重复。但是该公司必须被列为单数。

select
  "companies"."id",
  "companies"."name"
from
  "companies"
  left join "branches" on "branches"."company_id" = "companies"."id"
  left join "addresses" on "addresses"."addressable_id" = "branches"."id"
where
  "addresses"."addressable_type" = 'App\Domains\Company\Models\Branch'
  and "branches"."active" = true
  and "available" = true
order by
  "addresses"."location" <-> ST_SetSRID(ST_Point(28.9856799, 41.0842721), 4326)
limit
  21 offset 0

我正在使用PostgreSQL 13和PostGIS作为数据库。

您可以在这里找到示例数据。

我想要看到的结果如下：

 id |      name       
----+-----------------
  1 | Apple
 13 | Volvo
  9 | Burger King
 18 | Sunexpress
 11 | Togg
 19 | MC Donalds
 14 | THY
 16 | Lufthansa
  6 | Migros
  5 | Carrefour
  4 | Starbucks
  3 | Apartment
 10 | Tesla
  2 | Coffee
 17 | Pegasus
 22 | LG
 15 | British Airways
 12 | Volkswagen
 21 | Samsung
 20 | KFC
  7 | Google

- Fatih Aytekin

---

<-> 运算符在 ORDER BY 子句中利用空间索引，而 ST_Distance 不会使用空间索引，因为它将对表中每个点进行顺序扫描并计算距离。 - N'Bayramberdiyev

Answer 1

不要计算所有记录到给定几何体的距离以找到最短距离，只需像Jim Jones所说的那样使用距离运算符<->。

---

因此，请更改这些行：

MIN(
    ST_Distance(
      addresses.location,
      ST_SetSRID(ST_Point(28.9856799, 41.0842721), 4326)
    )
  ) as distance

to

MIN(ST_SetSRID(ST_Point(28.9856799, 41.0842721), 4326) <-> addresses.location) AS distance

在选择列表中。

SELECT
    "companies"."id",
    "companies"."name",
    MIN(ST_SetSRID (ST_Point (28.9856799, 41.0842721), 4326) <-> addresses.location) AS distance
FROM
    "companies"
    LEFT JOIN "branches" ON "companies"."id" = "branches"."company_id"
        AND "branches"."active" = TRUE
    INNER JOIN "addresses" ON "branches"."id" = "addresses"."addressable_id"
        AND "addresses"."addressable_type" = 'App\Domains\Company\Models\Branch'
GROUP BY
    "companies"."id"
ORDER BY
    "distance" ASC
LIMIT 21 OFFSET 0;

此查询成本为(cost=183.36..183.42 rows=21 width=30)

- Nurettin Özdoğan