Skip site navigation (1) Skip section navigation (2)

Re: Slow query: Select all buildings that have >1 pharmacies and >1 schools within 1000m

From: Stefan Keller <sfkeller(at)gmail(dot)com>
To: Tomas Vondra <tv(at)fuzzy(dot)cz>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow query: Select all buildings that have >1 pharmacies and >1 schools within 1000m
Date: 2012-08-07 12:22:53
Message-ID: CAFcOn28SbLbQ+3HtNXhSUiwiQ93un_PPP8sSA=Ut1kH_TyFtSA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Your proposal lacks the requirement that it's the same building from
where pharmacies and schools are reachable.
But I think about.

Yours, S.

2012/8/7 Tomas Vondra <tv(at)fuzzy(dot)cz>:
> On 7 Srpen 2012, 14:01, Stefan Keller wrote:
>> Hi
>>
>> I have an interesting query to be optimized related to this one [1].
>>
>> The query definition is: Select all buildings that have more than 1
>> pharmacies and more than 1 schools within a radius of 1000m.
>>
>> The problem is that I think that this query is inherently O(n^2). In
>> fact the solution I propose below takes forever...
>
> What about plain INTERSECT? Something like
>
> SELECT osm_id FROM osm_poi AS p, osm_polygon b
>    WHERE p.tags @> hstore('amenity','pharmacy')
>    AND ST_DWithin(b.way,p.way,1000)
> INTERSECT
> SELECT osm_id FROM osm_poi AS p, osm_polygon b
>    WHERE p.tags @> hstore('amenity','school')
>    AND ST_DWithin(b.way,p.way,1000)
>
> Or something like that. But maybe it's a complete nonsense ...
>
> Tomas
>

In response to

Responses

pgsql-performance by date

Next:From: Tomas VondraDate: 2012-08-07 13:56:46
Subject: Re: Slow query: Select all buildings that have >1 pharmacies and >1 schools within 1000m
Previous:From: Tomas VondraDate: 2012-08-07 12:16:43
Subject: Re: Slow query: Select all buildings that have >1 pharmacies and >1 schools within 1000m

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group