Re: query's performance

On Thu, 15 Feb 2018 22:43:59 +0100
hmidi slim <hmidi(dot)slim2(at)gmail(dot)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