On 18 February 2018 at 12:35, hmidi slim <hmidi.slim2@gmail.com> wrote:
> 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
You really should state what is wrong with the performance of the
above version and also include the EXPLAIN (ANALYZE, BUFFERS) of that
query.
The version of PostgreSQL that you're running it on is also a good
thing to share.
Details of the indexes which you've defined on the tables are also
useful information. It would especially be good to know if you've
added an index on product (establishment_id), for example.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services