优化最近点查询：解决PostgreSQL+PostGIS的高成本与重复问题

我们的项目需要分页列出离指定坐标最近的公司，每个公司拥有多个门店，门店地理位置存储在addresses表中。目前基于PostgreSQL 13和PostGIS扩展有两个查询方案：

• 第一个查询结果符合预期，但查询成本极高（cost=11835.77..11835.82 rows=21 width=137），会消耗大量服务器资源；
• 第二个查询成本极低（cost=0.57..23.12 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;

低成本但结果重复的查询 #

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

期望结果 #

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

内容的提问来源于stack exchange，提问作者Fatih Aytekin