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

[Regina Obe]

Yes it's a known issue.  Not just limited ST_Within and I think it has bad affects beyond killing use of index. 
 As a general rule, don't use a sub query in a function call if you can avoid it.

I think Tom Lane explained the issue well a while ago.  Don't have the explanation on hand at moment.

-----Original Message-----
From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Stefan Keller
Sent: Tuesday, April 18, 2017 7:01 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: [postgis-users] ST_Within with subquery as parameter makes query slow

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
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users