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