How to improve "MAX" for "GROUP BY" over geo data on PostgreSQL?

Question

I have table of addresses for companies with 3 columns: company_id, lat, lon.

I need to have nearest distances to each companies. I use cube and earthdistance extensions for PostgreSQL.

Query work fine but slowly:

SELECT
 company_id,
 MIN(earth_distance(ll_to_earth(lat,lon), ll_to_earth(53.96,83.96))) AS distance
FROM companies
GROUP BY company_id;

GIST index like:

CREATE INDEX i_name on companies USING gist(ll_to_earth(lat, lon));

not using.

How can i resolve this problem? Thanks.

Answer 1

In general an index may help you to find few rows from a large table or increases speed for queries with ORDER BY. Your query needs to scan all rows in the table, and it does a complex computation for all the rows. Thus, an index cannot help you, because Postgres doen't use indexes as precomputed values.

You should instead precompute the value for ll_to_earth(lat, lon) into a separate column, and use this column in your query.