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

On Thu, Aug 9, 2012 at 4:00 AM, Stefan Keller <sfkeller(at)gmail(dot)com> wrote:
> Hi
>
> 2012/8/8 Jeff Janes <jeff(dot)janes(at)gmail(dot)com>:
>> On Tue, Aug 7, 2012 at 5:07 PM, Stefan Keller <sfkeller(at)gmail(dot)com> wrote:
>>> Hi Craig
>>>
>>> Clever proposal!
>>> I slightly tried to adapt it to the hstore involved.
>>> Now I'm having a weird problem that PG says that "relation 'p' does not exist".
>>> Why does PG recognize table b in the subquery but not table p?
>>> Any ideas?
>>
>> I don't think it does recognize b, either. It just fell over on p
>> before it had a chance to fall over on b.
>
> No, the b get's recognized. See my original query.
> That's a strange behaviour of the SQL parser which I can't understand.

Oh, I see. You are referencing b only as the qualifier for a column
name, while you are trying to reference p as a an entire query. I
initially misread it and thought you referencing both b and p in both
ways each.

>
>> I think you have to use WITH if you want to reference the same
>> subquery in multiple FROMs.
>
> I'll try that with CTE too.
>
>> Another approach would be to add explicit conditions for there being
>> at least 1 school and 1 pharmacy within distance. There can't be >1
>> unless there is >=1, but the join possibilities for >=1 (i.e. "where
>> exists" rather than "where (select count(*)...)>1" ) are much more
>> attractive than the ones for >1.
>>
>> Cheers,
>>
>> Jeff
>
> You mean, first doing a select on existence and then apply the count
> condition later?

Yes, exactly.

Of course this won't help if most buildings do have at least one of
each within distance, as then the prefilter is not very selective.

Cheers,

Jeff