query's performance

On Thu, 15 Feb 2018 22:43:59 +0100
hmidi slim <hmidi.slim2@gmail.com> wrote:

Hi,
I have a table establishment which contains these columns: id, name,
longitude, latitude.
I want to select all the establishments in the table establishment within a
radius from a reference point which is a given establishment.
For example:
I have a given establishment called establishment1 and has an id=1.
I want to display all the establishments within a radius 1km from this
establishment.
I wrote this query:
select e.name,e1.name, e1.longitude, e1.latitude
from establishment as e, establishment as e1
where e.id = 1
and e.id <> e1.id
and ST_DWithin(ST_SetSRID(ST_MakePoint(e1.longitude, e1.latitude),
4326),ST_MakePoint(e.longitude, e.latitude)::geography, 1000) ;

Is it a good practice to iterate the rows of the table like I did and does
not have an effect in the performance if the number of table's rows
increase?

If performance is a concern, don't use long/lat in 2 columns. Instead use a
GEOGRAPHY type to store the point. You can then index on that GEOGRAPHY
column and queries against it will be quite fast.

The way you have it now is going to degrate in performance linearally with
the number of rows in the table. With the proper types and indexes, performance
degredation will be considerably less.

--
Bill Moran