Finding the Nearest Points - Optimize-CodePudding

The following sql query is almost the most used part of the project. It works exactly as I wanted but, Its cost (cost=11835.77..11835.82 rows=21 width=137) is too high and it consumes server resources.

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;

If I had to explain briefly; Every company has many branches. I keep the locations of the branches in the addresses table. My aim is to list the companies closest to the sent point by paginating.

The cost (cost=0.57..23.12 rows=21 width=137) of the following query is very low, but companies with more than one branch come as duplicates. But the company must be listed as singular.

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

I am using postgresql 13 and postgis as database.

You can find sample data here.

The result I want to see exactly;

 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

CodePudding user response：

Instead of calculating the distance of all records to the given geometries in order to find the shortest distance, just use the distance operator <-> as Jim Jones said.

So change this lines

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

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

this in select list.


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;

This query cost is (cost=183.36..183.42 rows=21 width=30)