Thread: query performance

query performance

From
hmidi slim
Date:
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 (
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

Re: query performance

From
David Rowley
Date:
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


Re: query performance

From
Tomas Vondra
Date:
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


Re: query performance

From
PT
Date:
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