Обсуждение: query performance
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:
from establishment as e, establishment as e1
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 (
from establishment as e, establishment as e1
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
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
On 02/18/2018 06:37 AM, David Rowley wrote:
> 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.
>
There's actually a wiki page with overview of what should be included in
"slow query" questions:
https://wiki.postgresql.org/wiki/Slow_Query_Questions
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Sun, 18 Feb 2018 00:35:18 +0100 hmidi slim <hmidi.slim2@gmail.com> wrote: > 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 Did you not see my earlier response? -- Bill Moran