[postgis-users] ST_Within with subquery as parameter makes query slow

[Stefan Keller]

Hi,

Recently I wrote this simple query:

  SELECT count (*)
  FROM
    osm_point AS osm
  WHERE
    ST_Within (
      osm.way,
     (SELECT way FROM osm_polygon WHERE osm_id=-51701)
    )
  AND tags @> 'railway=>station';

This was slow (>30 sec.) I then moved the subquery out of ST_Within to
the FROM-clause like this:

  SELECT count(*)
  FROM
    osm_point AS osm,
    (SELECT way FROM osm_polygon WHERE osm_id=-51701) AS ch
  WHERE
    ST_Within(osm.way, ch.way)
  AND tags @> 'railway=>station'

This was faster (5 sec.). As it seems, the presence of the subquery is
blocking the inlining of ST_Within. Strange that the call overhead is
so high.

Is this effect only bound to ST_Within?
Does anyone know more about this?

:Stefan