query performance

[hmidi slim <hmidi.slim2@xxxxxxxxx>]

Hi, 

I have two tables: establishment which contains these columns: id, name, longitude, latitude, geom (Geometric column)

Product contains: id, name, establishment_id

First of all I want to select the establishment within a radius. 

I run this query:

select e.name, e1.name

from establishment as e, establishment as e1

where e.id <> e1.id

and e1.id = 1

and ST_DWithin(geom, ST_MakePoint(e1.longitude, e1.latitude)::geography, 1000)

The result of this query returns all the establishment within a radius 1KM from from a given establishment which has an id = 1.

After that I want to get the product's name of each establishment from the query's result.

Is there an other optimized solution to make a query such this:

select * from (

select e.name, e1.name, e.id

from establishment as e, establishment as e1

where e.id <> e1.id

and e1.id = 1

and ST_DWithin(geom, ST_MakePoint(e1.longitude, e1.latitude)::geography, 1000)) as tmp inner join product as p on p.establishment_id = tmp.id