Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 0D5C08820F3 for ; Tue, 7 Aug 2012 09:17:00 -0300 (ADT) Received: from elizabeth.gransy.com ([89.187.132.199]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SyiiY-0004BP-2W for pgsql-performance@postgresql.org; Tue, 07 Aug 2012 12:16:59 +0000 Received: from sq.gransy.com (localhost [127.0.0.1]) by elizabeth.gransy.com (Postfix) with ESMTP id 0B2AE15E42FC; Tue, 7 Aug 2012 14:16:43 +0200 (CEST) Received: from 212.47.19.46 (SquirrelMail authenticated user tv@fuzzy.cz) by sq.gransy.com with HTTP; Tue, 7 Aug 2012 14:16:43 +0200 Message-ID: <1e50929e6ba44cabb8deb2ad0ee98c82.squirrel@sq.gransy.com> In-Reply-To: References: Date: Tue, 7 Aug 2012 14:16:43 +0200 Subject: Re: Slow query: Select all buildings that have >1 pharmacies and >1 schools within 1000m From: "Tomas Vondra" To: "Stefan Keller" Cc: pgsql-performance@postgresql.org User-Agent: SquirrelMail/1.4.22 MIME-Version: 1.0 Content-Type: text/plain;charset=utf-8 X-Priority: 3 (Normal) Importance: Normal Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: -0.5 (/) X-Archive-Number: 201208/33 X-Sequence-Number: 47571 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