Re: Expression alias not recognized in WHERE clause (ERROR: relation "p" does not exist)

On 13 Aug 2012, at 1:15, Stefan Keller wrote:

> 2012/8/9 Alban Hertroys <haramrae(at)gmail(dot)com> wrote:
>> You're referencing "p" as a table, not as a table-alias, because you
>> select FROM p.
>
> That's true but sorry that I can see any difference between
> referencing a table or a table alias.

I don't think SQL allowed re-referencing an earlier mentioned table like that until CTE's were introduced to take care of it instead. Not sure what's behind that.

Normally people only use table aliases to have easier access to columns in a specific table in their query. Additionally, aliases are sometimes required to disambiguate which instance of the same table in a join is meant. Perhaps there are other users still, but using an alias as a table to select from is not among them I'm afraid.

>> And you're using WHERE count(*) > 1 where you actually mean WHERE
>> EXISTS; you're not interested in the actual count, which is quite a
>> bit less efficient to determine than just existence.
>
> That's a good and usual performance hint to test for existence instead
> of counting.
> But there's one of the challenges of this query:
> "Select all buildings that have >1 pharmacies and >1 schools within 1000m".
> So it's really forcing a count because it's asking for more than one (not >=1).

Ah yes, you're correct about that.

> Below you find what I finally got with CTE.
> Look at the two "select count(*)>1 which need to be made two times and
> contain a count.
>
> WITH
> building AS (
> SELECT way FROM osm_polygon
> WHERE tags @> hstore('building','yes')
> --LIMIT 1000
> ),
> pharmacy AS (
> SELECT way FROM osm_poi
> WHERE tags @> hstore('amenity','pharmacy')
> ),
> school AS (
> SELECT way FROM osm_poi
> WHERE tags @> hstore('amenity','school')
> )
> SELECT ST_AsText(building.way) AS building_geometry
> FROM building
> WHERE
> (SELECT count(*) > 1 FROM pharmacy
> WHERE ST_DWithin(building.way,pharmacy.way,1000))
> AND
> (SELECT count(*) > 1 FROM school
> WHERE ST_DWithin(building.way,school.way,1000))
>
> I also tried alternatives like:
> * "WHERE tags ? 'amenity" which is a kind of tag existence function
> * doing a GROUP BY
> * or applying a "JOIN .... ON ST_DWithin(building.way,school.way,1000)"
> ... but no one was as "fast" as this one.
>
> Unfortunately it's still VERY SLOW and takes more than 6 minutes on a
> current 72GB memory(!) server!
> No idea on how to speed up this tough nut to crack any more...

A start would be to post an explain analyse (or a link to the results from http://explain.depesz.com/)

It's possible that you can about half that time by using something like this instead of the separate pharmacy and school expressions (I haven't used hstore before, so I may have made an error or two):

nr_by_type AS (
SELECT way,
SUM(CASE WHEN tags.value = 'pharmacy' THEN 1 ELSE 0 END) AS nr_pharmacies,
SUM(CASE WHEN tags.value = 'school' THEN 1 ELSE 0 END) AS nr_schools
FROM osm_poi
WHERE tags.key = 'amenity'
GROUP BY way
)

But that's just a guess on my part of where the bottleneck is.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.